PDA

View Full Version : Click on Cell and open worksheet



captHedge
03-12-2008, 02:45 AM
Hi All,

What I am looking to do is have a frontend sheet that if I click on a cell, then based on information row/column open another worksheet to display data.

Is this possible ? or would it be easier to display a message box with the data ?

Thanks
captHedge

Bob Phillips
03-12-2008, 03:02 AM
Does this get you started?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Select Case .Value

Case "ABC": Workbooks.Open "C:\test\file1.xls"
Case "XYZ": Workbooks.Open "C:\test\file2.xls"
'etc.
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

captHedge
03-12-2008, 03:17 AM
Thanks... that's just the start I needed...

Great Work :)