Finding CellRef for Control+Home

[Jim May]

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

[tony h]

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

[Jeff Standen]

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


"Jim May" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

[Jim May]

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
>
>

[Jim May]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Jeff Standen]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Tom Ogilvy]

?
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" <[email protected]> wrote in message
news:O%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Jeff Standen]

But I was close

Jeff

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> ?
> 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" <[email protected]> wrote in message
> news:O%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>
>

[Jim May]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Jim May]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>