10 Buttons (Shapes) only first 9 work. #10 does not.

[KarateKidClone]

I have a POS that is using 10 Shapes (Categories) as buttons. I have a Macro to call the Subcategories. It works for 9 of the buttons, but not 10.

Sub POS_LoadSubCategory()
With pos
    'Remove any existing product shapes
    For Each ProdShp In pos.Shapes
        If InStr(ProdShp.Name, "Subcategory") > 0 Then ProdShp.ShapeStyle = msoShapeStylePreset23
        If InStr(ProdShp.Name, "Product") > 0 Then ProdShp.Delete
    Next ProdShp
    SelCat = Right(Application.Caller, 1) 'Category Number
    For CatNumb = 1 To 10
        .Shapes("Category" & CatNumb).BackgroundStyle = msoBackgroundStylePreset3
    Next CatNumb
    .Shapes("Category" & SelCat).ShapeStyle = msoShapeStylePreset30
    PRODUCTS.Range("J3").Value = .Shapes("Category" & SelCat).TextFrame2.TextRange.Text
    For SubCatNumb = 1 To 12
        .Shapes("Subcategory" & SubCatNumb).TextFrame2.TextRange.Text = ADMIN.Cells(SubCatNumb + 4, SelCat + 3).Value
    Next SubCatNumb
End With
End Sub

 

[KarateKidClone]

All Shapes are grouped and named "CategoryGrp#" 1-10

 

[KarateKidClone]

This is the 10th button in question.

The error I get when I click the Button

Break in the Code.

If Just run the macro I get the following error:

The tutorial that helped me write this code said that if using more than 9 categories, a different formula should be used, but never went into any more detail.

 

[Rick Rothstein]

When you assign your value to the SelCat variable, you are using this line of code...

SelCat = Right(Application.Caller, 1)

This line of code takes the last character in the cell which, for your your 10th button is 0, not 10... this is why it fails. I don't have time now to look at your code to find a workaround for you, but a quick fix would be to change your first nine button numbers from 1, 2, 3, etc. to 01, 02, 03, etc. and change the 1 in the above line of code to 2.

 

[KarateKidClone]

Thank you for the reply, Rick. I'm very new to VBA so I appreciate any help.

I have made the suggested changes and it is still crashing in the same place in the VBA. With the change from 1 to 2, I can no longer press any of the 10 buttons. Gives me the same error for all buttons. changing back gives me previous functionality.

 

[Rick Rothstein]

Thank you for the reply, Rick. I'm very new to VBA so I appreciate any help.

I have made the suggested changes and it is still crashing in the same place in the VBA. With the change from 1 to 2, I can no longer press any of the 10 buttons. Gives me the same error for all buttons. changing back gives me previous functionality.

You have to carry the name changes through all your code. I'm guessing your button event procedures do not have 0's in front of their single digit numbers. Also, you have to look within your various code modules and make sure they are using the correct names or references.

 

[KarateKidClone]

I replaced

SelCat = Right(Application.Caller, 1)

with

If Instr(Application.caller, "Category") > 0 then SelCat = Replace(Application.Caller,"Category","") Else SelCat = Replace(Application.Caller,"CatIcon","")

Which should allow for unlimited category buttons