Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 -- Gary''s Student - gsnu200823 "santaviga" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200823 "santaviga" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 On Mon, 5 Jan 2009 14:32:01 -0800, santaviga wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200823 "santaviga" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin
wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets? Regards "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200823 "santaviga" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date? Regards "Lars-Ã…ke Aspelin" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets? "Lars-Ã…ke Aspelin" wrote: On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Ã…ke Aspelin wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 On Mon, 5 Jan 2009 14:51:01 -0800, santaviga wrote: Working thanks, is there a way so when I click on a cell in a column A it will automatically input todays date? Regards "Lars-Åke Aspelin" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Sorry, but I don't understand what you mean with "do this through 150
cells to 150 sheets". Could you please explain. Lars-Åke On Mon, 5 Jan 2009 15:06:00 -0800, santaviga wrote: Got it working thanks, is there a quick way to do this through 150 cells to 150 sheets? "Lars-Åke Aspelin" wrote: On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Many thanks, works a treat.
Thankyou. "Lars-Ã…ke Aspelin" wrote: 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 On Mon, 5 Jan 2009 14:51:01 -0800, santaviga wrote: Working thanks, is there a way so when I click on a cell in a column A it will automatically input todays date? Regards "Lars-Ã…ke Aspelin" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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" wrote: 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 On Mon, 5 Jan 2009 14:51:01 -0800, santaviga wrote: Working thanks, is there a way so when I click on a cell in a column A it will automatically input todays date? Regards "Lars-Ã…ke Aspelin" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 On Mon, 05 Jan 2009 23:15:27 GMT, Lars-Åke Aspelin wrote: Sorry, but I don't understand what you mean with "do this through 150 cells to 150 sheets". Could you please explain. Lars-Åke On Mon, 5 Jan 2009 15:06:00 -0800, santaviga wrote: Got it working thanks, is there a quick way to do this through 150 cells to 150 sheets? "Lars-Åke Aspelin" wrote: On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 On Tue, 6 Jan 2009 05:38:01 -0800, santaviga wrote: 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" wrote: 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 On Mon, 5 Jan 2009 14:51:01 -0800, santaviga wrote: Working thanks, is there a way so when I click on a cell in a column A it will automatically input todays date? Regards "Lars-Åke Aspelin" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
See my answer in another branch of this thread.
Lars-Åke On Tue, 6 Jan 2009 07:00:12 -0800, santaviga wrote: 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" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 "Lars-Ã…ke Aspelin" wrote: See my answer in another branch of this thread. Lars-Ã…ke On Tue, 6 Jan 2009 07:00:12 -0800, santaviga wrote: 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" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
Sorry it is opening sheet number below
"santaviga" wrote: 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 "Lars-Ã…ke Aspelin" wrote: See my answer in another branch of this thread. Lars-Ã…ke On Tue, 6 Jan 2009 07:00:12 -0800, santaviga wrote: 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" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or formula
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 "Lars-Ã…ke Aspelin" wrote: See my answer in another branch of this thread. Lars-Ã…ke On Tue, 6 Jan 2009 07:00:12 -0800, santaviga wrote: 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" wrote: On Mon, 5 Jan 2009 14:08:03 -0800, santaviga wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use macro to copy formula with value and not formula without value | Excel Programming | |||
Formula to use in Macro | Excel Programming | |||
Is there a Formula or Macro for This? | Excel Discussion (Misc queries) | |||
Formula in macro causes macro to fail | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming |