PDA

View Full Version : Sharing a Variable



pcsparky
01-18-2009, 06:08 AM
I've created a Userform and I'd like to use the variable startRow from the first Sub in the next sub (both are in the same form code box) but can't work out how despite trawling the net for ages. I just don't understand VBA very well at the moment as I'm just learning.

I get the Runtime error 1014 at endCell = Cells(startRow, 4).End(xlDown).Row as there is no value.

Any help please.

Sub options()
Dim startRow As Integer

If TrainingType.OptionButton1.Value = True Then
startRow = 8
handlerType = "GPD Handlers"
End If
End Sub
__________________________________________________

Public Sub UserForm_Activate()
Dim handlerName As String
Dim counter As Integer
Dim endCell As Integer
Dim startRow As Integer


ListBox1.Clear

Sheets("Dog and Handler Data").Activate
endCell = Cells(startRow, 4).End(xlDown).Row

For counter = startRow To endCell

handlerName = Cells(startRow, 4)
ListBox1.AddItem (handlerName)
startRow = startRow + 1
Next counter
End Sub

Bob Phillips
01-18-2009, 06:33 AM
There are a n umber of ways, but I am confused by your terminology.

You say that ... both are in the same form code box. What does that mean? I first read it as both being in the form code module, but the Userform_Activate event is called when the for is shown, so if Options is somehow called from within the form, Userform_Activate will not get called again.

pcsparky
01-18-2009, 06:44 AM
Yes, they're in the same module, couldn't remember the correct term.

I think I understand. The Userform_Activate is running first. Is there another way to do this.

What I am trying to do is get the variable startRow from the users selection of radio buttons from the previous form (TrainingType) which eventually opens a file.

I need for the user to be able to go back to this Userform though to select another option (in order to open another file). That's why I thought of using Userform_Activate.

Bob Phillips
01-18-2009, 07:22 AM
The simplest way (although not the way I would do it), is to create a Public variable in a standard code module, and in the first form write to that variable, in the second read from it.

That code also seems wrong, you have a counter but don't use it. Also, if StartRow is public, you have to be careful how you modify it



For counter = startRow To endCell

handlerName = Cells(counter, 4)
ListBox1.AddItem (handlerName)
Next counter

pcsparky
01-18-2009, 07:43 AM
OK thanks. Sorry I accidently edited out startRow = startRow + 1 in the loop that I'm using.