5 userforms with a single select listbox overwrite fixed cell

Edd78

Board Regular
Joined
Nov 22, 2013
Messages
57
Hello again y'all,

Yesterday I came to you with a question to help me setup a listbox with 5 categories that would trigger a userform.

http://www.mrexcel.com/forum/excel-...serform-not-userform-listbox.html#post3970853

Big thanks again to Andrew for his help on that!!

However, not being a VBA expert I'm a bit stuck again as I wish to gather the data from these 5 userforms in a single cell.
I'll try to explain, user clicks on a catagory in the list and one of 5 userforms pops up. User can than select 1 item in this list and than the popup should close with selection made. If the user changes his mind he can click on another catergory and open another of these 5 userforms to change his selection. This should than replace the value from the previous selection.

What I'm trying to figure out are two things,
1. how do I get the userform to close after clicking on an item in the listbox?
2. how do I get the selections made from the userforms to be fixed to a specific cell and be replaced when another selection is made?

I have this code, but it just spreads the selections over multiple cells:

Code:
Private Sub Select_Button_Click()Dim rRange As Range
Dim lCount As Long   'Counter

Set rRange = Range("$AC$2")

With ListBox1
   For lCount = 0 To .ListCount - 1
      If .Selected(lCount) = True Then
         rRange.Offset(lCount, 0).Value = .List(lCount)
      End If
   Next
End With

End Sub

I have a command button on the userform at the moment to test the code to place the selection on the worksheet. This obviously needs to be removed if I can figure out how to make the selection write to fixed cell and auto close the userform.

Many thanks for your time and assistance once again!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,130,426
Messages
5,642,055
Members
417,252
Latest member
selbysam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top