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

 

[Kevin0427]

Button is SaveRecordButton

 

[Kevin0427]

Maybe even hide the button AND disable it OR Unhide it AND enable it.

 

[My Aswer Is This]

Normally we would tell the script in the button to only run if this or that happened.

So if range("A1").value="Me" then do this.
This would then not even require a button click

Show us the script in the button

 

[Kevin0427]

Sub SaveRecordMacro()
'
' SaveRecordMacro Macro
' Unprotect Book
ActiveWorkbook.Unprotect Password:="newton7062"

' Activate and Unprotect Data Entry Form
Sheets("Data Entry Form").Activate
ActiveSheet.Unprotect Password:="newton7062"

' Unhide and Unprotect Real Data
Sheets("Real Data").Visible = True
Sheets("Real Data").Activate
ActiveSheet.Unprotect Password:="newton7062"

'Activate Data Entry Form, Select Data and copy
Sheets("Data Entry Form").Activate
Sheets("Data Entry Form").Range("BA1:BI1").Select
Selection.Copy

'Select Real Data, Scroll and Special Paste
Sheets("Real Data").Activate
Sheets("Real Data").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Select, Protect and Hide Real Data
Sheets("Real Data").Activate
ActiveSheet.Protect Password:="newton7062"
Sheets("Real Data").Visible = False

'Select Data Entry Form and Delete Data
Sheets("Data Entry Form").Activate
Sheets("Data Entry Form").Range("R16").Select
Selection.ClearContents
Sheets("Data Entry Form").Range("R18").Select
Selection.ClearContents
Sheets("Data Entry Form").Range("R20").Select
Selection.ClearContents
Sheets("Data Entry Form").Range("R22").Select
Selection.ClearContents

'Put Cursor in correct position
Sheets("Data Entry Form").Activate
Sheets("Data Entry Form").Range("R16").Select

'Select and Protect Data Entry Form
Sheets("Data Entry Form").Activate
ActiveSheet.Protect Password:="newton7062"

'Protect Workbook
ActiveWorkbook.Protect Password:="newton7062"

'Save Workbook
ActiveWorkbook.Save

'Scroll to Column A
ActiveWindow.ScrollColumn = 1




End Sub

 

[Kevin0427]

I prefer to have the button so they can change data before it gets recorded if necessary.

 

[My Aswer Is This]

You said:

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.​

So are you saying:
You have a named range on Sheet named Calculations. And this named range is named

ValidData​

And if this range value changes to 1 then run the script you provided. Is that what you want?

And if the Entire workbook is protected how does this value change from 1 to 0

 

[Kevin0427]

Oh. I'm getting it. IF ValidData=1 Run Macro else don't run it. I think that might be ok. They would still have to hit the button but it would not run if data is invalid. Perhaps a message would be needed to say the data was invalid and the record was not saved.

 

[Kevin0427]

Yes. The cell is calculated from their entries and changes all the time.

 

[Kevin0427]

To clarify if they hit the button and the data they entered is valid the macro should run. If they hit the button and the data is invalid the macro should not run.