Macro Buttons

[sully2]

Hi guys, thanks in advance for any help here, I have a small worksheet, nothing to advanced, however I have 2 buttons in it with 2 very short Macros attached to them, when I password protect the sheet I am unable to use these buttons, I get a "pop up" screen with 4 options, "Help", "End", "Debug" & "Cancel", does anyone have a method I can use to be able to activate these buttons when the worksheet is protected? Only the "Cells" that contain "Formulas" are locked on this sheet. The buttons were made from "Insert Controls" Options on "Developer Tab" and the macros were recorded. When I protect the sheet, I don't see an option in the pop up box to allow buttons with macros attached.

Again, Thank you in advance

 

[VoG]

You can unprotect then reprotect the sheet in your macros. Example:

ActiveSheet.Unprotect Password:="Abc"
'rest of code
ActiveSheet.Protect Password:="Abc"

 

[sully2]

Sorry, I am a bit of a noob when it comes to macros VoG, so am I right in thinking what you are saying is that I can create a new button and put your code to it? when pressed this will ask for a password to "Protect / Unprotect?

 

[AlphaFrog]

I get a "pop up" screen with 4 options, "Help", "End", "Debug" & "Cancel"

Only the "Cells" that contain "Formulas" are locked on this sheet.

Press the Debug button to highlight the line of code that errors. What's line of code doing?
Apparently it's trying to access a locked cell.

 

[VoG]

Sorry, no that isn't what I meant.

You can add the two lines of code that I posted near the beginning and end of each macro. You need to change Abc to the actual password.

 

[sully2]

Here is the line that has the error;

ActiveCell.FormulaR1C1 = "=R[3]C[-5]"

I'm not sure what this means?

 

[sully2]

ok VoG, I tried the code at the beginning and end of each macro... nothing happens, I appreciate your effort to help me out. But I'm outa my depth when it comes to macros, but here is what the macro looks like;

Sub Show_Wages()
'
' Show_Wages Macro
'

'ActiveSheet.Unprotect Password:="drive"
'
Range("H7").Select
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("I12").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-5]-R[-5]C[-1]"
Range("A1").Select
'ActiveSheet.Protect Password:="drive"
End Sub

and

Sub Discard_Wages()
'
' Discard_Wages Macro
'

'ActiveSheet.Unprotect Password:="drive"
'
Range("I12").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-5]"
Range("H7").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
'ActiveSheet.Protect Password:="drive"
End With
Range("A1").Select
End Sub


I have no doubt done something wrong, anyhow thanks...

 

[sully2]

Yes Alphafrog, there are locked cells that the macro changes, obviously when I protect the sheet the macro isn't allowed to make the changes the the font & formula, I'm not sure how to proceed, but thanks, at least I am a little clearer on why it's not working thanks to your knowledge.

Cheers

 

[AlphaFrog]

Remove the leading single quote from the .Unprotect and .Portect lines in your code above.

'ActiveSheet.Unpotect Password:="drive"

[color=darkblue]Sub[/color] Show_Wages()
    [color=green]'[/color]
    [color=green]' Show_Wages Macro[/color]
    [color=green]'[/color]
    ActiveSheet.Unprotect Password:="drive"
    
    [color=darkblue]With[/color] Range("H7").Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Range("I12").FormulaR1C1 = "=R[3]C[-5]-R[-5]C[-1]"
    
    ActiveSheet.Protect Password:="drive"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]