Sheets("NC-41282").Visible = True
Sheets("NC-41282").Select
But i have to do that for each NC-Number. what I am looking for is to
do it so the vba knows whic sheet to open based on which cell is
clicked. So cell B4 for example shows NC-41283. I wanted so when that
cell is clicked the tab NC-41283 opens. C4 would be NC-41284 and so
on. I am obviously not really adept at vba so any help would be
appreciated. Thanks
Or better yet. Assign the code to a button that takes the info from
say cell B4 and says open whatever tab is in cell B4. that way they
can enter whichever they want
i guess the "tab's names" are in cells A4: AD4
you could use "Worksheet_SelectionChange" event to execute this task.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set isect = Application.Intersect(Target, Range("A4:AD4"))
If Not isect Is Nothing Then
Sheets(Target).Visible = True
Sheets(Target).Select
End If
End Sub
--
isabelle
i.e. only one sheet plus Contents sheet open at any time.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim wsh As Worksheet
Const WS_RANGE As String = "B4:AE4" '30 sheets edit to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Sheets(Target.Value)
.Visible = True
.Select
End With
End If
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name <> Target.Value Then wsh.Visible = xlSheetHidden
Next wsh
Sheets("Contents").Visible = True
Cancel = True
ws_exit:
Application.EnableEvents = True
End Sub
This is sheet event code. Assumes you have a sheet named "Contents".
Right-click on "Content" tab and select "View Code"
Copy/paste into that module.
Alt + q to return to Excel...........double-click on a cell within
ws_range
Gord Dibben Microsoft Excel MVP
On Thu, 25 Aug 2011 11:03:51 -0700 (PDT), pat67 <pbu...@comcast.net>
wrote:
What about what i said in my second part? If i just want use the
contents of a single cell as the named sheet to open? In other words,
in cell E4 say they would enter NC-41282 and then i have a button that
says "Edit". when they click the button, the NC-41282 tab is opened.
Enter a sheet name in E4 to unhide that sheet.
I would use a data validation dropdown list in E4 on Contents sheet
and choose from that list.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Const WS_RANGE As String = "E4"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Sheets(Target.Value)
.Visible = True
.Select
End With
End If
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name <> Target.Value Then wsh.Visible = xlSheetHidden
Next wsh
Sheets("Contents").Visible = True
Cancel = True
ws_exit:
Application.EnableEvents = True
End Sub
Gord
On Thu, 25 Aug 2011 13:02:09 -0700 (PDT), pat67 <pbu...@comcast.net>
wrote:
>What about what i said in my second part? If i just want use the
Sub unhide()
Dim wsh As Worksheet
Dim rng As Range
Set rng = Sheets("Contents").Range("E4")
Sheets(rng.Value).Visible = True
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name <> rng.Value Then wsh.Visible = xlSheetHidden
Next wsh
Sheets("Contents").Visible = True
End Sub
Select from DV list or type a name in E4 then hit your button to run
the macro.
Gord
Private Sub CommandButton1_Click()
sh = ActiveSheet.Range("E4")
Sheets(sh).Visible = True
Sheets(sh).Select
End Sub
--
isabelle
Le 2011-08-25 16:02, pat67 a �crit :