CAN ANYONE HELP????

Please????

I am trying to run a simple macro in a protected workbook.

The workbook is protected adn so too are the worksheets, so the first
part of the macro is to remove the protection throughout - easy. The
second part is that I have the following code on several of the
worksheets which simply maintains the cell formatting when protected
(as some cells are allowed access for editting):


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

ActiveWorkbook.Unprotect Password:="techserv"

Me.Unprotect Password:="techserv"


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Worksheets("Formats").Range("q10:au131").Copy
Me.Range("q10:au131").PasteSpecial Paste:=xlPasteFormats
Target.Select



With Application
.EnableEvents = True
.ScreenUpdating = True
End With


Me.Protect Password:="techserv"
ActiveWorkbook.Protect Password:="techserv"

End Sub


As this is effectively running all the time as soon as I (the owner of
the workbook) want to edit it I have to manually go to the VBA window
and type in End Sub after Private Sub to stop it while I work on it.

What code do I need to add to the first macro (which removes the
protection) so that it will automatically do this for me????

Any help would be much appreciated.....

Hi J,

Application.EnableEvents = False

'Your code

Application.EnableEvents = True


You could alternatively re-write the code to avoid selections, which are
usually unnecessary and inefficient and, in this case, fire your selection
change event code.


---
Regards,
Norman


wrote in message
ups.com...
CAN ANYONE HELP????

Please????

I am trying to run a simple macro in a protected workbook.

The workbook is protected adn so too are the worksheets, so the first
part of the macro is to remove the protection throughout - easy. The
second part is that I have the following code on several of the
worksheets which simply maintains the cell formatting when protected
(as some cells are allowed access for editting):


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

ActiveWorkbook.Unprotect Password:="techserv"

Me.Unprotect Password:="techserv"


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Worksheets("Formats").Range("q10:au131").Copy
Me.Range("q10:au131").PasteSpecial Paste:=xlPasteFormats
Target.Select



With Application
.EnableEvents = True
.ScreenUpdating = True
End With


Me.Protect Password:="techserv"
ActiveWorkbook.Protect Password:="techserv"

End Sub


As this is effectively running all the time as soon as I (the owner of
the workbook) want to edit it I have to manually go to the VBA window
and type in End Sub after Private Sub to stop it while I work on it.

What code do I need to add to the first macro (which removes the
protection) so that it will automatically do this for me????

Any help would be much appreciated.....

Thanks very much, exactly what I was looking for.