Open Files by Clicking on a Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a dedicated folder, a workbook named “Dashboardâ€, with a single worksheet
(also named Dashboard), contains a matrix of Departments, with performance
ratings in three categories, A1, A2, and A3, in columns F, I, and L.
(Columns G and J are hidden.)

In that same folder are 51 workbooks for various Departments - example
below. These files are 6mb each.

The requirement: While users have Dashboard on screen, they want to click
on a particular cell, say Operations A1 (75%), cell F11, and have the
OperationsA1.xls file come on screen.

What would the code be to make this work? A quick open on the 6mb file
would be a big plus. The three ranges of “click†cells would be F10:F17,
I10:I17, and L10:L17.

Is there a clean, efficient architecture to this project? Any suggestions
appreciated.

I plan to put a control button in Department worksheets to return to
Dashboard – or should this be done by code too?


Dashboard.xls:
Col. Col. Col.
F J
L
---- ---- ----
Row 9 A1 A2 A3
Row 10 Finance 92% 85% 100%
Row 11 Operations 75% 97% 92%
Etc. Etc. Etc. Etc. Etc.
Row 17 Engineering 88% 96% 99%


Department workbooks:
FinanceA1.xls
FinanceA2.xls
FinanceA3.xls
OperationsA1.xls
OperationsA2.xls
OperationsA3.xls
Etc.
EngineeringA1.xls
EngineeringA2.xls
EngineeringA3.xls
 
Right click on the sheet tab>view code>left window select worksheet>right
window select double click>insert this.
Now when you double click on a cell with Operations A1 typed in it (BY
ITSELF), the workbook will open/ or activate if opened already.

If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
 
Back
Top