Hi folks. Keith here. I have a project for my Volunteer Fire Department in which I want to be able to update a membership roster between the dates of Jan 1 and Jan 15 each year. I would like to use an Option Button to activate a macro to acomplish this. I would also like the Option Button to be visible ONLY between the dates above. Can someone help me create this Option Button? Thanks in advance. KB
Option Button Visible By Date
- Thread starter Excel_Fan
- Start date
First, create a ActiveX command button (not from the Forms menu). Then, paying attention to the sheet name and the name of the shape you just created, you could use this macro. This needs to go in the ThisWorkbook module of the VB project.
[pre]
[/pre]
[pre]
Code:
Private Sub Workbook_Open()
'Define the sheet name and button name correctly
With Worksheets("Sheet2").Shapes("CommandButton1")
If Date >= DateValue("1/1/" & Year(Date)) And Date <= DateValue("1/15/" & Year(Date)) Then
.Visible = True
Else
.Visible = False
End If
End With
End SubLuke,
Thanks for the quick reply. I copied and pasted the code in to the sheet after creating the CommandButton. Further, I followed your directions exactly by defining the sheet name and CommandButton name. However after saving, closing and re-opening, the CommandButton is still visible even though the date parameters should require the CommandButton invisible.
What have I done wrong?
Keith
Thanks for the quick reply. I copied and pasted the code in to the sheet after creating the CommandButton. Further, I followed your directions exactly by defining the sheet name and CommandButton name. However after saving, closing and re-opening, the CommandButton is still visible even though the date parameters should require the CommandButton invisible.
What have I done wrong?
Keith
The code IS IN the ThisWorkbook module. Here is the code as I have it now:
Private Sub Workbook_Open()
'Define the sheet name and button name correctly
With Worksheets("Equip. Issue").Shapes("CommandButton1")
If Date >= DateValue("1/1/" & Year(Date)) And Date <= DateValue("1/15/" & Year(Date)) Then
.Visible = True
Else
.Visible = False
End If
End With
End Sub
KB
Private Sub Workbook_Open()
'Define the sheet name and button name correctly
With Worksheets("Equip. Issue").Shapes("CommandButton1")
If Date >= DateValue("1/1/" & Year(Date)) And Date <= DateValue("1/15/" & Year(Date)) Then
.Visible = True
Else
.Visible = False
End If
End With
End Sub
KB
NARAYANK991
Excel Ninja
Hi Keith ,
Can you try the following ?
Once your workbook is open , go into VBA project explorer , place the cursor anywhere in the procedure , and press F8 to step through the procedure ; each time you press F8 , the system will execute one statement within the procedure.
Once you come to the IF statement , you can use the Immediate window to display each of the individual components by using the ? symbol , as follows :
?Date
?DateValue("1/1/" & Year(Date))
?DateValue("1/15/" & Year(Date))
You can compare the two values the way the IF statement is doing , by using :
?DateValue("1/15/" & Year(Date)) = Date
This will display either True or False.
This will tell you where the program is not working the way you expect it to.
Narayan
Can you try the following ?
Once your workbook is open , go into VBA project explorer , place the cursor anywhere in the procedure , and press F8 to step through the procedure ; each time you press F8 , the system will execute one statement within the procedure.
Once you come to the IF statement , you can use the Immediate window to display each of the individual components by using the ? symbol , as follows :
?Date
?DateValue("1/1/" & Year(Date))
?DateValue("1/15/" & Year(Date))
You can compare the two values the way the IF statement is doing , by using :
?DateValue("1/15/" & Year(Date)) = Date
This will display either True or False.
This will tell you where the program is not working the way you expect it to.
Narayan
SirJB7
Excel Rōnin
Hi, Excel_Fan!
Have you checked how is your Excel configured to allow or deny macros use?
In 2010 version, File, Options, last item ("Centro de confianza" in spanish, in english something like "Trust Center"?), button at middle right "Setup ...", and there you have macro configuration.
Please adjust it to second value ("Deshabilitar todas las macros con notificación" in spanish, in english something like "Disable all macros with notification"), check the last and only check box ("Confiar en el acceso al modelo de objetos de proyectos de VBA" in spanish, in english something like "Trust in VBA project's objects model access").
Now when you open again your workbook it should ask you if you enable macros or not, choose yes and test the button functionality.
Regards!
Have you checked how is your Excel configured to allow or deny macros use?
In 2010 version, File, Options, last item ("Centro de confianza" in spanish, in english something like "Trust Center"?), button at middle right "Setup ...", and there you have macro configuration.
Please adjust it to second value ("Deshabilitar todas las macros con notificación" in spanish, in english something like "Disable all macros with notification"), check the last and only check box ("Confiar en el acceso al modelo de objetos de proyectos de VBA" in spanish, in english something like "Trust in VBA project's objects model access").
Now when you open again your workbook it should ask you if you enable macros or not, choose yes and test the button functionality.
Regards!
Narayan,
I did this procedure and got a compile error "End with no IF". Where do I add the "IF" in order to clear this up? When I exited and re-started the sheet, the CommandButton vanished, but did not re-appear when I changed the date to the "visible" parameter. We're VERY close to a solution here.
Thanks for your help.
KB
I did this procedure and got a compile error "End with no IF". Where do I add the "IF" in order to clear this up? When I exited and re-started the sheet, the CommandButton vanished, but did not re-appear when I changed the date to the "visible" parameter. We're VERY close to a solution here.
Thanks for your help.
KB
To clear up some potential confusion, in VB, Date is a system reserved word that refers to today's date (based off of systems's computer). The "No End if" error sounds like something got moved to the wrong line. Are the lines of code laid out just like this?
[pre]
Code:
Private Sub Workbook_Open()
'Define the sheet name and button name correctly
With Worksheets("Sheet2").Shapes("CommandButton1")
If Date >= DateValue("1/1/" & Year(Date)) And Date <= DateValue("1/15/" & Year(Date)) Then
.Visible = True
Else
.Visible = False
End If
End With
End SubHi folks. This is the code I have in the ThisWorkbook module:
[pre]
[/pre]
For some reason, now it checks out, but will not render the CommandButton invisible. Macros ARE enabled and I'm running Excel 2007 with Windows 7 Ultimate.
I really appreciate all the help.
KB
[pre]
Code:
Private Sub Workbook_Open()
'Define the sheet name and button name correctly
With Worksheets("Equip. Issue").Shapes("CommandButton1")
If Date >= DateValue("1/1/" & Year(Date)) And Date <= DateValue("1/15/" & Year(Date)) Then
.Visible = True
Else
.Visible = False
End If
End With
End SubFor some reason, now it checks out, but will not render the CommandButton invisible. Macros ARE enabled and I'm running Excel 2007 with Windows 7 Ultimate.
I really appreciate all the help.
KB
SirJB7,
I am reluctant to upload the file as it contains 11 sheets and some personal information as well as various forms. I will create another file in which I can address these issues and will try that. I am busy this week but will get it out as soon as I can.
Again, thank you for your assistance.
KB
I am reluctant to upload the file as it contains 11 sheets and some personal information as well as various forms. I will create another file in which I can address these issues and will try that. I am busy this week but will get it out as soon as I can.
Again, thank you for your assistance.
KB