Global worksheet variable for userform in excel

Question

I am building a userform that adds data to a worksheet. I have a bunch of sub-routines for my userform, but I'm having issues accessing the variable I set to my worksheet. It's currently a local variable (in a sub) and have tried placing it in a module as discussed here: Can a worksheet object be declared globally in Excel VBA. I've done some further searching but didn't quite get the results I wanted.

I'm quite new to VBA and new to programming in general. I've used Excel quite a bit and want to do more by creating macros and such using VBA. Here's the code I'm currently using:

sub savebutton_click()
    Dim trees As Worksheet
    Set trees = ThisWorkbook.Sheets("Sheet1")
    trees.Cells(1, 1) = Me.TextTreeName
    trees.Cells(1, 2) = Me.TextTreeType
End sub

Obviously I cannot access local variables from another subroutine, so I have to make 'trees' global. I created a module and replaced Dim with Public, placed the second line of code in Workbook_Open(), and then tried accessing the 'trees' variable. I get an "Invalid outside procedure" and highlights the code in Workbook_Open() when I hit 'Debug'.

Essentially I just want to be able to access, say, trees.Cells(5,6) from any sub and I'm not sure what else to do with it. Any ideas? Thank you!

Maarten van Stam Maarten van Stam · Accepted Answer · 2016-04-23T01:36:43

It must be a matter of placement ... if you open the VBA IDE and select the ThisWorkbook item in your VBAProject. Double click that item to open it, Select Workbook in the top left dropdown, Select Open in the top right dropdown. This will create your Workbook_Open Sub in the ThisWorkbook object:

Private Sub Workbook_Open()
    Set trees = ThisWorkbook.Sheets("Sheet1")
End Sub

Right click your VBAProject and Add Module. In that module you put the global variable:

Public trees As Worksheet

Next you create your form, put the button on that and double click the button. This will generate the button event:

In my case:

Private Sub CommandButton1_Click()
     trees.Cells(1, 1) = Me.TextTreeName
     trees.Cells(1, 2) = Me.TextTreeType
End Sub

Now if you close the Excel Workbook (save the code and the workbook first) and reopen the Workbook the Workbook_Open() will kick in and set the Worksheet. Now call your Form, and click the button. This will fill the two cells on the given sheet....

Global worksheet variable for userform in excel

1 Answers