Partly working macro - variable fails if it holds certain data

[SoulPrisoner]

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

[Greg M]

Hi there,

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

Regards,

Greg M

[SoulPrisoner]

Hi Greg M,

IŽll check on the file If I can make it shareable, but I believe that it wont be any use for you since the most important part is getting data from our server disk and that one wouldnt work for you....the file is rly simple just to process output of ERP system.

Best regards

Soul

EDIT1 So IŽve edited the file so I can share it here, but macros wont work (the final one) since the reason explained above - you dont posses autorization to access our serverdisk...sample.xlsm

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

Please Login or Register  to view this content.

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

[SoulPrisoner]

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)

[SoulPrisoner]

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....;-)

Please Login or Register  to view this content.

HUGE thx to Greg to address the issue

Best regards

Soul

[Greg M]

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