Get multiselected from combobox

  • Thread starter Thread starter Bhuktar S
  • Start date Start date
B

Bhuktar S

From the list of items in a combobox, user selects certain items (one o
more). How to get these on worksheet ? Say, selected are Item1,Item3
Item4, Item7. After the user clicks, say, a GO button, these to b
listed sequencely, say, B1=Item1, B2=Item3, B3=Item4, B4=Item7.
Further, the user to be allowed to select only limited no. of items
say, cannot select more than 6 items. If the 7th item is tried, i
cannot be highlighted or a message to display, say, "Max. 6 can b
selected.
 
I guessed that this was on a userform.

I put a listbox (listbox1) a label (label1), and 2 commandbuttons (Go and
Cancel, named commandbutton1 and commandbutton2) on a userform.

This is the code that I had behind the form:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim oRow As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
Worksheets("sheet1").Cells(oRow, "B").Value = .List(iCtr)
End If
Next iCtr
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_change()

Dim SelectedCount As Long
Dim iCtr As Long
Dim maxSelected As Long
Dim oRow As Long

maxSelected = 6

SelectedCount = 0
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
SelectedCount = SelectedCount + 1
End If
Next iCtr
End With

If SelectedCount > maxSelected Then
Me.Label1.Caption = "No more than " & maxSelected & " entries."
With Me.CommandButton1
.Enabled = False
End With
ElseIf SelectedCount = 0 Then
Me.Label1.Caption = "Please Make a Selection"
Me.CommandButton1.Enabled = False
Else
Me.Label1.Caption = SelectedCount & " chosen so far"
Me.CommandButton1.Enabled = True
End If

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 10
.AddItem "asdf" & iCtr
Next iCtr
End With

With Me.CommandButton1
.Caption = "Go!"
.Enabled = False
End With

Me.CommandButton2.Caption = "Cancel"

Me.Label1.Caption = "Please Make a Selection"

End Sub
 
Thanks Dave.
With this code, where do I indicate the cells in which I want th
output? (I mean, how do I know the output will be available in cells
say, B1 to B6 ?)

Secondly, which I had not clarified & sorry, the combobox is to be o
sheet & not on userform. So, please advise accordingly.

Thanks for your kindness.
Regards
 
This line:
Worksheets("sheet1").Cells(oRow, "B").Value = .List(iCtr)

says to put it in column B of sheet1. oRow is set right before it.

But that code won't work on a worksheet.

Before I suggest anything more, is it a combobox (probably no) and if it is a
listbox, is it from the Forms toolbar or from the Control toolbox toolbar.

The code would be different.
 
Thanks Dave,
The Combobox is on worksheet & made using Control Toolbox.

The code, for me as non-professional, looks quite complex. Is ther
some other way? or what readings you suggest me to increase m
knowledge?

Thanks for your help !
Regards
 
Are you sure it's a combobox (not a listbox????) and are you sure it's from the
control toolbox?

You may want to think about making a small userform that does it for you. (You
have the bulk of the code already <bg>.)


For excel books, Debra Dalgleish has a big ole list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.
 
Thanks for the suggestions of books.
Yes, it is the combobox on worksheet from control toolbox.
I have a list of items of a group in the worksheet but located in
range outof scoll area.
The items are such that for a particular product, possibility of an
selection but limited to max. 6.
Apart from this, there are other types of item selection but from othe
group (for which I am not concerned now).
So, on the worksheet, I made a combobox from the control toolbox & ha
the list range (from the one which I said before as out of the scrol
area).
I have reseved 6 cells to get the output of the selections from thi
combobox.
Yes, I can use userform but evrything being just possible by doing o
the sheet, I would prefer this also as part of sheet.
If no choice, then ofcourse I will have to adopt the userform method.
What changes are necessary in the code to apply to the combobox of th
sheet
 
It's a combobox that gets one value at a time and then populates a cell with
that value.

Then you go back to the combobox and change it to select the 2nd value, then
plop that into the worksheet?

Then the same for the 3 through 6th?

If yes, then I put a combobox from the control toolbar on the worksheet and had
this code behind it.

Option Explicit
Private Sub ComboBox1_Change()

Dim myRngToFill As Range
Dim iCtr As Long

With Worksheets("sheet1")
Set myRngToFill = .Range("b1:b6")
End With

If Application.CountA(myRngToFill) = myRngToFill.Cells.Count Then
MsgBox "Range is filled!"
Else
If Application.CountIf(myRngToFill, Me.ComboBox1.Value) Then
MsgBox "Already exists"
Else
For iCtr = 1 To myRngToFill.Cells.Count
If myRngToFill(iCtr).Value = "" Then
myRngToFill(iCtr).Value = Me.ComboBox1.Value
Exit For
End If
Next iCtr
End If
End If

End Sub
 
Dear Dave,
Your code, as under, is working fine.
I have made userform, added listbox1, commandbutton1 (as Go), set th
property of listbox1 as selection exteded, modal false for the form
copied the code for command button1.
The problem is: the list gets added always from B1 downwrds.
What shall I change in the code, if I select a particular cell instea
of default B1? (For this I made modal as False, so that I can select
cell).

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim oRow As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
Worksheets("sheet1").Cells(oRow, "B").Value = .List(iCtr)
End If
Next iCtr
End With
End Su
 
These lines:

oRow = oRow + 1
Worksheets("sheet1").Cells(oRow, "B").Value = .List(iCtr)

Help define where the output should go.

Since I left oRow unitialized, it starts = 0.

oRow + 1 means that before it does anything, it's set to 1 (and 1 is added each
time).

The second line of this pair:
Worksheets("sheet1").Cells(oRow, "B").Value = .List(iCtr)

Says to go to sheet1 column B and look in whatever row oRow is to populate the
worksheet.

You want column C of sheet13 starting with row 99???

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim oRow As Long
orow = 98 'one less than the starting row.
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
Worksheets("sheet13").Cells(oRow, "C").Value = .List(iCtr)
End If
Next iCtr
End With
End Sub
 
Dear Dave,
I am sorry if I am stretching this forum long.
May be I have not put it clearly.
Once the userform is displayed, the user may select a cell where h
wants the list to be started from. So the sequence of actions are:
-Upon click of a command button ("Show List") on the worksheet, th
userform opens,
-User selects a cell on the worksheet (as it is possible since modal i
off), or we can say, the output from the list starts from the activ
cell,
-User selects the items from the list
-User is limited to select max. 6 items (7th item CANNOT be selected.
(This I forgot to indicate to you earlier)
-User clicks a Go (command button) on the form
-The output is now abvailable listed from the active cell downwards (i
anything is existing, it can overwrite)
-The 'Close' button on the userform will allow the finish th
operations (userform will be unloaded)
-If the user wants to change a particular cell, the user repeats th
actions by opening the form.
Hope, I haven't made you work hard
 
Maybe....

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim oRow As Long
Dim oCol As Long
oRow = ActiveCell.Row - 1
oCol = ActiveCell.Column
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
ActiveSheet.Cells(oRow, oCol).Value = .List(iCtr)
End If
Next iCtr
End With
End Sub
 
Back
Top