Macro or formula

[santaviga]

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

 

[Gary''s Student]

Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Else
Application.Goto reference:=Sheets("Sheet3").Range("A1")
End If
End Sub

 

[Lars-Åke Aspelin]

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

 

[santaviga]

Hi,

I have put this in sheet 3 code area but I cant get it to work when I select
cell A3 on first sheet, first hsheet is called Index of Stock and all other
sheets are only numbered 1,2,3,4,5,6,7,8,9,10 and so on till 150

Any idea what i'm doing wrong?

Regards

 

[Lars-Åke Aspelin]

Try putting the code in Sheet "Index", not in sheet "3"
And replace "Sheet3" with just "3" if that is the name of the sheet to
go to.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Change this to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("3").Activate
End Sub

and remember to put the code in the sheet where you have the "A3"
cell.

Lars-Åke

 

[santaviga]

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?

Regards

 

[santaviga]

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

 

[santaviga]

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?

 

[Lars-Åke Aspelin]

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Sorry, but I don't understand what you mean with "do this through 150
cells to 150 sheets". Could you please explain.

Lars-Åke

 

[santaviga]

Many thanks, works a treat.

Thankyou.

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Ã…ke

 

[santaviga]

Sorry, what I mean is that I have cells A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards

 

[Lars-Åke Aspelin]

You explained in another branch that you wanted sheet 3 to be
activated when you select cell A3 on the first sheet, sheet 4 to
activated when you select cell A4 and so on until cell A50.

Try this code (where I have used 150 rather than 50 as this is what
you state in this branch)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("A3:A150")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

I have given an answer in the branch where you originally asked for
this. In this branch we discuss how to insert todays date.

Lars-Åke

 

[santaviga]

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards

 

[Lars-Åke Aspelin]

See my answer in another branch of this thread.

Lars-Åke

 

[santaviga]

Hi, sorry for not seeing your previous post, found it now and trying, it
works to an extent, when I click on a cell say A1 it opens Sheet 2 and not
sheet 1 as supposed to and son on, it always opens the sheet number above.

Any ideas?

Thanks

 

[santaviga]

Sorry it is opening sheet number below

Hi, sorry for not seeing your previous post, found it now and trying, it
works to an extent, when I click on a cell say A1 it opens Sheet 2 and not
sheet 1 as supposed to and son on, it always opens the sheet number above.

Any ideas?

Thanks

 

[santaviga]

Hi, here is my revised code I am using. It is opening sheet number below cell
number.

Many thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub