ExcelBanter - List Box Head-Scratcher

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - List Box Head-Scratcher (https://www.excelbanter.com/excel-programming/396002-list-box-head-scratcher.html)

Zone[_3_]

List Box Head-Scratcher

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

List Box Head-Scratcher

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.

"Zone" wrote:

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

Vergel Adriano

List Box Head-Scratcher

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

"Zone" wrote:

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

Vergel Adriano

List Box Head-Scratcher

JLGWhiz

List Box Head-Scratcher

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" wrote:

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[_3_]

List Box Head-Scratcher

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

"Michael" wrote in message
...
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.

"Zone" wrote:

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[_3_]

List Box Head-Scratcher

Vergel, that works like a charm. Thanks! James

"Vergel Adriano" wrote in message
...
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

"Zone" wrote:

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[_3_]

List Box Head-Scratcher

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

"JLGWhiz" wrote in message
...
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" wrote:

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

All times are GMT +1. The time now is 04:39 PM.