Using a cell number instead of a filepath in declaration

[Sc0tt1e]

I had an idea a while back which I have been unable to resolve and wondered if anyone knew a solution.

I create funky things that get passed around the office and different people store them in different locations, instead of having to walk around huge offices and changing the code for everyone that uses it I thought I could insert a user form that you can use Application.GetOpenFilename so they can save the document and then enter the file location themselves, which is saved on a hidden sheet in a cell which the code can refer to instead.

E.g.Instead of declaring a folder location in the code (i.e. Set Filepath = "C:\My Computer\My Location\My Folder") is it possible to enter the filepath into an excel cell (for the example A5) and call A5 within the code rather than the location and still declare it as Filepath.

[romperstomper]

Yes, you just refer to the range instead of a literal value:

Please Login or Register  to view this content.

[Sc0tt1e]

I tried that and it wouldn't work.

What if the filepath for the spreadsheet has also already been defined?

i.e.

Please Login or Register  to view this content.

[romperstomper]

You don't use Set with Strings and you have to specify a worksheet for the range. Note my code versus yours.

[Sc0tt1e]

So would something like this work?

BA1 would contain "C:\Users\Scott\Documents\CMC\Database.xlsm"

Please Login or Register  to view this content.

[romperstomper]

Not quite - this will fail because FPath is a String:

Please Login or Register  to view this content.

You need:

Please Login or Register  to view this content.

[Sc0tt1e]

Ahhhhhh, you star.

Cheers Romper, you have just made my life a hell of a lot easier.

[romperstomper]

Glad to help.