Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ListBox Selections to TextBox / ListBox / ComboBox

Posted on 2014-09-02
14
Medium Priority
?
205 Views
Last Modified: 2014-09-10
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!
0
Comment
Question by:martywal
  • 9
  • 5
14 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40298160
Hi, you did not post a screen shot, it would also be helpful if you attach the workbook.
0
 

Author Comment

by:martywal
ID: 40299779
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
0
 

Author Comment

by:martywal
ID: 40299811
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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:martywal
ID: 40302283
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
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 2000 total points
ID: 40303399
Give this a try. I think it should do what your expecting. I also changed the listbox style to multi extended so you can select and move more then one choice at a time.
List-Selector.xlsm
0
 

Author Comment

by:martywal
ID: 40304882
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
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40305648
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

0
 

Author Comment

by:martywal
ID: 40311202
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
0
 

Author Comment

by:martywal
ID: 40311245
It only seems to delete the full list if the bottom (most recently selected) country is highlighted when they select delete
0
 

Author Comment

by:martywal
ID: 40313539
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.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40314165
Can you post the workbook and tell me the steps to reproduce this. Thanks!
0
 

Author Comment

by:martywal
ID: 40314189
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
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40314197
NP :)
0
 

Author Closing Comment

by:martywal
ID: 40314200
Prompt and accurate response.
A pleasure as always!
Thanks Randy!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question