List Box Visible (Excel 2003/VBA)

Topic

New Reply

Is it possible to make a listbox on a worksheet invisible until a button is clicked.

Thanks

Reply | Quote

Replies

If you created the list box from the Forms toolbar:
– Right-click the list box to select it.
– Switch to the Visual Basic Editor (Alt+F11)
– Activate the Immediate window (Ctrl+G)
– Type ? Selection.Name and press Enter.
– You’ll see the name of the list box, probably something like List Box 1.
– Type Selection.Visible = False
– Use a macro like this to make the list box visible (substitute the correct name):

Sub ShowListBox
ActiveSheet.Shapes(“List Box 1”).Visible = True
End Sub

– Assign this macro to a command button.

If you created the list box from the Control Toolbox:
– Turn on design mode (first button on the Control Toolbox)
– Select the list box.
– Switch to the Visual Basic Editor (Alt+F11)
– The Properties window will show the name of the list box. It probably looks like ListBox1.
– Activate the Immediate window (Ctrl+G)
– Type ActiveSheet.ListBox1.Visible = False (substitute the correct name) and press Enter.
– Use the following code for the command button in the worksheet module (again, substitute the correct names):

Private Sub CommandButton1_Click()
Me.ListBox1.Visible = True
End If

Reply | Quote

I tried this code, but now I want to double click one of the items to fill a cell value.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 0, 135, 230, 75)
lb.ControlFormat.ListFillRange = “[req.xls]Sheet2!$A$2:[req.xls]Sheet2!$A$2.End(xlDown)”
End With

Reply | Quote

A list box from the Forms toolbar (that is what you are creating using AddFormControl) doesn’t have a double click event. You can only assign a macro to it that will be executed if the user clicks on the list box.

If you really need the double click event, you must use a list box from the Control Toolbox.

Reply | Quote

Thanks Hans,

Now, how do I enter the value that I clicked on from the textbox into cell A1.

Thanks

Reply | Quote

If you are still using a list box from the Forms toolbar, set its LinkedCell to A1:

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 0, 135, 230, 75)
lb.ControlFormat.ListFillRange = “[req.xls]Sheet2!$A$2:[req.xls]Sheet2!$A$2.End(xlDown)”
lb.LinkedCell = “A1”
End With

Reply | Quote

Hans, I should have been more clear.

I created the list box from the control toolbox as you stated in a previous message.

I then created button to show the list box.
Private Sub ApprovedSupplier_Click()

With Worksheets(1)
ListBox1.ListFillRange = “[req.xls]Sheet2!$A$2:$A$16”
Me.ListBox1.Visible = True
End With

End Sub

I then added the on click event.

Private Sub ListBox1_Click()

Here I would like to click on a company name from the list and have mailing information (stored in a different workbook) go into cells B8, B9, etc.
I guess I need the location or cell of the company that I clicked on to that I can fill the information in with offsets??

Me.ListBox1.Visible = False

End Sub

Thanks,

Reply | Quote

Thanks for the explanation.

I don’t think you should use the On Click event of the list box for this. If the user accidentally clicks the same item twice, you’d end up with duplicate entries unless you write complicated code to avoid that. Moreover, the list box would become invisible after one click.
Instead, I would set the MultiSelect property of the list box to fmMultiSelectMulti (each click selects or deselects an item) or to fmMultiSelectExtended (Windows Explorer-style multiple selection using the Shift and Ctrl keys), so that the user can select multiple items, and use another command button to process the selection.

Private Sub cmdProcess_Click()
Dim i As Long
Dim j As Long
j = Range(“B65536”).End(xlUp).Row
For i = 0 To Me.ListBox1.ListCount – 1
If Me.ListBox1.Selected(i) Then
j = j + 1
Range(“B” & j) = Me.ListBox1.List(i)
Me.ListBox1.Selected(i) = False
End If
Next i
Me.ListBox1.Visible = False
End Sub

Reply | Quote

Hans,

They do not want to multiselect. They would like to choose one thing, have it enter information and then become invisible. If they want to enter another client they can click the command button.

So who can the click get information from the alternate excel workbook, like cell location.

Deni

Reply | Quote

Private Sub ListBox1_Click()
Dim j As Long
j = Range(“B65536”).End(xlUp).Row + 1
If Me.ListBox1.ListIndex = -1 Then
MsgBox “Please select an item”
Exit Sub
End If
Range(“B” & j) = Me.ListBox1.List(Me.ListBox1.ListIndex)
Me.ListBox1.Visible = False
End Sub

Reply | Quote

Sorry Hans,

I can’t under what range to define. The data is stored in another workbook called req.xls.

So instead of this
j = Range(“B65536”).End(xlUp).Row + 1

I’m trying this but it doesn’t work.
j = Range(“[req.xls]Sheet2!$A$2)”, “[req.xls]Sheet2!$A$16)”).Row

So when company A gets clicked, that company information goes into cell B6 of the active sheet.

Thanks so much for the help.

Deni

Reply | Quote

Should the company ALWAYS go into B6?

Reply | Quote

Yes, they can only choose one company from the list.

Reply | Quote

Set the ListFillRange of the list box to the range in req.xls (see the code higher up in this thread).

Change the code to:

Private Sub ListBox1_Click()
If Me.ListBox1.ListIndex = -1 Then
MsgBox “Please select an item”
Exit Sub
End If
Range(“B6”) = Me.ListBox1.List(Me.ListBox1.ListIndex)
Me.ListBox1.Visible = False
End Sub

Reply | Quote

Hans, Thanks for the help

Another question

So the person has picked a company from the list and it entered in the right space.

Below that I would also like to enter the companies mailing address,etc, that’s stored in the other workbook.

I was trying something below but I can’t get it to work.

‘set where to look
Set code = ActiveSheet.Range(“A2″)
‘”[req.xls]Sheet2!$A$2:$A$16”
Set codeRanges = ActiveSheet.Range(code, code.End(xlDown))

‘initialize counter
counter = 1

‘look through the company names
For Each code In codeRanges
counter = counter + 1
If CompanyName = code.Value Then
‘Workbooks(Filename).ActiveChart
Workbooks(“copy of requisition.xls”).Range(“B9”).Value = Workbooks(“req.xls”).Range(counter, counter).Value
End If
Next

Thanks

Reply | Quote

Are the company data stored in a table format, i.e. all data for a company in neighboring cells in a row?

Reply | Quote

Yes

Reply | Quote

Set the ListFillRange of the list box to the complete table, instead of just the first column.
Set the ColumnCount to the correct number of columns.
Set the ColumnWidths property to (for example) 144;0;0;0. This is for 4 columns, the first is 144 points (=2 inches) wide, the others are hidden because their width is 0.
On Click code:

Private Sub ListBox1_Click()
Dim i As Integer
If Me.ListBox1.ListIndex = -1 Then
MsgBox “Please select an item.”, vbExclamation
Exit Sub
End If
For i = 0 To Me.ListBox1.ColumnCount – 1
Cells(6, i + 2) = Me.ListBox1.Column(i, Me.ListBox1.ListIndex)
Next i
Me.ListBox1.Visible = False
End Sub

Reply | Quote

Hans you rock, thanks for the help

Reply | Quote