Store UserForm List Selection as Variable

[alyssa75]

What am I doing wrong?

Private Sub cmdOk_Click()

iMyVariable = ListBox1.Value

Unload Me
End Sub

 

[Norie]

Oops, my bad.

Always get a bit confused when <> is used instead of just =.

Another thing I noticed is that you use ActiveCell instead of Target.

They are probably interchangeable though.

 

[alyssa75]

On the user form code....does the cell to be edited with the value selected have to be Active before it can be changed?? Do I need to go back to the variable thing?

 

[Norie]

Try this:

Module1:

Option Explicit
 
Public MyLocation As Range

Worksheet module:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 21 Then Exit Sub
    
    If Target.Value > 0 Then
    
        If Target.Offset(0, -10).Value = "" Then
        
            Application.EnableEvents = False
            
            Set MyLocation = Target.Offset(0, -10)
            
            frmLocation.Show
        End If
        
    End If
    Application.EnableEvents = True
End Sub

Userform module:

Option Explicit
 
Private Sub cmdOk_Click()
    If ListBox1.ListIndex <> -1 Then
        MyLocation.Value = ListBox1.Value
        Unload Me
    Else
        MsgBox "Please select a location"
    End If
End Sub
 
Private Sub UserForm_Initialize()
   
   ' your code to populate listboxox
 
End Sub

 

[alyssa75]

Thanks much for that! Unfortunately, for some reason it is still not pulling the value selected from the list. That is, when I step through and then hover to see the value...ListBox1.Value is equal to Null. It is because I haven't set it up to only allow one selection, which I still need to do but haven't tried to figure out how yet?

 

[alyssa75]

So the problem was the MultiSelect Property. I changed that to single select and got rid of the OK and Cancel buttons. The form code is now as follows as works perfectly. Thanks for all your help!

Private Sub ListBox1_Click()
If ListBox1.ListIndex <> -1 Then
MyLocation.Value = ListBox1.Value
Unload Me
Else
MsgBox "Please select a location"
End If

End Sub

 

[Norie]

No problem, should have thought of multivalue I suppose.

Nice idea to just use the Listbox Click event.

 

[alyssa75]

Hey - wondering if I could pick your brain on one more thing. I want to follow with another listbox. The Form and Public declar are the same (with Currency instead of Location) and I just added on another IF as follows - problem is that the second list box does not display...it just automatically populates the value that is in the same place in the list as the Location chosen. i.e. if I choose the 4th location listed it populates that correctly but then also just goes ahead and populates (in the correct cell) what would have been the 4th currency listed. I am at a complete loss.

Private Sub Worksheet_Change(ByVal Target As range)
If Target.Column <> 21 Then Exit Sub

If Target.Value > 0 Then

Set MyLocation = Target.Offset(0, -10)
Set MyCurrency = Target.Offset(0, 1)

If Target.Offset(0, -10).Value = "" Then
frmLocation.Show
End If

If Target.Offset(0, 1).Value = "" Then
frmCurrency.Show
End If

End If
End Sub


The New Form for CUrrency is as Follows

Option Explicit



Private Sub ListBox2_Click()
If ListBox2.ListIndex <> -1 Then
MyCurrency.Value = ListBox2.Value
Unload Me
Else
MsgBox "Please select a Currency"
End If

End Sub

Private Sub UserForm_Initialize()
ListBox2.List = range("CurrencyList").Value
End Sub

 

[Norie]

Do you want the user to select both the currency and location?

Or is it going to be one or the other sometimes?

 

[alyssa75]

It could be both, one or none. if the fields have not already been entered manually (before the cell that triggers the event), then no list(s).