disabling buttons

[southpaw163]

I have a spreadsheet with a few buttons on it, one of which is "archive". Which when I press I want it to lock the entire sheet not allowing anything new to happen to the sheet. Also on the page are "add a new row" button and "delete selected row" button, I want these buttons to be disabled after I hit archive. How do I go about doing this? Can I disable the macros that run when you hit the add or delete buttons? Or can I disable the buttons all together? I would prefer to disable the macros, but either way would work for me. Thanks for any help.

Adrian

 

[Joe Was]

This works if your buttons are from the "Control" Toolbox, it will not work if you used "Form" Toolbox Buttons!

Note: UserForms use the Control Toolbox, the Control toolbox is one of three Button options available for use on the Worksheet as well!
The three buttons are Control, AutoShape and Form in order of flexability. The form button is the least usefull, but is the quickest to setup.

Whenever you post a question about these objects you must state which you used or where you built it from, AKA which menu!


Private Sub CommandButton2_Click()
'Control ToolBar Button code only!

'To permanintly turn Button 1 off:
'CommandButton1.Enabled = False

'Or to Toggle Button 1, "On or Off" each time this button is clicked:
If CommandButton1.Enabled = True Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub

Private Sub CommandButton1_Click()
'Control ToolBar Button code only!

MsgBox "Active!"
End Sub

 

[DominicB]

Good evening southpaw163

Joe has supplied you with a solution if you are using the Control Toolbox toolbar, but there isn't really a way to disable the buttons if you are using the forms toolbar, but you could use a macro to unassign / assign a macro to the buttons - it's not a pretty way, I'd suggest you move your code over to the Control Toolbox method, but heres the code :

'Assign button 1 to Mymacro
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Mymacro"

'Unassign Mymacro from button 1
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = ""

HTH

DominicB

 

[Tom Urtis]

on the page are "add a new row" button and "delete selected row" button, I want these buttons to be disabled after I hit archive. How do I go about doing this?

there isn't really a way to disable the buttons if you are using the forms toolbar

You can reference the Forms buttons by their name or in this case, their index placement on the sheet.


With ActiveSheet
.Buttons(1).Enabled = False
.Buttons(2).Enabled = False
End With



then set their enabled property to True when you want:

With ActiveSheet
.Buttons(1).Enabled = True
.Buttons(2).Enabled = True
End With



Again, this depends on whether or not you are really using Forms buttons, as opposed to the activex commandbuttons which Joe solved.