Moving from one worksheet to another based on a cell value

[mpolitte]

Hello,

I have an excel workbook in which I'd like to jump from one sheet to another based on the value of a cell. The cell contains the following list of values: numeric, string, and list. When the cell is set to "numeric", I'd like to redirect the user to the "numeric" worksheet at a particular cell location. When the cell is set to "string" I'd like to redirect the user to the "string" worksheet, and so on. What is the best way to do this? With a macro, a function, or VBA code? Can you include sample code with your answer? Thank you!

[mdmackillop]

This goes into the Worksheet module and is triggered when A1 is changed.


Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "A1" Then Exit Sub
'Go to sheet
Sheets(Target.Value).Activate
Go to cell
Select Case Target
Case "Numeric"
ActiveSheet.Range("A1").Select
Case "String"
ActiveSheet.Range("A2").Select
End Select
End Sub

[mpolitte]

Thank you for the code snippet. I was able to jump from a cell to a worksheet depending on the value of the cell. Now I'd like to genericize this capability. I would like to jump to a specific worksheet if I change the value of a cell in column D where column D ranges from D8 to D1000. In other words, if I'm in column D and I change a cell value to "list, numeric, or string" I want to jump to the appropriate list, numeric, or string worksheet. Your sample code was specific to cell "A1" as shown below:


If Target.Address(0, 0) <> "A1" Then Exit Sub

How can I change this to see if I'm in column D? Thank you!!!

[mdmackillop]

If Intersect(Target, Range("D8:D1000") is Nothing Then Exit Sub

[mpolitte]

Thank you so much for your help and the expedience of your replies. The Intersect function worked like a champ. As background, I'm trying to create a Data Dictionary Tool to capture Data requirements for a network management card. Data quantity will range from 300-1000 points and have detailed information such as numeric type (base, resolution, access, units,...), list type (bits, signed, access,...), string type (min, max, encoding, access), metadata, help text, comment text, status, and so on. I am working on this spread sheet in my spare time, so your assistance is invaluable since I have minimal time to learn Excel VBA.

Adding the Intersect function works great when I'm modifying a cell in column D, but when I tried to cut and paste two rows I received a Runtime error '13' - type mismatch. I've attached the code. I can see the problem, when I try to cut and paste a row "Target" is no longer a cell value, anyway to catch that? Thanks again! Maureen


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D8:D1000")) Is Nothing Then Exit Sub
'Go to Numeric, List, or String sheet
Select Case Target
Case "Numeric"
Sheets("Numeric").Activate
ActiveSheet.Range("D8").Select
Case "String"
Sheets("String").Activate
ActiveSheet.Range("D8").Select
Case "List"
Sheets("List").Activate
ActiveSheet.Range("D8").Select
End Select
End Sub

[mdmackillop]

A common situation


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count>1 Then Exit Sub
If Intersect(Target, Range("D81000")) Is Nothing Then Exit Sub