Macro question
 

I need to build a macro that will take the user to another worksheet when a
specific value is entered into a cell. Any suggestions?
Thanks,
ew

Macro question
 

Try this in the code for sheet 1. Right Click the Tab. View Code. Paste this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets("Sheet2").Select
End Sub

Changes made to Cell A1 take you to sheet 2...

HTH

"ew" wrote:

I need to build a macro that will take the user to another worksheet when a
specific value is entered into a cell. Any suggestions?
Thanks,
ew

Macro question
 

So if you the sheet change is supposed to happen when a specific value
is entered, then the following will work:
Right click on the Sheet you want the cell value stuff to be done on
and Click "View Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "xxx" Then
Sheets("Sheet2").Select
End If
End Sub

xxx is my sample specific value, and sheeet 2 is the the worksheet name
of the page it will switch to when "xxx" is entered into a cell.



But if you would rather it be done when the value of a specific cell is
changed that can be done too.

Macro question
 

Jim's answer does the option I mentioned, it is called when the value
in a specific cell is changed (in his A1), while mine is called when
the value of any cell is changed to "xxx"...you choose which way you
want it.

Macro question
 

very cool. Can it be built so that only certain values entered into the A1
cell will point it to the other worksheet?
ew

"Jim Thomlinson" wrote:

Try this in the code for sheet 1. Right Click the Tab. View Code. Paste this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets("Sheet2").Select
End Sub

Changes made to Cell A1 take you to sheet 2...

HTH

"ew" wrote:

I need to build a macro that will take the user to another worksheet when a
specific value is entered into a cell. Any suggestions?
Thanks,
ew

Macro question
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" and target.value = "XXX" Then
Sheets("Sheet2").Select
End Sub

Here is Chips and my solution all together... A specific value in a specific
cell...

HTH
"Jim Thomlinson" wrote:

Try this in the code for sheet 1. Right Click the Tab. View Code. Paste this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets("Sheet2").Select
End Sub

Changes made to Cell A1 take you to sheet 2...

HTH

"ew" wrote:

I need to build a macro that will take the user to another worksheet when a
specific value is entered into a cell. Any suggestions?
Thanks,
ew

Macro question
 

In your woksheets code, you need to check the value entered into your target
cell using the change event:
Example:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim chkAddress As Boolean
Dim tVal As Boolean

tVal = Target.Value 20
chkAddress = Target.Address = "$A$1"

If chkAddress And tVal Then
Worksheets("Sheet2").Activate
End If
End Sub


"ew" wrote in message
...
I need to build a macro that will take the user to another worksheet when
a
specific value is entered into a cell. Any suggestions?
Thanks,
ew

Macro question
 

Yep. ew check out Jim's solution. You may also want to add some
error-trapping though because sometimes I get errors when I try to
delete values/or autofill cells.

So here it is all together:


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto Errortrap
If Target.Address = "$A$1" and target.value = "XXX" Then
Sheets("Sheet2").Select
End IF
Errortrap:
Exit Sub
End Sub

Here is Chips

Macro question
 

some good answers. Here's something a little different.
Assume that you have a number of cells that you want to trap entered valued
for, and that these values may change.
on sheet1 select a range for input and name it InputCells
select another range and call this TestValues and put some numbers in here

add this code to the sheet's code page:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim found As Range
Set found = Intersect(Range("inputcells"), Target)
If Not found Is Nothing Then
Set found = Range("TestValues").Find(Target.Value)
If Not found Is Nothing Then
MsgBox "OK"
'worksheets("Sheet2").Activate
End If
End If
End Sub

This will fire OK if any value entered into any of the Input cells matches
any value in the testvalues table.

The following code assunes a range of inputcells as before, but each cell
to its left is the test value for any of the input cells...this then will
fire if the value enetered into an input cell matches th evalue in the cell
to its left

Private Sub Worksheet_Change(ByVal Target As Range)

Dim found As Range
Set found = Intersect(Range("inputcells"), Target)

If Not found Is Nothing Then
If found.Offset(0, -1).Value = Target.Value Then
MsgBox "OK"
'worksheets("Sheet2").Activate
End If
End If

End Sub

The objectiove here is to demonstrate possibilities

Patrick Molloy
Microsoft Excel MVP

"ew" wrote:

I need to build a macro that will take the user to another worksheet when a
specific value is entered into a cell. Any suggestions?
Thanks,
ew