Keep Getting Error With Public Variable.

[Evagrius Ponticus]

Hi all,

I have a number of userforms that all reference the same worksheet. I wanted to create a public variable for that worksheet so I don't have to keep referencing it in each userform/commandbuttons etc. So I inserted a module and placed this declaration.

Option Explicit
Public MySh As Worksheet
Set MySh = ActiveWorkbook.Sheets("sheetName")

And I referenced it in a UserForm commandbutton

Private Sub CommandButton2_Click()
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i5") = Me.TextBox1
End Sub

What am I doing wrong?

 

[Richard Schollar]

You can only assign a reference to the variable from within a sub or function - so this bit:

Set MySh = ActiveWorkbook.Sheets("sheetName")

Needs to appear inside a sub or function (for instance, in the Workbook_Open event, or in the UserForm_Initialize event).

 

[Evagrius Ponticus]

Richard - it still doesn't work, here is what I have - unless I misunderstood you -

private Sub Workbook_Open()
Public MySh As Worksheet
Set MySh = ActiveWorkbook.Sheets("Passwords")
Application.OnTime Now + TimeValue("00:00:02"), "OpenW"

End Sub

I use it here but still get a variable error? Where is my mistake?

Private Sub CommandButton2_Click()
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i5") = Me.TextBox1
End Sub

 

[Richard Schollar]

The declaration itself ie:

Public MySh As Worksheet

Needs to appear in the declaration section of a standard module 9ie at the top of it before any Subs or Functions (and not inside a sub or a function).

It should then work.

 

[Evagrius Ponticus]

Hi Richard,

This is what I have now . . This works for all the procedures in that module, but when I try to use MySh in a seperate module, it doesn't recognize the variable.

Option Explicit
Public MySh As Worksheet


Private Sub CommandButton2_Click()
Set MySh = ActiveWorkbook.Sheets("sheetname")
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i6") = Me.TextBox1
End Sub

 

[RoryA]

I would guess you put it in the code module for a worksheet. In point of fact, you don't need the variable anyway - just use the sheet's codename. In the project explorer window in the VBEditor, you will see your sheets listed as:

Sheet1 (Passwords)

where the name in parentheses is the tab name. In code you can simply use the Sheet1 part - for example:

Sheet1.Range("A1").Value = 4

HTH

 

[Evagrius Ponticus]

well, I put the Public MySh as worksheet in a Userform module - this only works for all procedures in that module. I also tried putting it in the workbook module to run at the open event - and thus use it in my userform modules, but that didn't work either. I would like to figure out how to declare a variable that can be used by all projects/modules

 

[RoryA]

You need to put the Public declaration in a normal module, not a class module.

 

[Evagrius Ponticus]

HA! That was my mistake, it was in a class module - it works now. Thanks Richard, Rorya!

 

[RoryA]

Glad to help. Just as an additional point, using the sheet codename has the advantage that your code will still work if someone renames the sheet in Excel!