Store UserForm List Selection as Variable

[alyssa75]

What am I doing wrong?

Private Sub cmdOk_Click()

iMyVariable = ListBox1.Value

Unload Me
End Sub

 

[Norie]

What's the problem?

Are you trying to use iMyVariable after you've unloaded the form?

Where did you declare it?

 

[alyssa75]

Nothing is stored. When I step through the form code, after I made a selection, click ok and it goes past iMyVariable=ListBox1.Value, I hover over iMyVariable and it says NULL.

I have the variable declared as a String in the cmdOK_Click() code.


And yes, my intention is to use iMyVariable after Unload Me.


Any help is appreciated!! Thanks!!

 

[Norie]

Alyssa

The code you posted is the cmdOK_Click code but there's no variable declarations.

When you step through that code what's the value of Listbox1 before you try and assign it to the variable?

As for using it outside after the form is unloaded, where and how are you going to use it?

You could declare the variable as Public in a standard module and it should be available elsewhere.

 

[alyssa75]

Ok...so the first bit of code that calls the form is a Change Event.

NOTE: I changed iMyVariable to iLocation.

Private Sub Worksheet_Change(ByVal Target As range)

Dim iLocation As range

If Target.Column <> 21 Then Exit Sub
If Target.Value > 0 Then
If ActiveCell.Offset(0, -10).Value = "" Then
Load frmLocation
frmLocation.Show
ActiveCell.Offset(0, -10).Value = iLocation
End If
End If
End Sub

_________________________________________________________________________

Here's the entire Userform code:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOk_Click()
Dim iLocation As String
iLocation = ListBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
ListBox1.List = range("Locations").Value
End Sub

 

[Norie]

You've declared iLocation twice, once in the change event as a Range then in the OK_Click event as a string.

If all you want to do is put the value selected item from the listbox in a cell relative to the one that triggered the change event you don't need any variables.

Private Sub cmdOk_Click()
 
     ActiveCell.Offset(0, -10).Value = ListBox1.Value
 
     Unload Me 

End Sub

 

[alyssa75]

Makes sense. But cell is still not populating. What do I have wrong now? It shows me the form, allows me to select a value but then does not populate anything. And actually, when I test one where the value is not equal to "", it still initializes the form. Ugh.


Private Sub Worksheet_Change(ByVal Target As range)

If Target.Column <> 21 Then Exit Sub
If Target.Value > 0 Then
If ActiveCell.Offset(0, -10).Value = "" Then
Load frmLocation
frmLocation.Show
End If
End If
End Sub


FORM:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOk_Click()
ActiveCell.Offset(0, -10).Value = ListBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
ListBox1.List = range("Locations").Value
End Sub

 

[Norie]

That sounds like a problem with the change event.

Have you tried stepping through that code?

One thing I noticed about that code was that you only exclude one column.

So if a cell changes anywhere else on the worksheet the code will be triggered.

 

[alyssa75]

No other column seems to trigger it - so I think I'm fine there. How do you step through a change event?

 

[alyssa75]

I updated the change event code as follows and it now appears to function properly. The only, all be it major, flaw is that the value I select does not populate anywhere.

Private Sub Worksheet_Change(ByVal Target As range)

If Target.Column <> 21 Then Exit Sub
range(Target.Address).Activate
If ActiveCell.Offset(0, -10).Value = "" Then
Load frmLocation
frmLocation.Show
End If
End Sub