Enable/Disable Macro Button

[Kevin0427]

Hey there. I have a Workbook with several Sheets. One Sheet (Data Entry Form) has a button on it that runs a macro to save the data entered to a separate sheet (Real Data).

I would like for that button to be disabled at all times unless the data the user entered meets certain criteria. I have a cell (ValidData) on a third sheet (Calculations) that determines that. If ValidData = 0 the button should be disabled and if ValidData= 1 the button should be enabled. This should update anytime the workbook is changed so that it is always correct. And the macro won't run if the data the user entered is wrong.

I am very new at VBA so go easy on me !! LOL

 

[My Aswer Is This]

He is a simple example:

Sub Data_Check()
'Modified  8/30/2018  2:30:18 PM  EDT
If Range("DataValid") = 1 Then
MsgBox "Hello"
Else
MsgBox "DataValid does not equal 1  I will stop script": Exit Sub
End If
End Sub

 

[Greg Truby]

The following assumes: (a) the button is an ActiveX button and not a forms button and (b) your named range "ValidData" is scoped to the workbook level and not scoped to the Calculations worksheet.

Private Sub Worksheet_Calculate()
    Me.CommandButton1.Enabled = Me.Range("ValidData")
End Sub

 

[Kevin0427]

Like this ?

If Range("DataValid") = 1 Then

Existing Marco Here

Else
Msgbox "Invalid Data. Please check your work and try again."

 

[My Aswer Is This]

Like this ?

If Range("DataValid") = 1 Then

Existing Marco Here

Else
Msgbox "Invalid Data. Please check your work and try again."

Yes

 

[Kevin0427]

And of course I need an If End If before the End Sub.

Does DataValid need to be globally defined as Greg indicated? If so, how is that done.

 

[My Aswer Is This]

I never like to comment on what other posters may suggest.
I see nothing in his post here about

globally defined


​

 

[Kevin0427]

I never like to comment on what other posters may suggest.
I see nothing in his post here about

globally defined


​

My bad. "scoped to theWorkbook" presumably because the button and the ValidData cell are on different sheets. Does that matter ?

 

[Kevin0427]

My bad. "scoped to theWorkbook" presumably because the button and the ValidData cell are on different sheets. Does that matter ?

Your code works perfectly without the scope change unless I defined it like that by mistake when I did it. Thanks a Million !!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

There are always 10 different ways to do everything in Excel:
Which way is the best or fastest I'm sure is always up for discussion.

Like you could write a script.

And then in the button only enter: Call Sally

If your original script was name Sally

This way you could easily assign the same script to several buttons. Without having to put same script in each button.

And you could just enter: Sally

See how there are always different ways to do things

Nice talking with you. Take care

 

[Greg Truby]

My bad. "scoped to theWorkbook" presumably because the button and the ValidData cell are on different sheets. Does that matter ?

It only matters if you use the code I posted. Note that the code is tied to the _Calculate event handler for the Worksheet that has the button and that the call to the .Range method is prefixed by the Me. object. Even though, technically it shouldn't let you get away with doing this when the "parent" of the name is the workbook, Excel will let you slide this reference in.

Private Sub Worksheet_Calculate()
    Me.CommandButton1.Enabled = Me.Range("ValidData")
End Sub

As My Answer said, there are almost always several ways of skinning Excel cats. Pick whichever one you feel is the best fit and learn from the rest if you have the time and inclination. You had asked to actually disable the button - my code actually does disable the button.


However, there are many, many cases where — instead of toggling the .enabled property of a control — I've just had the procudure the control was calling send a message and exit if some validity check failed. This is especially true when I've been coding buttons that are called from the ribbon. Trying to get into all the happy jack surrounding coding the enabled callbacks and slipping those attributes into the Ribbon's xml is tiresome and tedious. Not to mention trying to figure out which application events to trap to invalidate the control to force Excel to raise the event to call the .enabled callback. Usually just easier to error message and exit.