Control Box Problems

U

uftiffany

I am attempting to get the selected values in a list box to feed into another cell. Does anyone know how this is done?
 
V

Vaughan

The list box has a Linked Cell property. Set it to the cell you want the list box to feed.
 
D

Dave Peterson

It sounds like you're using the listbox from the control toolbox toolbar on a
worksheet.

If that's true, I set the multiselect property to: fmmultiselectmulti (to allow
more than one selection).

Then I put a commandbutton from the same control toolbox toolbar right next to
the listbox (click it when you're done selecting):

And put this code under the button:

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
Me.Cells(oRow, "B").Value = .List(iCtr)
End If
Next iCtr
End With

End Sub
 
U

uftiffany

At what part in the code do I tell it where I want the information to go? I am sorry but I am code-illiterate. Thank you so much for your help!
 
D

Dave Peterson

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
Me.Cells(oRow, "B").Value = .List(iCtr)
End If
Next iCtr
End With

End Sub

I put the values in B1 and worked down the list:
me.cells(orow,"B").value = .list(ictr)
was the line.

oRow starts at 0 (I forgot to initialize it and an unitialized Long variable
will start at 0) but gets a 1 added whenever an item was selected. The next
time it finds a match, that oRow gets another 1 added to it (so it would be 2)
and B2 would get the value.

Did you really want to support multiple selections? If no, then the linked cell
should've worked ok.

And if this didn't help, where did you want the first value to go?

If you say E97, then change this line:
Me.Cells(oRow, "B").Value = .List(iCtr)
to
Me.Cells(oRow, "E").Value = .List(iCtr)

Add this line:

oRow = 96
(since the first thing we do is add one to it)

right before this line:
With Me.ListBox1

=====
It should look something like:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim oRow As Long

oRow = 96
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
Me.Cells(oRow, "E").Value = .List(iCtr)
End If
Next iCtr
End With

End Sub
 
L

lu_meihua

hi, I wish to link the result in a separate worksheet. Where should
put the code then?? Eg. Cell A2 in worksheet "Sheet2".

In addition, instead of Row, can I link the results across column
instead?? How do I do that?

Sorry, but I am code-illiterate. Thanks.

==========================
Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim oRow As Long

oRow = 1
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
Me.Cells(oRow, "A").Value = .List(iCtr)
End If
Next iCtr
End With

End Su
 
D

Dave Peterson

maybe...

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim oRow As Long

oRow = 1
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
oRow = oRow + 1
worksheets("sheetxxxx").Cells(oRow, "A").Value = .List(iCtr)
End If
Next iCtr
End With

End Sub

Change that sheetxxxx to the correct name.
 
L

lu_meihua

Hi Dave! thanks! it works!!!!!! thanks thanks thanks!
a separate question, i need to create a command button (from control
toolbar) to clear some contents in a separate worksheet. however I keep
getting run time error. am i missing something? the debug message
always highlight at the range. Please help. Many thanks.

===================
Private Sub CommandButton2_Click()
Sheets("Sheet2").Select
Range("G1:IV1").Select
Selection.ClearContents
Sheets("Sheet3").Select
Range("C7").Select
End Sub
===================
 
D

Dave Peterson

You have Range("g1:iv1") behind a worksheet module. Unqualified ranges in these
modules refer to the worksheet that owns the code. So you're trying to select
G1:IV1 on the "wrong" worksheet. And you can't select ranges on a worksheet
that isn't active.

You could do:

Private Sub CommandButton2_Click()
with sheets("sheet2")
.Select
.range("G1:IV1").Select
Selection.ClearContents
end with
'sheets("sheet3" is the sheet that owns the code??
'if yes,
me.select
'if no
Sheets("Sheet3").Select
'if sheet3 isn't the sheet that owns the code, then
'fully qualify it
sheets("sheet3").Range("C7").Select
End Sub

But even better is to just clear without all that selecting:

Private Sub CommandButton2_Click()
Sheets("Sheet2").Range("G1:IV1").ClearContents
End Sub
 
Top