Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ListBox Control (2002/SP3)

    I need help with a VBA code that I'm working on. I'm trying to create a ListBox control in a worksheet with the following code:

    Sub CreateListBoxControl()
    Dim objLB As OLEObject ' OLE Object
    Dim WS As Worksheet

    Set WS = ActiveSheet
    Set objLB = WS.OLEObjects.Add(Classtype:="Forms.ListBox.1", _
    Left:=Cells(1, 3).Left, Top:=Cells(1, 3).Top + 12, _
    Width:=180, Height:=41.25).OLEObject
    With objLB
    .ListFillRange = Range("B1:B3")
    End With
    End Sub

    The error message that I get says that "object doesn't support this property".

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

    Re: ListBox Control (2002/SP3)

    1) Remove .OLEOBject from the end of the instruction that creates the list box. The result of OLEObjects.Add is already an OLEObject.

    2) Set the ListFillRange property to a string, not to a range:

    .ListFillRange ="B1:B3"

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    As always, thank you Hans.

    For this part of the code I get an error message saying "Object variable or with Block variable not set". The code is:

    Set objLB = ActiveSheet.OLEObjects.Add(Classtype:="Forms.ListB ox.1", _
    Left:=Cells(1, 3).Left + 12, Top:=Cells(1, 3).Top + 12, _
    Width:=90, Height:=41.25)
    With objLB
    .Activate
    End With

    ' Define the range where the data is stored
    Set rng = Sheets("DataSheet").Range("B2:F2")

    ' Re-select the stored selections
    For i = 0 To WS.OLEObjects.SheetNameList.ListCount - 1
    WS.OLEObjects.SheetNameList.Selected(i) = rng.Cells(i + 1, 1)
    Next i

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

    Re: ListBox Control (2002/SP3)

    Why do you activate objLB?
    What is WS?
    What is SheetNameList?
    rng is a range of cells in row 2, but your code refers to cells in different rows.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    Does this work:

    For i = 0 To WS.OLEObjects("SheetNameList").ListCount - 1
    '...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    Thanks Jan and Hans. Sorry for the confusion in the code. Let me try to give an example and an explanation of what I'm trying to do.

    I've attached an example spreadsheet with the code. Here's what I'm trying to achieve with the code:
    1) In the Listbox control on "ControlSheet" I want to list the titles in "DataSheet" in the range of B2 to F2
    2) I want the Listbox to have a MultiSelect 1 (MultiSelectMulti) property so that the User will be able to select multiple titles from the list
    3) I then want to plot the data that was selected by the User (not coded yet)
    Attached Files Attached Files

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

    Re: ListBox Control (2002/SP3)

    Your code makes no sense. You cannot add list items by using the Selected property. The latter is used AFTER the items have been added to indicate which items are selected in a multi-select list box.

    I can't seem to add items and set the multiselect property from a general macro; I can do it in code behind the worksheet though, for example in the On click event procedure of a command button:

    Private Sub CommandButton1_Click()
    Dim i As Integer
    With Me.SheetNameList
    .Activate
    .MultiSelect = fmMultiSelectMulti
    For i = 1 To Worksheets("DataSheet").Range("A1").CurrentRegion. Columns.Count - 1
    .AddItem Worksheets("DataSheet").Cells(2, i + 1)
    Next i
    .Activate
    End With
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    Hans,

    I created a command button and copied the code to a module. When I run the code I get the following error: "Invalid use of the Me keyword".

    Why is that?

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

    Re: ListBox Control (2002/SP3)

    As I wrote in my previous reply, the code should be in the worksheet module (for the Control Sheet in this case).

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    But how is that going to work when I'm going to create a new worksheet with a similar Listbox? That is, how can I generalize it so it becomes independent of a specific worksheet?

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

    Re: ListBox Control (2002/SP3)

    Perhaps someone else knows how to make this work.

  12. #12
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    I can't text ActiveX controls on my Mac at home.
    Once the code for one listbox had been vetted, one could be moved to a class module.
    Each sheet's Activate event could cause the appropriate controls to look to that class for their event code.


    In a code module (named clsAllBoxes)<pre>Public WithEvents aBox As msforms.ListBox

    Private Sub aBox_Click()
    MsgBox "ListBox clicked"
    End Sub</pre>

    In a sheet's code module<pre>Private Sub Worksheet_Activate()
    Dim xBox As clsAllBoxes

    Set xBox = New clsAllBoxes
    Set xBox.aBox = ListBox1
    On Error Resume Next
    myBoxes.Add Item:=xBox, key:=Me.Name & xBox.Name
    On Error GoTo 0

    Set xBox = New clsAllBoxes
    Set xBox.aBox = ListBox2
    On Error Resume Next
    myBoxes.Add Item:=xBox, key:=Me.Name & xBox.Name
    On Error GoTo 0

    End Sub</pre>

    And the Public variable myBoxes is declared in a Normal Module
    <pre>Public myBoxes as New Collection</pre>


  13. #13
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    Thanks for all you help Mike and Hans. Mike, your code is very confusing to me and I don't understand how it works.

    Let me ask another question. My final objective is to allow the user to select one or more named ranges (all containing numbers) which can tell call then be used to plot on an XY type chart. Is it easier to program (via VBA) this using a listbox form rather than a listbox control? Or, is there a better way to do so using any other control, such as a checkbox contol?

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

    Re: ListBox Control (2002/SP3)

    One way to let a user select a range is

    Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Please select a range", Type:=8)

    Or you can use a RefEdit control on a userform. See for example How to Use the RefEdit Control with a UserForm

  15. #15
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Control (2002/SP3)

    Not exactly what I'm looking for since the range of data could be for instance [A3:A1000 & C3:C1000] or [A3:A500 & C3:C500 & D3500]. That's why I'd prefer to set it up as a named range (or titles), and allow the user to select from at least one of the listed names which will then be used to plot the data. I guess it's not an simple problem to deal with.

    Thanks again for taking the time to look at this issue. I appriciate all the help.

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
  •