| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding a From Button
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
On Aug 16, 7:53 pm, Dave Peterson wrote:
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 wrote: 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 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
On Aug 17, 11:54 am, Dave Peterson wrote:
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. wrote: On Aug 16, 7:53 pm, Dave Peterson wrote: 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 wrote: 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 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- 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
On Aug 17, 1:53 pm, Dave Peterson wrote:
Do you have 3 buttons on each sheet? And are those buttons named "button 1", "button 2" and "button 3"? wrote: On Aug 17, 11:54 am, Dave Peterson wrote: 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. wrote: On Aug 16, 7:53 pm, Dave Peterson wrote: 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 wrote: 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 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- 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- 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding a From Button
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 wrote: <<snipped 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hiding a macro button | Excel Programming | |||
| Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
| Hiding a Button | Excel Programming | |||
| hiding forms button | Excel Discussion (Misc queries) | |||
| HIDING A BUTTON | Excel Programming | |||
Linear Mode
