Data Validation linked to folders in ActiveWorkbook.Path

[plans]

Hello,
I have a folder located in the ActiveWorkbook.Path, called “Sales”
The “Sales” folder might contain various subfolders, for example:
East Teritory
West Teritory
North Teritory
South Teritory
but could be anything else….
I need to “Data Validate” a cell which would pull up at the Data Validation Drop Down the subfolders names and when a subfolder is selected from the Drop Down, I want that cell to be hyperlinked to the subfolder.
(see screenshots)
Please advice.Screenshots.JPG
Thanks

[Kevin#]

Try this.

How it works
The macro kicks in automatically when the cell value is changed, taking the user to the selected subfolder of activeworkbook.path\sales\ (indicated by the cell value)

What to do
- Paste this code into the module of the sheet containing the cell with validation
- Amend "$A$2" to the correct cell reference - include both "$" symbols
(right click on sheet tab and select View Code)
- Change selected value in the cell and see if it works!

Please Login or Register  to view this content.

[plans]

Kevin,
Thank you very much.
It works!
I just wanted to amend it :

I want that cell to be hyperlinked to the subfolder

I mean, instead of having the .Show, I want the cell to be hyperlinked to the target folder.
Is that possible?
Thanks again.

[Kevin#]

I expected you to say something like this. A hyperlink opens a file, activates a worksheet, opens a web page etc.
What are you aiming to do when you get to the subfolder?

[Kevin#]

'Removed and replaced by post#3

[Kevin#]

Does this meet your needs?

Put this in the sheet module and amend $A$1 to match the relevant cell
Macro creates the link on the hoof and takes you there

Please Login or Register  to view this content.

[plans]

Kevin,
Thank you, this is it.
Works fine!

[Kevin#]

Great.
Please mark the thread as solved