CODEWITHSUNDEEP
vbaexcel
MiguelH
MiguelH

Reputation: 1425

Workbook Open event and detecting this event in the Worksheet code

I am trying to cut down on some duplicate coding in a .xlsm workbook. To do this, I have created a number of subs in the "Workbook Open" area that are now called from subs within Sheet 1. (Initially there were repeated code segments in both these). This all works fine!

My question is this ...

Is it possible for code in Sheet 1 to know that it is being activated by the Workbook Open event?

For example, I have a combo box that is populated by the Workbook_open event which then fires off the sheet 1 Combo box "Change" event. However, I only want this change event to triggered by the user once the Workbook has been opened.

I've tried setting a global variable in Workbook Open but this only has scope within this event.

What would be the best workaround here?

Could I use a Workbook Module?
Should I write the variable to a cell in the worksheet and clear this out at the end of the event?
Some other solution?

Part of the Workbook Open code ..

Private Ws_Open As Boolean
Private Sub Workbook_Open()
'
' set up drop down filter list
'
Range("A1").Select
With ThisWorkbook.Worksheets(1).Cmb_filter
    .Clear
    .AddItem "None"
    .AddItem "60% or less"
    .AddItem "70% or less"
    .AddItem "80% or less"
    .AddItem "90% or less"
    .ListIndex = 0
End With

Part of the Sheet 1 code ...

Private Sub Cmb_filter_Change()
 Dim intvalue As Integer
 Cmb_filter.BackColor = vbWhite
 Application.Run "'" & ThisWorkbook.Name & "'!Thisworkbook.clear_it"
..etc

Upvotes: 1

Views: 1454

Answers (3)

R3uK
R3uK

Reputation: 14537

How to use a Public/Global variable to prevent the events from triggering

Place this in a regular module with the rest of your code :

Private Ws_Open As Boolean

Private Sub Cmb_filter_Change_Proc()
If Ws_Open Then Exit Sub
    Dim intvalue As Integer
    Cmb_filter.BackColor = vbWhite
    Application.Run "'" & ThisWorkbook.Name & "'!Thisworkbook.clear_it"
    '..etc
End Sub

Public Sub Wb_Open_Proc()
'
' set up drop down filter list
'
Ws_Open = True
Range("A1").Select
With ThisWorkbook.Worksheets(1).Cmb_filter
    .Clear
    .AddItem "None"
    .AddItem "60% or less"
    .AddItem "70% or less"
    .AddItem "80% or less"
    .AddItem "90% or less"
    .ListIndex = 0
End With

'rest of your code

Ws_Open = False
End Sub

And these in ThisWorbook and Sheet modules :

Private Sub Workbook_Open()
    Call Wb_Open_Proc
End Sub

Private Sub Cmb_filter_Change()
    Call Cmb_filter_Change_Proc
End Sub

Proposition that won't work for this specific case (Control in a Worksheet), but will for most of the other events of Sheet, Workbook, ...

Simpler than detecting how the event is triggered, you can disable the events during your manipulation in the code with Application.EnableEvents = False

So your code will look like this :

Private Ws_Open As Boolean
Private Sub Workbook_Open()
'
' set up drop down filter list
'
Application.EnableEvents = False
Range("A1").Select
With ThisWorkbook.Worksheets(1).Cmb_filter
    .Clear
    .AddItem "None"
    .AddItem "60% or less"
    .AddItem "70% or less"
    .AddItem "80% or less"
    .AddItem "90% or less"
    .ListIndex = 0
End With
Application.EnableEvents = True

Upvotes: 2

MiguelH
MiguelH

Reputation: 1425

Ok. Found it. Seems that I do need a Module. I couldn't get the above solution to work. The Public declaration in the Workbook worked ok, but the Worksheet could not access the variable?

In the end I created Module1 with

Public Ws_Open As Boolean

This then made the variable accessible to all workbook objects.

Previously I had tried the Module solution, but with a Private variable declaration! Doh!

Upvotes: 1

Rory
Rory

Reputation: 34045

You need a Public variable. For example: ThisWorkbook module:

Public Ws_Open As Boolean
Private Sub Workbook_Open()
'
' set up drop down filter list
'
Range("A1").Select
ws_open = True
With ThisWorkbook.Worksheets(1).Cmb_filter
.List =  Array("None", "60% or less", "70% or less", "80% or less", "90% or less")
    .ListIndex = 0
End With
ws_Open = False
End Sub

then in the worksheet:

Private Sub Cmb_filter_Change()
 Dim intvalue As Integer
 If ThisWorkbook.ws_Open then exit sub
 Cmb_filter.BackColor = vbWhite
 Application.Run "'" & ThisWorkbook.Name & "'!Thisworkbook.clear_it"

Upvotes: 1

Related Questions