Partly working macro - variable fails if it holds certain data

Hello,

so, IŽve this piece, which is working if it holds first half of variables, and fails with the second part of em, any clues what might be causing it?:

Sub menu_cmd_1_Click()
Dim i As Integer
Dim j As Integer

frm_menu_months.Hide
frm_wait.Show False
DoEvents
Application.ScreenUpdating = False
For i = 0 To menu_lbx_1.ListCount - 1
    If menu_lbx_1.Selected(i) = True Then
        For j = 0 To menu_lbx_2.ListCount - 1
            If menu_lbx_2.Selected(j) = True Then Application.Run menu_lbx_1.List(i) & "_" & menu_lbx_2.List(j)
        Next j
    End If
Next i
Application.ScreenUpdating = True
frm_wait.Hide
End Sub

the concept is simple, form has two list boxes, where first contains values such as: "A998", "C995" etc. and the second contains names of months: "January", "February"....multi select is enabled....user chooses from both listboxes and click the button, where the macro above is executed....so to read it correctly, the line with:

If menu_lbx_2.Selected(j) = True Then Application.Run menu_lbx_1.List(i) & "_" & menu_lbx_2.List(j)

makes app to run macro with name i.e.: "A998_January".....all those macros are in the module, the macro copied above resides in the form module.

and now to the issue: If user chooses options starting with "A" (A998, A989 etc.) macro works as expected, BUT if user chooses those with "C" (C998, C995) it fails with error msg 1004 and the note that macro is not present in the workbook or might be restricted....any ideas what the hack is going on??

hope anyone can help me on it, cause IŽm totaly desperate.

Best regards

Soul

Hi there,

Can you post a sample of your workbook here so that we can take a better look at it?

Regards,

Greg M

Hi again,

Well, we've discovered something K R A Z E E E here!!!

The good news is that there's nothing actually wrong with the way your code is written (apart from the fact that it doesn't actually work!) It seems that Excel 2013 (and possibly earlier versions also?) doesn't like macros whose names are "C" or "C" followed by numbers (apart from "C0" which it appears to tolerate).

Write the following macro:

Sub c()
    MsgBox "Hello"
End Sub

and try to run it using the Alt+F8 keys. What happens? The "Run" key is disabled, and the only key which is enabled is "Create".

Now for the good news - add a new VBA code module, insert the same (i.e. above) macro in it, and try to run it using the Alt+F8 keys - success! Excel will allow you to run either of the two macros, but will not allow it if only one of the macros exists. S T R A N G E !!!

The workaround? Have a look at the attached workbook. It contains a separate module (declared as a Private module) which contains "empty" versions of all of your routines which begin with "C" followed by numbers. This seems to allow the "real" versions of the routines to be run.

Alternatively, if it suits you to do this, the code seems to run correctly when the workbook is saved in "Excel 97 - 2003 workbook" format, i.e. with a ".xls" extension

I hope this is helpful to you - PLEASE let me know how you get on with it!

Regards,

Greg M

Anyways, rereading your start Greg it sounds like a start for a joke...."The good news is that there's nothing actually wrong with the way your code is written (apart from the fact that it doesn't actually work!) "....:-D kinda funny statement...:-D...but true one...;-).

So I was rly curious if my idea gonna work and it actualy does. So here goes the code that solves my issue with the name of macros....;-)

Sub menu_cmd_1_Click()
Dim Plant As String
frm_menu_months.Hide
frm_wait.Show False
DoEvents
Application.ScreenUpdating = False
Dim i As Integer
Dim j As Integer
For i = 0 To menu_lbx_1.ListCount - 1
    If menu_lbx_1.Selected(i) = True Then
        For j = 0 To menu_lbx_2.ListCount - 1
            If menu_lbx_2.Selected(j) = True Then
                If InStr(menu_lbx_1.List(i), "C") > 0 Then
                    Plant = Replace(menu_lbx_1.List(i), "C", "D")
                    Application.Run Plant & "_" & menu_lbx_2.List(j)
                Else
                    Application.Run menu_lbx_1.List(i) & "_" & menu_lbx_2.List(j)
                End If
            End If
        Next j
    End If
Next i
Application.ScreenUpdating = True
frm_wait.Hide
End Sub

HUGE thx to Greg to address the issue

Best regards

Soul

Hi Greg M,

Big thanks to you man, I thought IŽm blind or crazy and that there might be some syntax mistake, but obviously error wasnt on my part which is good. Secondly, IŽve tried to put your module2 in the master file but its not working, error is still the same, but that is not a problem anymore since youŽve recognized the cause of it. IŽll implement a simple replace function in the process so I can keep the UI same, but rename the macros and IŽll do just fine I believe.

once more Big thanks to you, youŽve saved my sanity

best regards

Soul

PS -- IŽll mark this thread as solved just when i get that one working, prolly during the day (just in case my perception is flawed)

Hi again Soul,

Many thanks for your feedback - I'm really pleased that I was able to help out and that things are working correctly for you now.

Thanks also for the Reputation increase

Best regards,

Greg M