Help with Private Sub Worksheet_Activate()

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Help with Private Sub Worksheet_Activate()

Post by ABabeNChrist »

I’m having a problem using Private Sub Worksheet_Activate() to run a macro and to control scroll area
I know it something simple; I tried a few different ways

Code: Select all

Private Sub Worksheet_Activate()
    ColorandBold 'Macro
    Worksheets("Summary").ScrollArea = "A1:AM125"
End Sub

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with Private Sub Worksheet_Activate()

Post by HansV »

Have you placed the code in the worksheet module? It won't do anything if you place it in a standard module.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Help with Private Sub Worksheet_Activate()

Post by ABabeNChrist »

Yes it is in a worksheet module and not a workbook module.
They seem to run separately but not together, no sure why

Update: Could it be sheet protection causing this?
Double Update: I added

Code: Select all

Private Sub Worksheet_Activate()
    ActiveSheet.Unprotect Password:=""
      Call ColorandBold 'Macro
      Worksheets("Summary").ScrollArea = "A1:AM125"
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""
End Sub
Now it seems to work :clapping:

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with Private Sub Worksheet_Activate()

Post by HansV »

Alternatively, you could protect the sheet once using a line like this:

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:="", UserInterfaceOnly:=True

After that, you can manipulate the sheet in VBA without needing to unprotect and reprotect it.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Help with Private Sub Worksheet_Activate()

Post by ABabeNChrist »

Very Cool thank you Hans