Because the header structure of the units to download matches the units table in ODM2 instead of having the same headers as the other CV's, we do need to add to or modify the VBS code excel is currently using to pull in CV's to get the units to come in. Adding a separate button on the units worksheet might be a good idea to further distinguish it from the other CV's. The only problem with pulling the entire table into the units worksheet like that is that the user would suddenly have to scroll through the entire table to get to the unit they want when they're using the drop-downs in the data columns sheet.
If everyone was going to use the downloaded units, then the scroll list could be cropped to at least specific unit types by having the user first select the unit type and then having only the option of units of that type. This can be done without any VBA at all just by using fancier equations for the dynamic named ranges that are used to populate the data validation drop down. This would also solve https://github.com/ODM2/YODA-File/issues/45. The problem with this would be that if someone wanted to use only their own units, it wouldn't work unless they typed in their units doubly sorted, first alphabetically by by unit type and then within each unit type by unit term. Or we would have to create a macro to sort the units for them after they've been typed in.
Another solution to the scrolling problem could be to figure out how to use combo boxes and VBA to come up with some type of autofill. This would actually solve the scrolling problems for every data-validation list all at once. I've found instructions on how to make that work (http://www.contextures.com/xlDataVal10.html) but it has caveats, like not working on Macs or and maybe some of the issues with clearing the undo log as I mentioned in (https://github.com/ODM2/YODA-File/issues/18) We might want to do this even in combination with the double selection above.
We could also try to do something with the units like having a hidden units table that is pre-populated and updated with the CV's in addition to the visible units sheet. Then we could make the entire units table drop-down fill-able, but with looser validation so that using the drop-down is a suggestion instead of a forced list. This would allow users to keep their visible units table short, but would also allow them the easily use common units like minutes and seconds and meters without having to recreate them unit every time.
We've already discussed this a bit in https://github.com/ODM2/ODM2ControlledVocabularies/issues/27 and https://github.com/ODM2/YODA-File/issues/32 but I wanted to create a separate issue just for this problem.
Because the header structure of the units to download matches the units table in ODM2 instead of having the same headers as the other CV's, we do need to add to or modify the VBS code excel is currently using to pull in CV's to get the units to come in. Adding a separate button on the units worksheet might be a good idea to further distinguish it from the other CV's. The only problem with pulling the entire table into the units worksheet like that is that the user would suddenly have to scroll through the entire table to get to the unit they want when they're using the drop-downs in the data columns sheet.
If everyone was going to use the downloaded units, then the scroll list could be cropped to at least specific unit types by having the user first select the unit type and then having only the option of units of that type. This can be done without any VBA at all just by using fancier equations for the dynamic named ranges that are used to populate the data validation drop down. This would also solve https://github.com/ODM2/YODA-File/issues/45. The problem with this would be that if someone wanted to use only their own units, it wouldn't work unless they typed in their units doubly sorted, first alphabetically by by unit type and then within each unit type by unit term. Or we would have to create a macro to sort the units for them after they've been typed in.
Another solution to the scrolling problem could be to figure out how to use combo boxes and VBA to come up with some type of autofill. This would actually solve the scrolling problems for every data-validation list all at once. I've found instructions on how to make that work (http://www.contextures.com/xlDataVal10.html) but it has caveats, like not working on Macs or and maybe some of the issues with clearing the undo log as I mentioned in (https://github.com/ODM2/YODA-File/issues/18) We might want to do this even in combination with the double selection above.
We could also try to do something with the units like having a hidden units table that is pre-populated and updated with the CV's in addition to the visible units sheet. Then we could make the entire units table drop-down fill-able, but with looser validation so that using the drop-down is a suggestion instead of a forced list. This would allow users to keep their visible units table short, but would also allow them the easily use common units like minutes and seconds and meters without having to recreate them unit every time.