Trying to link a variable to a public const

Hello there,

Can you please add code tags around you post? Also, what values will be entered into the text box? For instance what is entered into the textbox to make the variable location equal to Public Const Tim= "G:\Users\Tim\" and so on?

Thanks!

I will be entering a client name into the inputbox & using that name in the save as string (saving the pdf as "client name 1/2/3/4 Q Account Overview". So basically, the client name I type into the inputbox has two functions. 1. used in save as string and 2. used to determine which file location that client should be saved to based on the public const I set up for each client. Each client has a different file location (separate folder) & I am trying to be able to type just a client name & my macro will save it in the correct folder. Thanks

As structured, I'm thinking a Select Case statement might be the easiest to program:

Please Login or Register  to view this content.

Thank you. I have over 100 client folders so will that many case statements slow down the macro? I guess there really isn't a better way to store these directories in the module.

I don't know how that many cases will effect performance.

There might be -- I'm not sure of your reasons for storing these values in constants. If I were doing this, I might try storing the directory names in a lookup table in a spreadsheet or in an array (I don't think constants can store arrays. If I'm wrong someone correct me). Then you could use some kind of lookup statement to return the directory string. There might be performance penalties for building the array or referencing the worksheet, too, so I'm not sure if this is better or just different.

Try this instead

Please Login or Register  to view this content.

This macro is inside an add-in and I run it on individual client's spreadsheets so I cannot house the file locations string inside a central spreadsheet because I run this macro on tons of indivdual spreadsheets. Each client has a performance report saved in their own client folder. I open each file separately & audit the data then run the macro to save a file verison as a pdf.

Each client's folder location is unique so I cannot use Location = "G:\Users\" & MyInput & "\"

I finished entering all the file locations to the macro & it run quick so all good.

Thanks for your help!