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

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

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

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