Hiding a From Button

[alpine7411]

I have a spreadsheet that has 7 different sheets. In each sheet there
are 6 From Buttons that activate Macro's. All the sheets are locked
with the exception of a few unprotected cells (where they can enter
there data). I've locked 3 of the Form Buttons so that you have to
unlock the sheet to use the buttons. Is there a way so that once I
lock the sheet the 3 From Buttons dissappear and when I unlock the
sheet they reappear? I'm not sure where to even start with this one.

Thanks,
Keith

 

[Dave Peterson]

Can you add another button that does the locking?

Option Explicit
Sub testme()

With ActiveSheet
If .ProtectContents = True _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
'do nothing, already protected
Else
'sheet is unprotected, so hide buttons and protect it
.Buttons("button 1").Visible = False
.Buttons("button 2").Visible = False
.Buttons("button 3").Visible = False
.protect password:="YourPasswordHere"
End If
End With

End Sub

 

[alpine7411]

Can you add another button that does the locking?

Option Explicit
Sub testme()

With ActiveSheet
If .ProtectContents = True _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
'do nothing, already protected
Else
'sheet is unprotected, so hide buttons and protect it
.Buttons("button 1").Visible = False
.Buttons("button 2").Visible = False
.Buttons("button 3").Visible = False
.protect password:="YourPasswordHere"
End If
End With

End Sub

Dave,

Here is what i have for locking all the sheets.

Private Sub CommandButton9_Click()


'
' LockAllSheets Macro
' Macro recorded 8/15/2007 by Keith Ammons
'

'
If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
MsgBox "Contents Protected. Must have Authorization."
Exit Sub
End If

Application.ScreenUpdating = False
Sheets("ROM Draft").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("BASE BUILDING").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("RENOVATION").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROM Source").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROM Product").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("TABLE").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("COVER").Select
ActiveSheet.Protect Password:="BACCW", DrawingObjects:=True,
Contents:=True, Scenarios:=True


End Sub

I see where your going with this. However i'm not sure where the code
should go.

 

[Dave Peterson]

This relies on the password being the same, the way you protect it being the
same and the button names being the same (button 1, button 2, and button 3):

Option Explicit
Private Sub CommandButton9_Click()

Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr As Long

If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
MsgBox "Contents Protected. Must have Authorization."
Exit Sub
End If

mySheetList = Array("ROM Draft", _
"Base building", _
"Renovation", _
"ROM Source", _
"ROM Product", _
"Table", _
"Cover")

myPWD = "BACCW"

For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList)
For bCtr = 1 To 3
.Buttons("Button " & sCtr).Visible = False
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next bCtr
End With
Next sCtr

End Sub

Untested, but compiled.

 

[alpine7411]

This relies on the password being the same, the way you protect it being the
same and the button names being the same (button 1, button 2, and button 3):

Option Explicit
Private Sub CommandButton9_Click()

Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr As Long

If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
MsgBox "Contents Protected. Must have Authorization."
Exit Sub
End If

mySheetList = Array("ROM Draft", _
"Base building", _
"Renovation", _
"ROM Source", _
"ROM Product", _
"Table", _
"Cover")

myPWD = "BACCW"

For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList)
For bCtr = 1 To 3
.Buttons("Button " & sCtr).Visible = False
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next bCtr
End With
Next sCtr

End Sub

Untested, but compiled.













--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

I'm getting an error when i try to run it. The error says "Object
doesn't support this property or method". It dosen't like the line:

..Buttons("Button" & sCtr).Visible = False


Why am i getting this error? I've had problems working with the
".Buttons" command before.

The code so far is:

Private Sub CommandButton9_Click()


Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr As Long

mySheetList = Array("ROM Draft", _
"BASE BUILDING", _
"RENOVATION", _
"ROM Source", _
"ROM Product", _
"TABLE", _
"COVER")


myPWD = "BACCW"


For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList)
For bCtr = 1 To 3
.Buttons("Button " & sCtr).Visible = False
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next bCtr
End With
Next sCtr


End Sub

 

[Dave Peterson]

Do you have 3 buttons on each sheet?

And are those buttons named "button 1", "button 2" and "button 3"?

 

[alpine7411]

Do you have 3 buttons on each sheet?

And are those buttons named "button 1", "button 2" and "button 3"?
















--

Dave Peterson- Hide quoted text -

- Show quoted text -

I have 6 buttons on each of the 7 sheets. 3 of those 7 should
disappear once i lock the sheet. Those 3 buttons are named:
CommandButton1, CommandButton2, and CommandButton3. They are named the
same on all 7 sheets.

 

[Dave Peterson]

When you wrote From (sic) button, I thought that you meant that you used the
Forms toolbar's buttons--not commandbuttons from the control toolbox toolbar.

This fixes the button/commandbutton confusion and also fixes a typo in my code:

Option Explicit
Private Sub CommandButton9_Click()

Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr As Long

If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
MsgBox "Contents Protected. Must have Authorization."
Exit Sub
End If

mySheetList = Array("ROM Draft", _
"Base building", _
"Renovation", _
"ROM Source", _
"ROM Product", _
"Table", _
"Cover")

myPWD = "BACCW"

For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList(sCtr)) '<---added (sctr) here!
For bCtr = 1 To 3
.OLEObjects("Commandbutton" & bCtr).Visible = False
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next bCtr
End With
Next sCtr

End Sub