VBA has errors with Form Control Listbox

[artz]

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:

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:

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:

Set lbtarget = ListBox88

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

Thanks,

Art

 

[JoeMo]

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]

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]

I think you will only get one column in a "Forms" Listbox and you need to fill it like this:-

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:-

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]

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]

Your could Load then in the List box as strings as below:-

[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]

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

 

[MickG]

Have a look at this Example:-
https://app.box.com/s/mm0k389l0yj565hyl9rohip33wsdvwhe

 

[artz]

Thanks, Mick, I'll take a look. -Art