Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box Visible (Excel 2003/VBA)

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

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    Thanks Hans,

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

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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,

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  11. #11
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    Should the company ALWAYS go into B6?

  13. #13
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

  15. #15
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Visible (Excel 2003/VBA)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •