5 userforms with a single select listbox overwrite fixed cell

[Edd78]

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:

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!

 

[Edd78]

Nevermind, search long enough and you will find what you need.

Tinkered around with the code found on: http://www.mrexcel.com/forum/excel-...close-userform-after-data-selection-only.html

[COLOR=#333333]Private Sub lstitems_Click()[/COLOR]
MsgBox "Thanks " & lstitems.ValueRange("D3").Value = Me.lstitems.ValueUnload Me </pre>[COLOR=#333333]End Sub[/COLOR]