Excel help needed - creating a public variant

[alrodopial]

It must something really simple but I don't know how to declare/create variants, so:

I want to create a variant in the Worksheet_Change event and then use it in a module / macro.

The below code creates the variant but it does not keep it for the next macro.

Code: Select all

Public MyVar As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim MyVar As Variant
   
    Application.EnableEvents = False
   
    If Target.Columns.Count = 1 Then
        MyVar = "EEEEE"
        Call test1
    End If
   
    Application.EnableEvents = True
       
End Sub

and the module

Code: Select all

Public MyVar As Variant

Sub test1()
   
    Sheets("Sheet2").Range("A1") = MyVar
   
End Sub

Where I'm wrong?

[throwmeadisc]

Just put

Code: Select all

Public MyVar As Variant

in the top of the module and nowhere else. That also means don't Dim it in any Sub.

That's it.

Al

[alrodopial]

Just put

Code: Select all

Public MyVar As Variant

in the top of the module and nowhere else. That also means don't Dim it in any Sub.

That's it.

Al

So simple when you know what you are doing.
Thanks mate