Worksheet Activate Event Not Working

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,533
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hope someone is able to help me.

I have a worksheet that has a Private Worksheet_Activate function associated with it. My understanding is that this code will be triggered each time the worksheet is "activated"?

Code: 
Private Sub Worksheet_Activate()
  Call reset3
  ActiveSheet.Protect
End Sub

Code: 
Sub reset3()
'
' A routine to default and hide all user entry fields prior to involuntary entering of a record data entry
' Called by worksheet_activate (Edit) event & positive pivvottable
'
    With Application
        .ScreenUpdating = False
        .EnableEvents = False 'disable worksheet change
        .DisplayAlerts = False
    End With
    Sheets("Enter_Miss").Activate
    ActiveSheet.Unprotect
' COMMON1
    Range("$J$8").Value = "0"
    Range("$D$10").Value = ""
    Range("$J$10").Value = ""
    Range("$J$11").Value = ""
    Range("$T$11").Value = ""
    Range("$AA$11").Value = ""
    Range("$L$8:$M$8").ClearContents
    Range("$P$8:$AA$8").ClearContents
' DIAMONDS
    Range("$e$15").Value = ""
    Range("$J$15").Value = ""
    Range("$P$15").Value = "NA"
    Range("$X$15").Value = "NA"
    Range("$E$16").Value = "Yes"
    Range("$J$16").Value = "NA"
    Range("$P$16").Value = "NA"
    Range("$X$16").Value = "NA"
    Range("$E$17").Value = "NA"
    Range("$J$17").Value = "NA"
    Range("$X$17").Value = "NA"
' FIELDS
    Range("$F$21").Value = ""
    Range("$F$22").Value = ""
' COURTS
    Range("$F$26").Value = ""
' COMMON2
    Range("$G$30").Value = ""
    Range("$G$31").Value = ""
' HIDE ACTIVITY ROWS
    ActiveSheet.Unprotect
    Rows("7:37").Select
    Selection.EntireRow.Hidden = True
    Range("$E$5:$F$5").Locked = False
    Range("$E$5") = "0"
    Application.EnableEvents = True
    ActiveSheet.Protect
End Sub

When I manually step through my tabs, when I select this particular worksheet, the code is executed and performs the desired results. (reset3 basically defaults a series of cells and hides some rows and returns the user back to this worksheet)

When I run the application's code (not manually stepping through worksheets), the worksheet does not update when activated through VB code. I thought it might have something to do with EnableEvents, but I assume not since after the code is run, I get the results with manual worksheet selection. (although when I step through the code line by line, I still get no results and ?EnableEvents in the immediate window comes up blank ... assume false)

Any advice for a resolution?

Jenn
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
WorkSheet_Activate is an Event. When you disable events, it will not run. I assume, you are activating Sheets("Enter_Miss") and the Activate_Event lies in that module. If so, you do not need this line
Code: 
Sheets("Enter_Miss").Activate
Instead, use
Code: 
Sheets("Enter_Miss").Unprotect
I think that will solve your problem as it appears you are calling the macro with the Activate Event, which fires, then turning off events and reactivating the sheet, which doesn't fire.

lenze
 
Upvote 0
In the Immediate Window it should be

?application.enableevents

You could add a debugging message in the event code:

Code: 
Private Sub Worksheet_Activate()
MsgBox "Code fired"
  Call reset3
  ActiveSheet.Protect
End Sub

which will tell you if it has been triggered.
 
Upvote 0
Augh!

As VoG suggested, I put the debugging message into the worksheet_activation code.
Based on that, it is obvious that the worksheet_activation code is not firing when outside code "activates it."

I have simplified my code just as a means of testing. Baby steps. I can't even get it to trigger with simple code. This worksheet is being called from another workbook ...


Code: 
...
    If Range("D27") < 1 Then
        MsgBox ("All information has been confirmed." & vbCrLf & "You may proceed with workorder processing.")
        Workbooks("Groups.xls").Activate
        ActiveWorkbook.Save
        MsgBox ("GROUPS.XLS saved despite having no changes.")
        Exit Sub
    
    End If
    MsgBox ("You have " & Range("D27") & " records with no associated group information." & vbCrLf & "These deficiencies must be resolved before proceeding.")
    MsgBox ("Open Groups.xls")
    ChDir "E:\SportsOps 2009\"
    Workbooks.Open Filename:="E:\SportsOps 2009\Groups.xls"
    Exit Sub
End Sub

The worksheet I wish to activate at this point is "Enter_Miss". As it is now, it goes there just because it's the first worksheet in the book. In addition to triggering the activation event, I first need to ensure the user is taken to the correct sheet regardless as to the layout of the workbook previously saved.

Jenn
 
Upvote 0
I think this is the problem

Rich (BB code): 
With Application
        .ScreenUpdating = False
        .EnableEvents = False 'disable worksheet change
        .DisplayAlerts = False
End With

which disables all events.

Try this

Rich (BB code): 
Sub reset3()
'
' A routine to default and hide all user entry fields prior to involuntary entering of a record data entry
' Called by worksheet_activate (Edit) event & positive pivvottable
'
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    Sheets("Enter_Miss").Activate
    ActiveSheet.Unprotect
    Application.EnableEvents = False 'disable worksheet change

' COMMON1
    Range("$J$8").Value = "0"
    Range("$D$10").Value = ""
    Range("$J$10").Value = ""
    Range("$J$11").Value = ""
    Range("$T$11").Value = ""
    Range("$AA$11").Value = ""
    Range("$L$8:$M$8").ClearContents
    Range("$P$8:$AA$8").ClearContents
' DIAMONDS
    Range("$e$15").Value = ""
    Range("$J$15").Value = ""
    Range("$P$15").Value = "NA"
    Range("$X$15").Value = "NA"
    Range("$E$16").Value = "Yes"
    Range("$J$16").Value = "NA"
    Range("$P$16").Value = "NA"
    Range("$X$16").Value = "NA"
    Range("$E$17").Value = "NA"
    Range("$J$17").Value = "NA"
    Range("$X$17").Value = "NA"
' FIELDS
    Range("$F$21").Value = ""
    Range("$F$22").Value = ""
' COURTS
    Range("$F$26").Value = ""
' COMMON2
    Range("$G$30").Value = ""
    Range("$G$31").Value = ""
' HIDE ACTIVITY ROWS
    ActiveSheet.Unprotect
    Rows("7:37").Select
    Selection.EntireRow.Hidden = True
    Range("$E$5:$F$5").Locked = False
    Range("$E$5") = "0"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub
 
Upvote 0
OK folks ... I have eliminated all EnableEvents actions from my code. It's become far to complicated turning it on and off all over the place. I'll work on getting things to work before I start monkeying around with that again.

Needless to say, it still isn't working when code in takes the focus of activity from workbook A to workbook b (where Enter_Miss resides).

The code focuses Enter_Miss but does not execute the work_sheet activation code. If I select another worksheet, and return to Enter_Miss, it works.

Jenn

New code (snippet from worksheet A)
Code: 
...
    If Range("D27") < 1 Then
        MsgBox ("All information has been confirmed." & vbCrLf & "You may proceed with workorder processing.")
        Workbooks("Groups.xls").Activate
        ActiveWorkbook.Save
        MsgBox ("GROUPS.XLS saved despite having no changes.")
        Exit Sub
    
    End If
      MsgBox ("You have " & Range("D27") & " records with no associated group information." & vbCrLf & "These deficiencies must be resolved before proceeding.")
      MsgBox ("Open Groups.xls")
      ChDir "E:\SportsOps 2009\"
      Workbooks.Open Filename:="E:\SportsOps 2009\Groups.xls"
      Worksheets("Enter_Miss").Activate
      Exit Sub
End Sub

{Enter_Miss} worksheet_activation
Code: 
Private Sub Worksheet_Activate()
  MsgBox ("Trigger Reset Code.")
  Call reset3
  ActiveSheet.Protect
End Sub

sub reset3 ...
Code: 
Sub reset3()
'
' A routine to default and hide all user entry fields prior to involuntary entering of a record data entry
' Called by worksheet_activate (Edit) event & positive pivvottable
'
    Sheets("Enter_Miss").Activate
    ActiveSheet.Unprotect
' COMMON1
    Range("$J$8").Value = "0"
    Range("$D$10").Value = ""
    Range("$J$10").Value = ""
    Range("$J$11").Value = ""
    Range("$T$11").Value = ""
    Range("$AA$11").Value = ""
    Range("$L$8:$M$8").ClearContents
    Range("$P$8:$AA$8").ClearContents
' DIAMONDS
    Range("$e$15").Value = ""
    Range("$J$15").Value = ""
    Range("$P$15").Value = "NA"
    Range("$X$15").Value = "NA"
    Range("$E$16").Value = "Yes"
    Range("$J$16").Value = "NA"
    Range("$P$16").Value = "NA"
    Range("$X$16").Value = "NA"
    Range("$E$17").Value = "NA"
    Range("$J$17").Value = "NA"
    Range("$X$17").Value = "NA"
' FIELDS
    Range("$F$21").Value = ""
    Range("$F$22").Value = ""
' COURTS
    Range("$F$26").Value = ""
' COMMON2
    Range("$G$30").Value = ""
    Range("$G$31").Value = ""
' HIDE ACTIVITY ROWS
    ActiveSheet.Unprotect
    Rows("7:37").Select
    Selection.EntireRow.Hidden = True
    Range("$E$5:$F$5").Locked = False
    Range("$E$5") = "0"
    ActiveSheet.Protect
End Sub

Jenn (scratching her head)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Vlookup From another workbook
Replies
0
Views
72
Sunil7598
S
K
  • Question
Compare multiple columns in 2 workbook's different worksheet, then copy and past values
Replies
2
Views
211
sijpie
sijpie
M
  • Question
VBA Lookup array
Replies
2
Views
218
mattless1
M
H
  • Question
Copy from multiple worksheets and multiple source files in a folder and paste into multiple sheets in target workbook
Replies
0
Views
60
Hs72711
H
R
  • Question
Copy Rows to existing Multiple worksheet based on condition and create new worksheet if one does not exist
Replies
1
Views
170
RPE
R

Forum statistics

Threads
1,196,252
Messages
6,014,264
Members
441,809
Latest member
pawansher2002

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top