disabling buttons

southpaw163

New Member
Joined
Aug 28, 2006
Messages
4
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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 :

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,723
Members
410,630
Latest member
JFORTH97
Top