Pass variable from Private Sub Worksheet_SelectionChange to a Module ?

[JumboCactuar]

Hi,
having trouble figuring this out, i have the following sheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Not Intersect(Target, Range("Z2:Z50")) Is Nothing Then
            If Target.Offset(0, 1).Value = Target.Offset(0, 2).Value Then
                'do nothing
            Else
                myvar1 = target.value
                call Macro2
            End If
        End If
End Sub

and i want to pass the myvar1 to a macro but unsure how

so i can then use myvar1 like this example

Sub Macro2()
If myvar1 > 50 then
msgbox (myvar1 & "Greater than 50")
End If
End Sub

Appreciate any help

 

[JumboCactuar]

I think I can just use:

myvar1 = active cell.value
At top of Macro2

Still curious about the variable though if it can be passed from sheet to module

 

[JoeMo]

Try making the procedure a part of the sheet code, not a standard procedure. Copy and paste this to the sheet (right-click sheet tab>View Code and paste):

Dim myVar1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Target(1)
If Not Intersect(Target, Range("Z2:Z50")) Is Nothing Then
    myVar1 = Target.Value
    Call myMacro
End If
End Sub
Sub myMacro()
x = myVar1
MsgBox "Called from worksheet selection_change event code" & vbNewLine & "myvar1 = " & x
End Sub

 

[Norie]

Try changing Macro2 like this.

Sub Macro2(theVar)
    If theVar > 50 Then
        MsgBox theVar & " - Greater than 50")
    End If
End Sub

You can then call it from the SelectionChange event like this.

myvar1 = Target.Value
Call Macro2(myvar1)

 

[JumboCactuar]

Thanks
Didn't know you could do it like this