Global Variable

[Patrick Simonds]

I am confused, I want to create a global variable called: bSELCTIONCHANGE. I
read through the other posts and thought that I had to place the global
variable before the Private Sub. But I get an error saying I have an Invalid
Outside Procedure. Where does the global variable belong? I have another
macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from
running.


bSELCTIONCHANGE

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If bSELCTIONCHANGE Then

'Backup Board
If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15,
D17, D19")) Is Nothing Then Sunday_Route_Selection.Show

'Relief Board
If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30,
D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then
Sunday_Route_Selection.Show

'Part Time Available
If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58,
D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show

'Overtime and Miscellaneous Assignments
If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94,
D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then
Sunday_Route_Selection.Show

'Routes To Cover
If Not Application.Intersect(Target, Range("D119147")) Is Nothing Then
Sunday_Routes_to_Cover.Show

End If

End Sub

 

[Rowan Drummond]

You need to actually declare the variable eg
Public bSELCTIONCHANGE As Boolean

Hope this helps
Rowan

 

[Patrick Simonds]

I am sorry but can you walk me through this?

Should it look like

Public Sub bSELCTIONCHANGE()

bSELCTIONCHANGE As Boolean

End Sub

 

[Rowan Drummond]

It should be like this:

Public bSELCTIONCHANGE as Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If bSELCTIONCHANGE Then
etc

Regards
Rowan

 

[Patrick Simonds]

I appreciate your time. I guess I do not know in which module to place
this. I assume that as Public it could go into any module. Should I put it
at the top of my Auto_Open Macro? I placed the following code around my
WorkSheet Code:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If bSELCTIONCHANGE Then

If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15,
D17, D19")) Is Nothing Then Sunday_Route_Selection.Show

End If
End Sub

And I have used:

bSELCTIONCHANGE = False and bSELCTIONCHANGE = True

On my userform which determines if bSELCTIONCHANGE is true or false. When
I click on the option button which should change it to True:

If OptionButton2.Value = True Then

bSELCTIONCHANGE = True

Unload UserFormAccess
UserFormPassword.Show
End If

It has does not seem to turn bSELCTIONCHANGE to true, since the macro it is
wrapped around does not run.

 

[Rowan Drummond]

Hi Patrick

You should declare public variables in a standard module not a class
module. The code modules behind sheets and userforms are class modules
so you should insert a new standard module and use this to declare you
public variable:

Public bSELCTIONCHANGE as Boolean

Hope this helps
Rowan

 

[Dave Peterson]

You have another reply at your other thread.

I am confused, I want to create a global variable called: bSELCTIONCHANGE. I
read through the other posts and thought that I had to place the global
variable before the Private Sub. But I get an error saying I have an Invalid
Outside Procedure. Where does the global variable belong? I have another
macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from
running.

bSELCTIONCHANGE

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If bSELCTIONCHANGE Then

'Backup Board
If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15,
D17, D19")) Is Nothing Then Sunday_Route_Selection.Show

'Relief Board
If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30,
D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then
Sunday_Route_Selection.Show

'Part Time Available
If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58,
D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show

'Overtime and Miscellaneous Assignments
If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94,
D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then
Sunday_Route_Selection.Show

'Routes To Cover
If Not Application.Intersect(Target, Range("D119147")) Is Nothing Then
Sunday_Routes_to_Cover.Show

End If

End Sub

 

[Patrick Simonds]

Thank you for all your help

Hi Patrick

You should declare public variables in a standard module not a class
module. The code modules behind sheets and userforms are class modules so
you should insert a new standard module and use this to declare you public
variable:

Public bSELCTIONCHANGE as Boolean

Hope this helps
Rowan

 

[Rowan Drummond]

You're welcome.

Thank you for all your help