Use of Macros with protected sheets

[mesteptoe]

Is this possible?

I have a series of Worksheets with various formulae on, and cells where data is to be entered. I have locked the formulae cells so that when I put password protection on each Worksheet the formulae cells cannot be altered by mistake - others will use the Worksheet and I don't want it corrupted.

However I normally like to enter macros on sheets to make it easier to move around, e.g.

"Go To" - to move to a particulat Cell on a Worksheet, or to a Cell in another worksheet

"Save and Close" - to save the workbook and quit the Workbook

However, the Macros won't work unless I unprotect the Worksheet. Is there a way of using Macros on a protected Worksheet?

Thanks


Graham

 

[xld]

In your macro, unprotect the sheet, do your stuff, and protect it again.

 

[VoG]

You can unprotect it with code

ActiveSheet.Unprotect password:="xxx"
'
'your code
'
ActiveSheet.Protect password:="xxx"

 

[mesteptoe]

Hi

But if I insert that extra Code in the Macro, and the Macro is designed to move to a Cell in a completely different Worksheet, or even to Close the Workbook completely, will the instruction to Protect the previous Worksheet still work?

Thanks

 

[VoG]

Can you give some examples of your macros. Preferably short ones.

 

[mesteptoe]

Sub goto_SUMMARY()
'
' goto_SUMMARY Macro
' Macro recorded 31/10/2005 by Graham Spicer
'
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
'
Sheets("SUMMARY").Select
ActiveWindow.SmallScroll ToRight:=-2
ActiveWindow.LargeScroll Down:=-2
Range("B2:B3").Select
End Sub
<o> </o>
<o> </o>

<o> </o>
<o> </o>
Sub save_and_close()
'
' save_and_close Macro
' Macro recorded 14/07/2006 by Graham Spicer
'
<o> </o>
'

ActiveWorkbook.save
ActiveWorkbook.Close
End Sub

 

[VoG]

I don't think that protection should be an issue with your save and close macro. For the other one try

Sub goto_SUMMARY()
'
' goto_SUMMARY Macro
' Macro recorded 31/10/2005 by Graham Spicer
'

'
With Sheets("SUMMARY")
    .Unprotect password:="xxx"
    Application.Goto .Range("B2:B3")
    .Protect password:="xxx"
End With
End Sub

 

[mesteptoe]

Sorry to be a pain but wouldn't that Macro rely on having a Password protection for the Workbook as a whole rather than protection of individual Sheets. If I am moving from one sheet to the SUMMARY Sheet I need to maintain individual sheet protection on the Sheet I am moving from.

 

[VoG]

That code doesn't mind whether the sheet that you are moving from is protected or not.