Using Data Validation with Enable Macros

Question

I want to use data validation so that if macros are not enabled, data validation will not allow any entry in the selected cell. Maybe a helper cell that is TRUE if macros have been enabled? How is this done?

Answer 1

One related answer is that you can actually set up a helper worksheet with a big warning "close this file and re-open with macros enabled", and have the workbook_open macro hide that sheet and unhide your actual data sheets (and reverse the hiding and unhiding process on workbook_close).

If you want to keep your form visible at all times and only allow entry with macros enabled, the concept is similar- use the workbook_open macro to change your helper cell to TRUE, and use the workbook_close macro to change it back to FALSE(if your users may re-use the same file)

I can think of two ways to use this to support your data validation; the easier way would be to set up your source list as a named range, and check the value of your helper cell within that named range formula. If the value of the helper cell is false, then the only item in the acceptable value list will be "Entry disabled" or if you have room, something more self-explanatory like "Enable macros to use this form"

Alternatively, you could build it directly into the data validation, by selecting 'Custom' instead of 'List'. You would then have to build in the matching logic as a formula, such as (aircode) =IF(AND(A1 = True,not(iserror(match(B1,MyListOfAcceptableValues,false))))

I'm not totally sure on that syntax, it is just to point you in the right direction.

EDIT: here is a sample for building the logic within data validation:

=OR(AND(G10=TRUE,NOT(ISERROR(MATCH(H10,F10:F12,FALSE)))),AND(G10=FALSE,H10="Enable Macros"))

[H10 is the cell containing the data validation]

This checks to see if the helper cell (G10) is true or false, then depending on the answer, it requires either a value found in F10-F12, or the text "Enable Macros" (which would be the default in that cell when you open the workbook)

The downside of this approach is that you don't get the nice drop-down lists in data validation (if you were using lists), and there is a little more setup hassle.