Finding CellRef for Control+Home
 

Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

You just got me doubting my own name with that question.

Isn't it just sh.range("A1") or can it be something other than A1

therefore sh.range("A1").address

or even sh.range("A1").address(external:=true)

etc

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=547108

Finding CellRef for Control+Home
 

Without wishing to appear foolish, doesn't ctrl-home always go to A1?


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

If you used window|freeze panes, it may not.

Jeff Standen wrote:

Without wishing to appear foolish, doesn't ctrl-home always go to A1?

"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

--

Dave Peterson

Finding CellRef for Control+Home
 

So I failed to indicate that in most sheets someone has
performed the Windows - Freeze to where the first 10 rows and first 2
columns are locked (frozen) - and each page (sheet) might have a different RC
setting.

"tony h" wrote:


You just got me doubting my own name with that question.

Isn't it just sh.range("A1") or can it be something other than A1

therefore sh.range("A1").address

or even sh.range("A1").address(external:=true)

etc

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=547108

Finding CellRef for Control+Home
 

Not if you have previously utilized the Window - Freeze setup..
sorry failed to mention that this is the case in all 100 sheets (each with a
different RC setting..


"Jeff Standen" wrote:

Without wishing to appear foolish, doesn't ctrl-home always go to A1?


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

Ahhhh.

After a little experimentation, this would seem to work:

Activewindow.Panes.Item(Activewindow.Panes.count). VisibleRange.cells(1).Address

assuming the window is in fact active. Not sure how to reference an inactive
window.

"Jim May" wrote in message
...
Not if you have previously utilized the Window - Freeze setup..
sorry failed to mention that this is the case in all 100 sheets (each with
a
different RC setting..


"Jeff Standen" wrote:

Without wishing to appear foolish, doesn't ctrl-home always go to A1?


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

?
Activewindow.Panes.Item(Activewindow.Panes.count). VisibleRange.cells(1).Addr
ess
$Q$18


Doesn't work for me. Should have been B5. You solution depends on what the
visible range is.

--
Regards,
Tom Ogilvy

"Jeff Standen" wrote in message
...
Ahhhh.

After a little experimentation, this would seem to work:


Activewindow.Panes.Item(Activewindow.Panes.count). VisibleRange.cells(1).Addr
ess

assuming the window is in fact active. Not sure how to reference an
inactive
window.

"Jim May" wrote in message
...
Not if you have previously utilized the Window - Freeze setup..
sorry failed to mention that this is the case in all 100 sheets (each
with
a
different RC setting..


"Jeff Standen" wrote:

Without wishing to appear foolish, doesn't ctrl-home always go to A1?


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

Perhaps your real desire is to do the equivalent of Ctrl+Home

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'CtrlHome
Dim rng as Range
Application.Goto Range("A1"), True
set rng = ActiveWindow.VisibleRange(1, 1)
rng.Select
End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

But I was close :)

Jeff

"Tom Ogilvy" wrote in message
...
?
Activewindow.Panes.Item(Activewindow.Panes.count). VisibleRange.cells(1).Addr
ess
$Q$18


Doesn't work for me. Should have been B5. You solution depends on what
the
visible range is.

--
Regards,
Tom Ogilvy

"Jeff Standen" wrote in message
...
Ahhhh.

After a little experimentation, this would seem to work:


Activewindow.Panes.Item(Activewindow.Panes.count). VisibleRange.cells(1).Addr
ess

assuming the window is in fact active. Not sure how to reference an
inactive
window.

"Jim May" wrote in message
...
Not if you have previously utilized the Window - Freeze setup..
sorry failed to mention that this is the case in all 100 sheets (each
with
a
different RC setting..


"Jeff Standen" wrote:

Without wishing to appear foolish, doesn't ctrl-home always go to A1?


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

Thanks Tom;
I've got "this part, that is the Control+Home" now working;
Only LAST problem is when I select a ws from my combobox -- the focus
remain on/in the cb, versus "jumping to" the worksheet just activated.
See any "Flaws" with what I'm using (below)?

Private Sub ComboBox1_Change()
Myws = ComboBox1.Value
Worksheets(Myws).Activate
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub



"Tom Ogilvy" wrote:

Perhaps your real desire is to do the equivalent of Ctrl+Home

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'CtrlHome
Dim rng as Range
Application.Goto Range("A1"), True
set rng = ActiveWindow.VisibleRange(1, 1)
rng.Select
End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

Is the Userform Modeless or Modal. If modal, you will need to drop the
userform. If modeless, try adding

Private Sub ComboBox1_Change()
Myws = ComboBox1.Value
Worksheets(Myws).Activate
AppActivate Application.Caption
End Sub


--
Regards,
Tom Ogilvy

"Jim May" wrote in message
...
Thanks Tom;
I've got "this part, that is the Control+Home" now working;
Only LAST problem is when I select a ws from my combobox -- the focus
remain on/in the cb, versus "jumping to" the worksheet just activated.
See any "Flaws" with what I'm using (below)?

Private Sub ComboBox1_Change()
Myws = ComboBox1.Value
Worksheets(Myws).Activate
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub



"Tom Ogilvy" wrote:

Perhaps your real desire is to do the equivalent of Ctrl+Home

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'CtrlHome
Dim rng as Range
Application.Goto Range("A1"), True
set rng = ActiveWindow.VisibleRange(1, 1)
rng.Select
End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance

Finding CellRef for Control+Home
 

Tom:
My Userform is Modeless so AppActivate Application.Caption did the trick!!
Thanks a million... hummmm I've gonna have to quite saying that -- I already
owe you well over a 100 million LOL,,,
Jim May

"Tom Ogilvy" wrote:

Is the Userform Modeless or Modal. If modal, you will need to drop the
userform. If modeless, try adding

Private Sub ComboBox1_Change()
Myws = ComboBox1.Value
Worksheets(Myws).Activate
AppActivate Application.Caption
End Sub


--
Regards,
Tom Ogilvy

"Jim May" wrote in message
...
Thanks Tom;
I've got "this part, that is the Control+Home" now working;
Only LAST problem is when I select a ws from my combobox -- the focus
remain on/in the cb, versus "jumping to" the worksheet just activated.
See any "Flaws" with what I'm using (below)?

Private Sub ComboBox1_Change()
Myws = ComboBox1.Value
Worksheets(Myws).Activate
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub



"Tom Ogilvy" wrote:

Perhaps your real desire is to do the equivalent of Ctrl+Home

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'CtrlHome
Dim rng as Range
Application.Goto Range("A1"), True
set rng = ActiveWindow.VisibleRange(1, 1)
rng.Select
End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Is there a way I can determine the .address of the cell that is the
result of the Control+Home on any sheet that I might activate (and
I have over 100 sheets)?
I'd like to incorporate it into a
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' macro

Tks in Advance