VBA has errors with Form Control Listbox

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi,

I tried out a workbook that was created in Excel 2007 on a laptop with Excel 2010. On the Excel 2010 machine, the ActiveX controls, two CommandButtons and a multicolumn Listbox no longer worked correctly.

Since I need this to work on both platforms, I decided to change from ActiveX controls to Form controls.

The CommandButtons worked just fine; just had to change from CommandButton to Button and that was it.

The Forms ListBox is still called a ListBox, but I receive an error when the code is run. The ActiveX and Forms control code are shown below.

ActiveX:
Code:
Private Sub CommandButton2_Click()
Dim lbtarget As msforms.ListBox
    Dim rngSource As Range

    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Chart").Range("O2:P50")
       
    'Fill the listbox
    Set lbtarget = ListBox1
    
    With lbtarget
        'Determine number of columns
        .ColumnCount = 2
        'Set column widths
        .ColumnWidths = "40;20"
        'Insert the range of data supplied
        .List = rngSource.Cells.Value
    End With

Forms:
Code:
Private Sub Button2_Click()

Dim lbtarget As msforms.ListBox
    Dim rngSource As Range

    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Chart").Range("O2:P50")
      
    'Fill the listbox
    Set lbtarget = ListBox88
    
    With lbtarget
        'Determine number of columns
        .ColumnCount = 2
        'Set column widths
        .ColumnWidths = "40;20"
        'Insert the range of data supplied
        .List = rngSource.Cells.Value
    End With

End Sub

The code is virtually identical however, I get an error that "variable not declared and the debugger points to:
Code:
Set lbtarget = ListBox88

Does anyone in the Forum have an idea make this work?

Thanks,

Art
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi,

I tried out a workbook that was created in Excel 2007 on a laptop with Excel 2010. On the Excel 2010 machine, the ActiveX controls, two CommandButtons and a multicolumn Listbox no longer worked correctly.
Thanks,

Art
Possibly that laptop has not gotten recent updates to Office 2010 and is suffering from an active-x controls problem Microsoft inflicted with a December, 2014 update. Might be easier to fix the laptop than write new code for form controls. See this link for more information:
https://social.technet.microsoft.co...rking-after-december-2014-updates?forum=excel
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Joe,

Thanks for your response and suggestions. Unfortunately have had a long ride around the block on the ActiveX problems. After that MS update, I had a very large and complex workbook (not this one) break due to the update. I tried all the suggestions on the webpage you cited; the fix was only temporary. The ActiveX issues always returned.

I worked with my developer to try to resolve this. He is an Excel MVP and and as such, has an inside track with MS support. They really didn't tell him much about the security issue that they addressed and in the end, we had to redo the whole workbook replacing the ActiveX controls with form controls. It was painful.

Guess my memory is short (it's good but short :) )because I put ActiveX controls in the workbook that I created the other day. So the bottom line is that I need to fix the code to work with the Forms Listbox.

Any suggestions on how to do this?

Thanks,

Art
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
I think you will only get one column in a "Forms" Listbox and you need to fill it like this:-
Code:
ActiveSheet.Shapes("List Box 2").ControlFormat.RemoveAllItems
ActiveSheet.Shapes("List Box 2").ControlFormat.ListFillRange = "A1:A17"
MsgBox ActiveSheet.Shapes("List Box 2").ControlFormat.ListFillRange
Or Perhaps like this from Another sheet:-
Code:
Dim a
With ActiveSheet.Shapes("List Box 2").ControlFormat
    .RemoveAllItems
    For a = 1 To 10
        .AddItem Sheets("Sheet53").Cells(a, 1)
    Next
End With
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791

ADVERTISEMENT

Hi Mick,

Thanks for your response and code suggestions. I am listing stock trading pairs and one or the other of the pair may be repeated so I definitely need a two column listbox but without ActiveX. Any ideas on how to do this?

Thanks,

Art
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Your could Load then in the List box as strings as below:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG31May08
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sht [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Set[/COLOR] Sht = Sheets("Sheet1")
[COLOR="Navy"]With[/COLOR] ActiveSheet.Shapes("List Box 2").ControlFormat
    .RemoveAllItems
    [COLOR="Navy"]For[/COLOR] a = 1 To 10
        .AddItem Sht.Cells(a, 1) & Space(6) & Sht.Cells(a, 2) & Space(6) & Sht.Cells(a, 3)
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791

ADVERTISEMENT

Hi Mick,

Thanks for the revised code. When I run your macro, I get the following error: Runtime error 5. Invalid procedure call or argument. Do you have any ideas on what the problem is?

Thanks,

Art
 

Watch MrExcel Video

Forum statistics

Threads
1,129,756
Messages
5,638,177
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top