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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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,257
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.
 

Forum statistics

Threads
1,140,940
Messages
5,703,285
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top