Keep Getting Error With Public Variable.

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
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.

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

And I referenced it in a UserForm commandbutton

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

What am I doing wrong?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You can only assign a reference to the variable from within a sub or function - so this bit:

Code:
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

Well-known Member
Joined
May 24, 2007
Messages
1,467
Richard - it still doesn't work, here is what I have - unless I misunderstood you -

Code:
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?

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
The declaration itself ie:

Code:
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

Well-known Member
Joined
May 24, 2007
Messages
1,467

ADVERTISEMENT

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.


Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,491
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:
Code:
Sheet1 (Passwords)
where the name in parentheses is the tab name. In code you can simply use the Sheet1 part - for example:
Code:
Sheet1.Range("A1").Value = 4
HTH
 

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,491
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to put the Public declaration in a normal module, not a class module.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,491
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,896
Messages
5,627,512
Members
416,250
Latest member
darius_rebelo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top