List Box Head-Scratcher

[Zone]

I want this userform to put the value of the listbox in cell A1 as soon as
the user makes a selection. Then I want the form to close. I finally
figured out it was the Unload Me that was causing the code to choke, so I
put in a Close button to unload the darn thing. But I don't like that. How
can I make this work? TIA, James

Private Sub ListBox1_Change()
[a1] = Me.ListBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub

 

[Michael]

If all you need to do is place the value on A1 go to the listbox properties
and on the tab Categorized, select the data category and Place A1 where the
RowSource property is, this should place whatever the user selects on A1.

 

[Vergel Adriano]

maybe like this:

Private bFormIsLoading As Boolean
Private Sub ListBox1_Change()
[a1] = Me.ListBox1
If Not bFormIsLoading Then
Unload Me
Else
bFormIsLoading = False
End If
End Sub

Private Sub UserForm_Initialize()
bFormIsLoading = True
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub

 

[Vergel Adriano]

maybe like this:

Private bFormIsLoading As Boolean
Private Sub ListBox1_Change()
[a1] = Me.ListBox1
If Not bFormIsLoading Then
Unload Me
Else
bFormIsLoading = False
End If
End Sub

Private Sub UserForm_Initialize()
bFormIsLoading = True
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub

 

[JLGWhiz]

James, I usually use the ListBox1_Click method to do what you are trying with
the ListBox1_Change and have not had any problems with the UserForm
unloading. Maybe I missed something?

 

[Zone]

Michael, thannks for your reply. However, that puts the value of A1 in the
listbox. It does not put the value of the listbox in A1. James

If all you need to do is place the value on A1 go to the listbox
properties
and on the tab Categorized, select the data category and Place A1 where
the
RowSource property is, this should place whatever the user selects on A1.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

I want this userform to put the value of the listbox in cell A1 as soon
as
the user makes a selection. Then I want the form to close. I finally
figured out it was the Unload Me that was causing the code to choke, so I
put in a Close button to unload the darn thing. But I don't like that.
How
can I make this work? TIA, James

Private Sub ListBox1_Change()
[a1] = Me.ListBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub

 

[Zone]

Vergel, that works like a charm. Thanks! James

maybe like this:

Private bFormIsLoading As Boolean
Private Sub ListBox1_Change()
[a1] = Me.ListBox1
If Not bFormIsLoading Then
Unload Me
Else
bFormIsLoading = False
End If
End Sub

Private Sub UserForm_Initialize()
bFormIsLoading = True
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub


--
Hope that helps.

Vergel Adriano

I want this userform to put the value of the listbox in cell A1 as soon
as
the user makes a selection. Then I want the form to close. I finally
figured out it was the Unload Me that was causing the code to choke, so I
put in a Close button to unload the darn thing. But I don't like that.
How
can I make this work? TIA, James

Private Sub ListBox1_Change()
[a1] = Me.ListBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub

 

[Zone]

Whiz, If I change ListBox1_Change to ListBox1_Click, it still crashes, so
I'm going to go with Vergel's solution. Thanks for replying, though.
James

James, I usually use the ListBox1_Click method to do what you are trying
with
the ListBox1_Change and have not had any problems with the UserForm
unloading. Maybe I missed something?

I want this userform to put the value of the listbox in cell A1 as soon
as
the user makes a selection. Then I want the form to close. I finally
figured out it was the Unload Me that was causing the code to choke, so I
put in a Close button to unload the darn thing. But I don't like that.
How
can I make this work? TIA, James

Private Sub ListBox1_Change()
[a1] = Me.ListBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Lions"
Me.ListBox1.AddItem "Tigers"
Me.ListBox1.AddItem "Bears"
Me.ListBox1 = "Bears"
End Sub