Enable a Macro only when there are Entries in Two Cells

[HangMan]

I have a button (Shape) which when clicked runs a macro. I'd like this to be disabled and greyed out until there are entries in two cells (see attached sample). When there are entries in both cells, the button's format should change and allow the macro to run when clicked.

Is there any way to achieve this?

Many thanks

[LJMetzger]

Hi HangMan,

I was able to do what you want by adding code to "Hello World" and putting code in the 'Sheet' Worksheet_Change() event.

The 'Shape name' must be in the code , and the 'Shape name' MUST be unique. If the name is not unique, VBA will use the first Shape that has that name. In addition, the 'Hello World' Macro cannot service multiple shapes as written.

My code assumes shape name 'Rounded Rectangle 1'.

Modified Hello World Code:

Please Login or Register  to view this content.

Sheet Module Code:

Please Login or Register  to view this content.

-----------------
A much simpler approach which I use in my own applications, is to Hide the Shape when you want to disable it. This requires NO MODIFICATIONS to the 'Hello World' Macro, and the 'Hello World' macro can service many shapes if needed.

The Sheet module code for this would be:

Please Login or Register  to view this content.

Lewis

[HangMan]

I hadn't considered the idea of 'hiding' the button. I think that is potentially a much better approach as it means the user can't run the macro without populating the two cells (although in practice of course they could still run it manually, but I'm not worried by that).

I applied your code to my main sheet and it seems to be working perfectly...

Many thanks for your idea and help with this, it is very much appreciated...

[LJMetzger]

Hi,

Thanks for the kind words and the rep points.

I think that is potentially a much better approach as it means the user can't run the macro without populating the two cells (although in practice of course they could still run it manually, but I'm not worried by that).

For future reference, code like the following will prevent the user running the Macro manually.

Please Login or Register  to view this content.

Lewis