VBA Test to Enable/Disable Command Button in Form-Acc03/XPhm

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Hello all!

I've been toying with a piece of code that I can't get to work...I got the ideas for this from part of various posts and I'm doing something wrong. At the moment, I get a Type Mismatch error on compile for (VarDate2 Is Null), but I have yet to get any test like this to work even when I wasn't getting an error.

Could someone check the code, please? I am an absolute novice at VBA :confused:

Code:
Private Sub Form_AfterUpdate()

Dim VarDate, VarDate1, VarDate2 As Date
VarDate = Me.BeginDate
VarDate1 = Me.EndDate
VarDate2 = Me.FiscalYear

If (VarDate Is Null) Or (VarDate1 Is Null) Or (VarDate2 Is Null) Then
  Me.Run_Art_Reporting_Macro_Button.Enabled = False
Else
  Me.Run_Art_Reporting_Macro_Button.Enabled = True
End If
End Sub

Basically, I don't want anyone to be able to touch that macro button (default not enabled) unless they first fill in the three date fields. Otherwise, they will be left waiting for the macro to give them absolutely zero data!

Any help-greatly appreciated!
Thanks,
Max
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Max

Perhaps you should be using IsNull?
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Norie, you mean like this?

Code:
Private Sub Form_AfterUpdate()

Dim VarDate, VarDate1, VarDate2 As Date
VarDate = Me.BeginDate
VarDate1 = Me.EndDate
VarDate2 = Me.FiscalYear

If (VarDate IsNull) Or (VarDate1 IsNull) Or (VarDate2 IsNull) Then
  Me.Run_Art_Reporting_Macro_Button.Enabled = False
Else
  Me.Run_Art_Reporting_Macro_Button.Enabled = True
End If
End Sub

It gives me this error Expected: ) for the whole If/Then line!
The help box that I clicked didn't really help me :confused:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Eh, no.:)

More like this.
Code:
Private Sub Form_AfterUpdate() 

Dim VarDate, VarDate1, VarDate2 As Date 
VarDate = Me.BeginDate 
VarDate1 = Me.EndDate 
VarDate2 = Me.FiscalYear 

If IsNull(VarDate) Or IsNull(VarDate1) Or IsNull(VarDate2) Then 
  Me.Run_Art_Reporting_Macro_Button.Enabled = False 
Else 
  Me.Run_Art_Reporting_Macro_Button.Enabled = True 
End If 
End Sub
You should also note that those variables aren't actually all being declared as dates.
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
OK, Thanks Norie. (Told you I was a novice! :biggrin: -maybe that was an understatement!)

One last thing:

I have this code as an "After Update" event for the form, but it doesn't seem to function like I hoped. What qualifies as an "Update" for the form.

I copied the same code in one of the date fields and when I update that field, it performs it's function perfectly. I can set it for all three date fields if necessary, but had hoped that by setting it on the form's after update event, any change in the form would test to see if the button should be enabled or not.

I appreciate your time :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top