Code to determine name of Worksheet
 

Probably a bit of an easy one...I have a selection of hidden worksheets which
appear in a drop down box and linked to a cell with a vlookup. I than have a
macro button which, once clicked, opens a worksheet. I want to add some code
that opens up the result of the vlookup determined by the dropdown box. For
example, if I select Sheet5 from the drop down box which in turn becomes the
sheet name in the vlookup cell, when I click the Macro button, Sheet5 opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant" wrote in message
...
Probably a bit of an easy one...I have a selection of hidden worksheets
which
appear in a drop down box and linked to a cell with a vlookup. I than have
a
macro button which, once clicked, opens a worksheet. I want to add some
code
that opens up the result of the vlookup determined by the dropdown box.
For
example, if I select Sheet5 from the drop down box which in turn becomes
the
sheet name in the vlookup cell, when I click the Macro button, Sheet5
opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

I'm not clear on why you have a VLOOKUP. If the validation list is a list of
available worksheet names and the list is located in A1, use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[A1]) Is Nothing Then
Sheets(Target.Value).Select
End If
End Sub

Right-click on the worksheet tab, go to View Code, and past in the code above.

HTH
Jason
Atlanta, GA


"Ant" wrote:

Probably a bit of an easy one...I have a selection of hidden worksheets which
appear in a drop down box and linked to a cell with a vlookup. I than have a
macro button which, once clicked, opens a worksheet. I want to add some code
that opens up the result of the vlookup determined by the dropdown box. For
example, if I select Sheet5 from the drop down box which in turn becomes the
sheet name in the vlookup cell, when I click the Macro button, Sheet5 opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

Thanks Bob,

Not sure I need Data Validation. Once the user selects the choice from the
drop down box, that choice appears in a cell ie A:1. (via a vlookup). All I
need is code that recognises what is in that cell.

ie Sheets("What is in cell A:1").Select

"Bob Phillips" wrote:

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant" wrote in message
...
Probably a bit of an easy one...I have a selection of hidden worksheets
which
appear in a drop down box and linked to a cell with a vlookup. I than have
a
macro button which, once clicked, opens a worksheet. I want to add some
code
that opens up the result of the vlookup determined by the dropdown box.
For
example, if I select Sheet5 from the drop down box which in turn becomes
the
sheet name in the vlookup cell, when I click the Macro button, Sheet5
opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

Bob,

I tried that code and it works perfectly. however it only works if the
worksheet it is looking for is not hidden. Do you know what the additional
code is to unhide and select the Sheet name in eg cell "H10".

"Bob Phillips" wrote:

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant" wrote in message
...
Probably a bit of an easy one...I have a selection of hidden worksheets
which
appear in a drop down box and linked to a cell with a vlookup. I than have
a
macro button which, once clicked, opens a worksheet. I want to add some
code
that opens up the result of the vlookup determined by the dropdown box.
For
example, if I select Sheet5 from the drop down box which in turn becomes
the
sheet name in the vlookup cell, when I click the Macro button, Sheet5
opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

You may want to check to make sure that the worksheet actually exists, too:

Option Explicit
Sub testme()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Sheets(ActiveSheet.Range("H10").Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Doesn't exist!"
Else
With testWks
.Visible = xlSheetVisible
.Select
End With
End If

End Sub






Ant wrote:

Bob,

I tried that code and it works perfectly. however it only works if the
worksheet it is looking for is not hidden. Do you know what the additional
code is to unhide and select the Sheet name in eg cell "H10".

"Bob Phillips" wrote:

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant" wrote in message
...
Probably a bit of an easy one...I have a selection of hidden worksheets
which
appear in a drop down box and linked to a cell with a vlookup. I than have
a
macro button which, once clicked, opens a worksheet. I want to add some
code
that opens up the result of the vlookup determined by the dropdown box.
For
example, if I select Sheet5 from the drop down box which in turn becomes
the
sheet name in the vlookup cell, when I click the Macro button, Sheet5
opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.




--

Dave Peterson

Thanks Dave - that works a treat. Good man!

"Dave Peterson" wrote:

You may want to check to make sure that the worksheet actually exists, too:

Option Explicit
Sub testme()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Sheets(ActiveSheet.Range("H10").Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Doesn't exist!"
Else
With testWks
.Visible = xlSheetVisible
.Select
End With
End If

End Sub






Ant wrote:

Bob,

I tried that code and it works perfectly. however it only works if the
worksheet it is looking for is not hidden. Do you know what the additional
code is to unhide and select the Sheet name in eg cell "H10".

"Bob Phillips" wrote:

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant" wrote in message
...
Probably a bit of an easy one...I have a selection of hidden worksheets
which
appear in a drop down box and linked to a cell with a vlookup. I than have
a
macro button which, once clicked, opens a worksheet. I want to add some
code
that opens up the result of the vlookup determined by the dropdown box.
For
example, if I select Sheet5 from the drop down box which in turn becomes
the
sheet name in the vlookup cell, when I click the Macro button, Sheet5
opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.




--

Dave Peterson