disable a comman button after one click then

[mahhogany]

enable it again 28 days later?

Is this possible?

I have a command button that copy and pastes data but this information only needs to be done every month. Any help with the VBA code?

 

[Andrew Poulsom]

What actually determines whether the macro should be run? If you know that you can test for it in the macro.

 

[mahhogany]

The Macro which is allocated to this command button will run after a manual click but I don't want users to click on it multiple times as it will corrupt the data.

Idealy I want the user to click on the button once to run the macro which will copy and paste the data.
If they click on it a second time I want a message box to pop up instructing them that they have already done so this month etc.

 

[Andrew Poulsom]

Isn't there some way of determining that it has already been run for the current month? You can exit the procedure if that condition is true?

 

[mahhogany]

Isn't there some way of determining that it has already been run for the current month? You can exit the procedure if that condition is true?

There isn't anything particuarly in the sheet. I was thinking that there could be maybe some sort of VBA timestamp function which could determine this. i.e. the date the macro is run is recorded and then every 28 days after that.

 

[Andrew Poulsom]

You could store the date it's run in a spare cell somewhere and deduct from today's date. If the difference is less than 28 days exit the sub, otherwise run the macro and update the date run.

 

[mahhogany]

You could store the date it's run in a spare cell somewhere and deduct from today's date. If the difference is less than 28 days exit the sub, otherwise run the macro and update the date run.

Thanks

Any tips for the code?

Alternatively I have this code below in sheet 1

Private Sub Refresh_Graph_Data_Click()
CopyGraphValues 'name of macro
Refresh_Graph_Data.Enabled = False 'name of button
End Sub

Which disables the button after one click. However how do I get it enabled again on workbook open? I was thinking that I could try this option togther with a message box instructing them to press the button once.

I put this code in the workbook but it won't work.

Sub Workbook_Open()
Workbook ("sheet 1")
.Refresh_Graph_Data.Enabled = True
End Sub

 

[Andrew Poulsom]

If the code is in the module for the worksheet that contains the CommandButton:

Me.Refresh_Graph_Data.Enabled = False

Otherwise:

Worksheets("Sheet 1").OLEObjects("Refresh_Graph_Data").Object.Enabled = True