Can I create a "Button" on a sheet that only becomes visible in the month of Jun?

[mikecox39]

Every year, in June, I create a new workbook from the current workbook.

I have a macro the does all that and I put a button on the current workbook to run it, but I want to hide it until Jun, so no one will hit it mistake. Can that be done?

 

[jim may]

Paste below code in your June Code Sheet (Code below currently reads Sheet1). Currently assumes your Command button to be named "CommandButton1". You will only need to edit it each year to update the year in the code dates. HTH, Jim

Private Sub Worksheet_Activate()
'ActiveX button
If Date >= DateValue("6/01/2019") And Date <= DateValue("6/30/2019") Then
  Sheets("Sheet1").CommandButton1.Visible = False
Else
  Sheets("Sheet1").CommandButton1.Visible = True
End If
End Sub

 

[mikecox39]

Paste below code in your June Code Sheet (Code below currently reads Sheet1). Currently assumes your Command button to be named "CommandButton1". You will only need to edit it each year to update the year in the code dates. HTH, Jim

Private Sub Worksheet_Activate()
'ActiveX button
If Date >= DateValue("6/01/2019") And Date <= DateValue("6/30/2019") Then
  Sheets("Sheet1").CommandButton1.Visible = False
Else
  Sheets("Sheet1").CommandButton1.Visible = True
End If
End Sub

Awsome! Thanks to much!

But here's the rub... I'm turning this WB over to someone who, most likely, will not know how to edit a macro.
Is it possible to code it so it updates itself, based on the current date? So it updates itself at the time is it used?

 

[jim may]

Maybe something like this...

Private Sub Worksheet_Activate()
'ActiveX button
dYear = Year(Date) 'your systems currrent year
If Date >= DateSerial(dYear, 6, 1) And Date <= DateSerial(dYear, 6, 30) Then
    Sheets("Sheet1").CommandButton1.Visible = False
Else
  Sheets("Sheet1").CommandButton1.Visible = True
End If
End Sub

 

[Rick Rothstein]

Maybe something like this...

Private Sub Worksheet_Activate()
'ActiveX button
dYear = Year(Date) 'your systems currrent year
If Date >= DateSerial(dYear, 6, 1) And Date <= DateSerial(dYear, 6, 30) Then
    Sheets("Sheet1").CommandButton1.Visible = False
Else
  Sheets("Sheet1").CommandButton1.Visible = True
End If
End Sub

I think this Activate event code (same sheet and button as you assumed) will also work...

Private Sub Worksheet_Activate()
  Sheets("Sheet1").CommandButton1.Visible = Month(Now) = 6
End Sub

 

[mikecox39]

I think this Activate event code (same sheet and button as you assumed) will also work...

Private Sub Worksheet_Activate()
  Sheets("Sheet1").CommandButton1.Visible = Month(Now) = 6
End Sub

This looks super simple. Even I understand it, i think.

It looks me like the button will appear on the current worksheet every year, for the entire month of Jun, with no need to update it. If that's correct "I love it"!

 

[Rick Rothstein]

This looks super simple. Even I understand it, i think.

It looks me like the button will appear on the current worksheet every year, for the entire month of Jun, with no need to update it. If that's correct "I love it"!

I am not sure "current worksheet" is the proper term. What I posted is event code and event code goes in the code sheet for a particular worksheet, not any worksheet that is active. To install the code I posted, you must right click the tab for the worksheet you want to put the code in... a code window will open... you copy/paste my code in it. Then after that, whenever that worksheet activates, the code I posted will run... it will test to see if the current month number is 6 and, if it is, the Month(Now)=6 part of the code line will evaluate to True which, in turn, will be assigned to the CommandButton's Visible property... if the month number is not 6, then False will be assigned to the CommandButton's Visible property.

 

[mikecox39]

I am not sure "current worksheet" is the proper term. What I posted is event code and event code goes in the code sheet for a particular worksheet, not any worksheet that is active. To install the code I posted, you must right click the tab for the worksheet you want to put the code in... a code window will open... you copy/paste my code in it. Then after that, whenever that worksheet activates, the code I posted will run... it will test to see if the current month number is 6 and, if it is, the Month(Now)=6 part of the code line will evaluate to True which, in turn, will be assigned to the CommandButton's Visible property... if the month number is not 6, then False will be assigned to the CommandButton's Visible property.

The sheet i would put this in would be sheets("Master")

I'm taking a course in VBA so I'm a real novice, which means I'm not certain where to put this.

I could put it in a module but I'm thinking that putting in the master sheet Object might be better since I wouldn't use it on any other sheet or in any other WB. In that case would the Worksheet("Master") be needed but could you just put the method (or property, I still have trouble with syntax)?

 

[Rick Rothstein]

The sheet i would put this in would be sheets("Master")

I'm taking a course in VBA so I'm a real novice, which means I'm not certain where to put this.

I could put it in a module but I'm thinking that putting in the master sheet Object might be better since I wouldn't use it on any other sheet or in any other WB. In that case would the Worksheet("Master") be needed but could you just put the method (or property, I still have trouble with syntax)?

You must put my code in the worksheet's code module... nowhere else. Again, right-click the tab for your "Master" worksheet and select "View Code" from the popup menu that appears, then copy/paste my code into that code window. That's it... you are done. From then on out, whenever the "Master" worksheet is activated, the code I gave you will run (and display the CommandButton on in the 6th month).

 

[mikecox39]

You must put my code in the worksheet's code module... nowhere else. Again, right-click the tab for your "Master" worksheet and select "View Code" from the popup menu that appears, then copy/paste my code into that code window. That's it... you are done. From then on out, whenever the "Master" worksheet is activated, the code I gave you will run (and display the CommandButton on in the 6th month).

Ok, thanks, I'll do that. And thanks again for got code.