Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jump to cell in other worksheet
Hello,
In my worksheet Sheet1 I have a cell containing the string "Sheet2!Cells(10,10)". How can I jump to the cell and make changes to it? Thanks in advance, Roel van der Heide |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jump to cell in other worksheet
Assume your string is in cell A1 of Sheet1.
In the code module for sheet1 Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim sStr As String, iloc As Long, sName Dim sRow As String, sCol As String If Target.Count 1 Then Exit Sub If Target.Address < "$A$1" Then Exit Sub sStr = Range("A1").Value iloc = InStr(sStr, "!") sName = Left(sStr, iloc - 1) sCell = Right(sStr, Len(sStr) - iloc) iloc = InStr(sCell, ",") sRow = Mid(sCell, 7, iloc - 7) sCol = Mid(sCell, iloc + 1, Len(sCell) - iloc - 1) Worksheets(sName).Activate Worksheets(sName).Cells(CLng(sRow), CLng(sCol)).Select End Sub You can add some error checking. -- Regards, Tom Ogilvy "Roel van der Heide" wrote in message om... Hello, In my worksheet Sheet1 I have a cell containing the string "Sheet2!Cells(10,10)". How can I jump to the cell and make changes to it? Thanks in advance, Roel van der Heide |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jump to cell in other worksheet
Tom,
That's very kind of you. This is the way I used to program in the old Basic days. I had intentionally coded the string name of my "anchor cell" this way, thinking VBA would have some nice way jumping to the cell, but then I couldn't find it anywhere. I should have coded it differently, but then: how? I.e.: how do you keep hold of an "anchor cell" in a different sheet when you want to be able to jump back to the "anchor cell" at any time? Please note, the "anchor cell" does not have a fixed "A1"-type location but has variable (row, column)-co-ordinates. To make the problem more clear: I start with a cell on a summary sheet which is colour coded to represent various stages. There will be a sequence of macro's calling each other, each sending ranges of cells to other sheets. Depending on the status, I want every other sheet to be able to change the colour of my "anchor cell" according to changes in the status. I included the co-ordinates of my "anchor cell" in every range upon copying the range. I know how to do the colour part. I have programmed a lot but I'm relatively new to OOP and VBA. I like it very much but then it seems some problems still have to be solved the legacy way. Thanks a lot anyway, Roel van der Heide "Tom Ogilvy" schreef in bericht ... Assume your string is in cell A1 of Sheet1. In the code module for sheet1 Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim sStr As String, iloc As Long, sName Dim sRow As String, sCol As String If Target.Count 1 Then Exit Sub If Target.Address < "$A$1" Then Exit Sub sStr = Range("A1").Value iloc = InStr(sStr, "!") sName = Left(sStr, iloc - 1) sCell = Right(sStr, Len(sStr) - iloc) iloc = InStr(sCell, ",") sRow = Mid(sCell, 7, iloc - 7) sCol = Mid(sCell, iloc + 1, Len(sCell) - iloc - 1) Worksheets(sName).Activate Worksheets(sName).Cells(CLng(sRow), CLng(sCol)).Select End Sub You can add some error checking. -- Regards, Tom Ogilvy "Roel van der Heide" wrote in message om... Hello, In my worksheet Sheet1 I have a cell containing the string "Sheet2!Cells(10,10)". How can I jump to the cell and make changes to it? Thanks in advance, Roel van der Heide |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jump to cell in other worksheet
Any way you want to put the address of the anchor cell should work. You
just have to write the code to parse it. You might want to look at the Hyperlink Worksheet function. Perhaps you can then do what you want with no programming. -- Regards, Tom Ogilvy "Roel van der Heide" wrote in message ... Tom, That's very kind of you. This is the way I used to program in the old Basic days. I had intentionally coded the string name of my "anchor cell" this way, thinking VBA would have some nice way jumping to the cell, but then I couldn't find it anywhere. I should have coded it differently, but then: how? I.e.: how do you keep hold of an "anchor cell" in a different sheet when you want to be able to jump back to the "anchor cell" at any time? Please note, the "anchor cell" does not have a fixed "A1"-type location but has variable (row, column)-co-ordinates. To make the problem more clear: I start with a cell on a summary sheet which is colour coded to represent various stages. There will be a sequence of macro's calling each other, each sending ranges of cells to other sheets. Depending on the status, I want every other sheet to be able to change the colour of my "anchor cell" according to changes in the status. I included the co-ordinates of my "anchor cell" in every range upon copying the range. I know how to do the colour part. I have programmed a lot but I'm relatively new to OOP and VBA. I like it very much but then it seems some problems still have to be solved the legacy way. Thanks a lot anyway, Roel van der Heide "Tom Ogilvy" schreef in bericht ... Assume your string is in cell A1 of Sheet1. In the code module for sheet1 Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim sStr As String, iloc As Long, sName Dim sRow As String, sCol As String If Target.Count 1 Then Exit Sub If Target.Address < "$A$1" Then Exit Sub sStr = Range("A1").Value iloc = InStr(sStr, "!") sName = Left(sStr, iloc - 1) sCell = Right(sStr, Len(sStr) - iloc) iloc = InStr(sCell, ",") sRow = Mid(sCell, 7, iloc - 7) sCol = Mid(sCell, iloc + 1, Len(sCell) - iloc - 1) Worksheets(sName).Activate Worksheets(sName).Cells(CLng(sRow), CLng(sCol)).Select End Sub You can add some error checking. -- Regards, Tom Ogilvy "Roel van der Heide" wrote in message om... Hello, In my worksheet Sheet1 I have a cell containing the string "Sheet2!Cells(10,10)". How can I jump to the cell and make changes to it? Thanks in advance, Roel van der Heide |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Jump | Excel Worksheet Functions | |||
Can I jump to a cell in another worksheet | Excel Worksheet Functions | |||
Jump to link worksheet | Excel Worksheet Functions | |||
I want to jump to a worksheet from a contents sheet | Excel Worksheet Functions | |||
can i link a cell to jump to a worksheet? | Excel Programming |