Disable button after first use/enable again after re-opening file

[CutterSoilMixing]

Hi everyone,

I created a command button from the ActiveX controls. I want it to add +1 to a certain cell, update the date in another cell, disable after clicking it once and re-enable after closing and opening the excel file. I got everything working but the re-enabling... please help, this is my first experience with VBA, macros etc.

Here's my code:

Private Sub CommandButton1_Click()
[BF5].Value = [BF5].Value + 1
[AZ6].Value = "=TODAY()"
CommandButton1.Enabled = False


End Sub


Sub Autpen()
CommandButton1.Enabled = True


End Sub

Thanks!

 

[Fluff]

Hi & welcome to MrExcel
Try this in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("sheet1").CommandButton1.Enabled = True
End Sub

Change sheet name to suit

 

[CutterSoilMixing]

Hi & welcome to MrExcel
Try this in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("sheet1").CommandButton1.Enabled = True
End Sub

Change sheet name to suit

Thanks for your reply and the welcome!

I tried your code but I'm not sure if I did it correctly. Here's my code:

 Private Sub CommandButton1_Click()[BF5].Value = [BF5].Value + 1
[AZ6].Value = "=TODAY()"
CommandButton1.Enabled = False


End Sub
Private Sub Workbook_Open()
CommandButton1.Enabled = True
End Sub

I'm not sure what the ThisWorkbook module is...as I said before, very inexperienced!

Thanks!

 

[Fluff]

In the VB editor you should have a window entitled "project" (top left of the editor) which has a list of the sheets & modules in the workbook.
One of those will be entitled ThisWorkbook, double click on that and paste this

Private Sub Workbook_Open()
Sheets("[COLOR=#ff0000]sheet1[/COLOR]").CommandButton1.Enabled = True
End Sub

into the codewindow that opens up.
You will need to change the part in red to match the name of the sheet containing the button