asked on

ListBox Selections to TextBox / ListBox / ComboBox

Hi Experts,

I am trying to get the following form to populate a TextBox in Excel (see attached file for screen shot).

Basically I've made the list box 'Single' as I want th user to only be able to select one option at a time.

I need to make it so they hit Add Country and it poulates the List (or Text / Combo box tp the right)

Once the list has been made I then need to give the user the option to delete an entry.

Once the list has been successfully compiled I will be taking it to a cell in the workbook.

If someone can advise of the best way to do this or a reference point on the net (I'm sure it's been done before) that would be amazing!

Thanks in advance for any help!

Randy Poole

Hi, you did not post a screen shot, it would also be helpful if you attach the workbook.

martywal

ASKER

Sorry, I was pretty sure it had uploaded...
Here is the screen shot
Will try and get a section of the workbook out shortly
Cheers
form.png

martywal

ASKER

OK...

Hope this helps.
The attached is a section of the workbook with the form and the worksheet that the form depends on.

The idea is that when the user is selecting (click on) the "Serviced Countries" column (Cells F9:28) the form will open up.

They need to be able to select Multiple countries to be populated in the appropriate cell (as text "," delimited).

As you can see I've tossed around the idea of having a 'Multiple Locations' option but this is not enough I'm afraid.

:-)
List-Selector.xlsm

martywal

ASKER

Hi again Experts, just thought I'd see if the files I posted helped.
Pretty keen to try and get this worked out.
Sorry to be persistent.
Martywal

ASKER CERTIFIED SOLUTION

Randy Poole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

martywal

ASKER

That's getting there Randy thanks heaps!
Just getting run-time error 424 on the 'continue' when I need to take the selection back to the cell that was double clicked (object required).
Will play about with it and see where I end up.
Cheers

Randy Poole

Replace the function "CONTINUEserveCMD_Click" in the workbook with this one:

Private Sub CONTINUEserveCMD_Click()
    Dim st As String, c As Integer
    st = ""
    For c = 0 To SelCountryLst.ListCount - 1
        If Len(st) > 0 Then st = st & ","
        st = st & SelCountryLst.List(c)
    Next
    Dim r As Range
    Set r = Module1.selRange
    r.Value = st
    Me.Hide
End Sub

Open in new window

martywal

ASKER

Thanks Randy!
Yep that worked a treat!
One other very small issue I'm having is that sometimes when I hit 'Delete' it is taking not just the selected value but sometimes a couple and others ALL values.
Any thoughts there?
Thanks again
Martywal

martywal

ASKER

It only seems to delete the full list if the bottom (most recently selected) country is highlighted when they select delete

martywal

ASKER

Hi again,
Here's a screen shot that hopefully explains where I'm coming from.

In this case If I select Australia and hit 'Delete Country' it does the right thing...

If I hit Hong Kong (the bottom entry) it removes all countries.

The script currently looks like this:

Private Sub DELETEserveCMD_Click()
Dim c As Integer
MsgBox (c)
For c = SelCountryLST.ListCount - 1 To 0 Step -1
If SelCountryLST.Selected(c) Then
ServCountryLst.AddItem SelCountryLST.List(c)
SelCountryLST.RemoveItem (c)
End If
Next c
SortLB ServCountryLst
End Sub

If best for me to open a new question just let me know.

Randy Poole

Can you post the workbook and tell me the steps to reproduce this. Thanks!

martywal

ASKER

Hey Randy,
Just worked it out.
The list box (selcountrylst) needs to be set to extended 1...
Thanks for all your help!
Been great!
Cheers
Martywal

Randy Poole

NP :)

martywal

ASKER

Prompt and accurate response.
A pleasure as always!
Thanks Randy!