Thread: making a literal from expression

Hello,

I was just wondering if anone knew how to make a literal into an expression. In my code I have the name of the ComboBox that needs to change within a loop, however when I use that with the select case, the "UCase" makes it match "combobox#" instead of what it is representing.

The ComboBoxes are categories; then depending on what category from the combobox is selected the number entered beside it will be added to a variable (such as the first one is restaurants) which is keeping track of the totals.

Please let me know if anyone has any suggestions. Below I posted my code

Thanks!

For cboxnum = 1 To 32
cbnum = ("ComboBox" + CStr(cboxnum) + ".value")


Select Case UCase(cbnum)
Case "RESTAURANTS"
restaurants = restaurants + wksInput.Range("o3")
Case "GROCERIES"
groceries = groceries + wksInput.Range("o3")
Case "COFFEE"
coffee = coffee + wksInput.Range("o3")
Case "ENTERTAINMENT"
Entertainment = Entertainment + wksInput.Range("o3")
Case "OTHER FOOD"
ofood = ofood + wksInput.Range("o3")
Case "RENT"
rent = rent + wksInput.Range("o3")
Case "CAR PAYMENTS"
carpmts = carpmts + wksInput.Range("o3")
Case "PARKING"
parking = parking + wksInput.Range("o3")
Case "GAS"
investments = investments + wksInput.Range("o3")
Case "OTHER BILLS"
obills = obills + wksInput.Range("o3")
Case "CLOTHING"
clothing = clothing + wksInput.Range("o3")
Case "DONATIONS"
donations = donations + wksInput.Range("o3")
Case "GIFTS"
gifts = gifts + wksInput.Range("o3")
Case "FAMILY"
family = family + wksInput.Range("o3")
Case "OTHER"
other = other + wksInput.Range("o3")
End Select
Next

Assuming that your combo boxes are on a user form, you can specify them with the controls property of the form.

e.g.

MyComboBoxName = UserForm1.Controls(0).Name

Does this help?

Try

ActiveSheet.drawingobjects(1).Name

    For cboxnum = 1 To 32
        Select Case UCase(ActiveSheet.OLEObjects("ComboBox" & cboxnum).Object.Value)
            Case "RESTAURANTS"
                restaurants = restaurants + wksInput.Range("O3").Value
            Case "GROCERIES"
                groceries = groceries + wksInput.Range("O3").Value

Great! Would you please mark the thread as Solved?