Count Listbox items to Convert to Uppercase

[dmqueen]

Good Morning,
I have two listboxes that are dynamically populated at runtime so the user can add items. I want to add an Uppercase converserion to the text before these items are displayed Since the number of items can e different every time. I'm trying to use listbox.Itemscount as my loop max. But I'm receiving an Object required Runtme 424 error on this line. Suggestions?

<code>
Sub Machine_Change()
'for each entry in Machines
'convert it to UPPER CASE
'display in listbox
'save to Machines worksheet
Dim counter As Integer
'next line receives error
For counter = 0 To Machines.Items.Count
Machines.counter = UCase(Machines.counter)
Next counter
End Sub


Sub PressMachine_Change()
'for each entry in PressMachines
'convert it to UPPER CASE
'display in listbox
'save to PressMachines worksheet
Dim counter As Integer
'next line receives error
For counter = 0 To PressMachines.Items.Count
Machines.counter = UCase(PressMachines.counter)
Next counter
End Sub
</code>

 

[Jerry Sullivan]

Hi dmqueen,

If your ListBox object is named "Machines" then this syntax should work...

Sub Machines_Change()
 'for each entry in Machines
 'convert it to UPPER CASE
 'display in listbox
 'save to Machines worksheet
 Dim counter As Integer
 'next line receives error
 With Machines
   For counter = 0 To Machines.ListCount - 1
      .List(counter) = UCase(.List(counter))
   Next counter
 End With
End Sub

Rather than have the uppercase conversion occur on each Change event, it would be better to have it just happen when the ListBox is initially populated.

Be careful to match the name of the ListBox exactly as both your examples have mismatches between singular (PressMachine_Change) and plural (PressMachines.counter).

 

[dmqueen]

Hi dmqueen,

If your ListBox object is named "Machines" then this syntax should work...

Sub Machines_Change()
 'for each entry in Machines
 'convert it to UPPER CASE
 'display in listbox
 'save to Machines worksheet
 Dim counter As Integer
 'next line receives error
 With Machines
   For counter = 0 To Machines.ListCount - 1
      .List(counter) = UCase(.List(counter))
   Next counter
 End With
End Sub

Rather than have the uppercase conversion occur on each Change event, it would be better to have it just happen when the ListBox is initially populated.

Be careful to match the name of the ListBox exactly as both your examples have mismatches between singular (PressMachine_Change) and plural (PressMachines.counter).

Hello Jerry,

Thanks for your suggestion.
Unfortunately, it didn't work. I received a Runtime Error 438 Object doesn't support this property or method. Could the problem be that I have multiple list boxes of this name on different sheets that I am trying to handle with 1 macro? I tried adding an ActiveSheet. I front of the ListBoxMachines but no help

 

[Jerry Sullivan]

No the multiple listboxes with the same name on different sheets isn't the problem. I assumed that you are using ListBoxes on a UserForm instead of embedded in the worksheets and the code will be different.

How are you populating your listboxes? Are you using the ListFillRange property or a method like AddItem?

 

[dmqueen]

Jerry,

I just used the control's input range and used a named range on a separate worksheet. I'm converting a Lotus file that had 3300 lines of structured code to Excel and trying to use using O.O.P. practices to severely cut the code down and reuse items when I can.

 

[Jerry Sullivan]

If you are populating the ListBox using its ListFillRange then you'll need to change the Case of the values in the worksheet ranges to have that change reflected in items displayed in the ListBox. If that's what you want to do, it might be simpler to just have the values in the named ranges Upper Case to begin with, or converted to Upper Case any time they are edited by the user.

Another a way to populate the ListBoxes is to use the .AddItem and/or .List. This is often recommended over using the ListFillRange, and it gives you the flexibility to add/remove or change items in the ListBox without changing the worksheet range.

If you want to try that technique with OOP-style coding, copy this code into a standard code module in your workbook.

Public Sub Populate_Control_List(ByVal objControl As Object, _
   ByVal rList As Range)
'--populates an ActiveX ListBox or ComboBox with
'     items in the 1 column range rList
 
 objControl.ListFillRange = vbNullString
 If rList Is Nothing Then
   objControl.Clear
 Else
   If rList.Rows.Count > 1 Then
      objControl.List = rList.Value
   Else
      objControl.Clear
      objControl.AddItem rList.Value
   End If
 End If
End Sub


Public Sub UCaseListItems(ByVal objControl As Object)
'--converts each item in ActiveX ListBox or ComboBox to uppercase
 Dim lCounter As Long

 With objControl
   For lCounter = 0 To .ListCount - 1
      .List(lCounter) = UCase(.List(lCounter))
   Next lCounter
 End With
End Sub

Then place this code in the Sheet Code Module of any sheets that have ListBoxes to be populated to call the shared Subs.

Private Sub Worksheet_Activate()
 '--populate with list of items in named range MyList
 Call Populate_Control_List(objControl:=Me.Machines, _
   rList:=Me.Range("MyList"))
 Call UCaseListItems(objControl:=Me.Machines)
End Sub

Modify this code to match the names of your ListBoxes and named ranges.

If you have many ListBoxes and a relationship between the name of the ListBox and the Named Range, this could be further streamlined and placed in the ThisWorkbook module.

Each time the worksheet is activated, the lists will be repopulated.
If you will be changing items in your named ranges and need those to update dynamically, you could call the procedure through a Worksheet_Change event.

 

[Rick Rothstein]

Public Sub UCaseListItems(ByVal objControl As Object)
  '--converts each item in ActiveX ListBox or ComboBox to uppercase
  Dim lCounter As Long
  With objControl
    For lCounter = 0 To .ListCount - 1
      .List(lCounter) = UCase(.List(lCounter))
    Next lCounter
  End With
End Sub

First, a point about the argument for the above subroutine... you show it as being passed ByVal. That instruction is being ignored as you cannot pass objects ByVal... objects are always passed ByRef. If you actually were passing it ByVal, then your subroutine would not work as the upper casing you did to each element of the ListBox would have been made to a copy of the ListBox and not the actual ListBox itself. Since all your changes were reflected back to the passed object, that could only mean it was passed ByRef.

Now, I think you are aware of my penchant for compact code, especially one-liners. Yep... I have a one-liner version of your above subroutine.

Sub UCaseListItems(Obj As Object)
  Obj.List = Split(UCase(Join(Application.Transpose(Obj.List), Chr(1))), Chr(1))
End Sub

 

[Jerry Sullivan]

Rick, It's my understanding that objects can be passed ByVal. That doesn't preclude the object's properties being changed by the called function.

A difference between passing an object ByRef and ByVal is that when passed ByRef, the called procedure can change what the argument is referencing and pass those changes back up the calling procedure.

Here's an example using a Range object which may be clearer than using a ListObject example....

Sub MyCaller()
   Dim rngV As Range, rngR As Range
   
   Set rngV = Range("A1:A10")
   Set rngR = Range("B1:B10")
   Call CalledProc(rngVal:=rngV, rngRef:=rngR)
   
   MsgBox "RngV: " & rngV.Address & vbCr & _
      "RngR: " & rngR.Address
  
End Sub

Sub CalledProc(ByVal rngVal As Range, ByRef rngRef As Range)
   rngVal.Interior.Color = vbYellow
   rngRef.Interior.Color = vbRed
   
   Set rngVal = Range("A11:A20")
   Set rngRef = Range("B11:B20")

End Sub

The explicit ByVal keyword indicates the intent not to pass changes to what the object is referencing back to the calling procedure.