ListBox Control (2002/SP3)

Topic

New Reply

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”.

Reply | Quote

Replies

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″

Reply | Quote

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.ListBox.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

Reply | Quote

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.

Reply | Quote

Does this work:

For i = 0 To WS.OLEObjects(“SheetNameList”).ListCount – 1
‘…

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Perhaps someone else knows how to make this work.

Reply | Quote

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)

Public WithEvents aBox As msforms.ListBox

Private Sub aBox_Click()
    MsgBox "ListBox clicked"
End Sub

In a sheet’s code module

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

And the Public variable myBoxes is declared in a Normal Module

Public myBoxes as New Collection

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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 & D3:D500]. 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.

Reply | Quote

If I understand the problem, I would suggest that you just use a combobox from the Forms toolbar to have the user select a name and then set your chart based on this with formulas. it requires no coding and the combobox can even be put onto a chart sheet.

I often use this method to have a generic chart where the user selects the desired values for the X and Y axes. I have attached an example which adjusts the Y-axis. The example does have 2 pieces of code, one is used to print all the charts and the other to adjust the formatting and the min/max in the Y-Axes

Steve

Reply | Quote

Thanks alot Steve. Your example spreadsheet is a good starting point for me.

By the way, how do you get the chart title, X/Y axes, and the scales to change with each choice? ie, how is linked in the chart sheet?

Reply | Quote

If you click on the chart title or value (Y) axis title, you’ll see a formula in the formula bar. So these titles are updated automatically if the cells they refer to change. The category (X) axis title is fixed.

The combo box on the chart sheet has been assigned the macro ChartFormatY, so this macro is run each time a value is selected from the dropdown list. The macro set the properties of the value axis.

Reply | Quote

Great. Thanks again.

Reply | Quote