Running a macro to edit one line in existing VBA
 

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.....

Running a macro to edit one line in existing VBA
 

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.....

Running a macro to edit one line in existing VBA
 

Thanks very much, exactly what I was looking for.