PDA

View Full Version : Solved: How do you run ThisWorkbook code from a module?



nousername
03-22-2008, 08:09 PM
I am trying to run some code contain in ThisWorkBook under the sub Workbook_SheetSelectionChange. I am trying to use some code in module1 to toggle the ThisWorkbook code on and off when the user select that option under the contextmenu.

Where am I going wrong with the code below?

Module1 Code:

Public bSwitch As Boolean
Public bRw As Boolean

Sub MyTestCode()

'Enable user to switch on/off with a right click prompt
If bSwitch Then
If MsgBox("Shut off", 36) = 7 Then Exit Sub

Else
If MsgBox("Turn on", 36) = 7 Then

ThisWorkbook.Test

End If

'Toggle boolean variable on/off switch
If Selection.Rows.Count > 1 Then
bRw = False
Else
bRw = True
End If
bSwitch = Not bSwitch

End If
End Sub

ThisWorkbook Code:

Public Sub Test()
If Not bSwitch Then Exit Sub
Else
ThisWorkbook.Workbook_SheetSelectionChange
End If
End Sub


Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'If we are running procedure or not:
If Not bSwitch Then Exit Sub

' More code is located here removed for posting.

End Sub

Bob Phillips
03-23-2008, 02:39 AM
Two things.

Code like this



If Not bSwitch Then Exit Sub
Else
ThisWorkbook.Workbook_SheetSelectionChange
End If


is just plain wrong, you are mixing a single line If with an If ... Else structure. It should be


If Not bSwitch Then
Exit Sub
Else
ThisWorkbook.Workbook_SheetSelectionChange
End If


You do this in two places.

Secondly, the event procedure you are calling has arguments, so you must pass parameter values to the procedure arguments for it to run.

mdmackillop
03-23-2008, 03:31 AM
Hi Nousername,
When you post code here, select it and click the VBA button to format it as shown. The Code tags don't do that
Regards
MD

nousername
03-23-2008, 08:54 AM
Here is a sample project of what I am trying to do.

rbrhodes
03-24-2008, 02:18 AM
HI nn,

and here's your sample back... I think you were trying too hard? <g>

Dusty

nousername
03-24-2008, 05:03 AM
YOU ROCK!! The code works just like I wanted it too!!

THANKS!!