Count Listbox items to Convert to Uppercase

dmqueen

Board Regular
Joined
Aug 5, 2014
Messages
53
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
:confused:</code>
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi dmqueen,

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

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

Board Regular
Joined
Aug 5, 2014
Messages
53
Hi dmqueen,

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

Code:
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
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Aug 5, 2014
Messages
53
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

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

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
Code:
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.
Code:
Sub UCaseListItems(Obj As Object)
  Obj.List = Split(UCase(Join(Application.Transpose(Obj.List), Chr(1))), Chr(1))
End Sub
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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....

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

Forum statistics

Threads
1,081,798
Messages
5,361,366
Members
400,628
Latest member
teresajm

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top