Goto Sheet based on Cell Value

E

Eric

I am trying to write a macro that will go to a specific
sheet with the same name as the value of a cell.

Ex. I have a cell that holds a drop down list with users
names. I would like to select a user, say "eric", hit a
botton that will take me to a sheet called "eric".

Thanks
 
J

JE McGimpsey

One way:

Public Sub Button1_Click()
On Error Resume Next
Worksheets(Range("A1").Text).Activate
On Error GoTo 0
End Sub
 
G

Guest

Perfect. This worked just as I had hoped.

Can I add a little bit of complexity? If the destination
sheet is password protected, is there a way to unprotect
it with a password on the originating sheet?

Ex. A user would have to enter a user name and a password
on the starting (originating) sheet and the password would
be incorporated into the macro to unprotect the
destination sheet.

Thanks
 
J

JE McGimpsey

Since XL's worksheet protection is so weak, I wouldn't advise that you
use it to limit who can make changes. Anyone with the gumption to find
these newsgroups can find password breaking software, such as

http://www.mcgimpsey.com/excel/removepwords.html

Therefore, I'd recommend using a password only to prevent inadvertent
changes, and using the same password for each sheet, doing something
like:

Public Sub Button1_Click()
Const PWORD As String = "drowssap"
On Error Resume Next
With Worksheets(Range("A1").Text)
.Activate
.Unprotect PWORD
End With
On Error GoTo 0
End Sub
 
Top