Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make Control-Home go immediately to A1? | Excel Discussion (Misc queries) | |||
Control+Home, Not working | Setting up and Configuration of Excel | |||
Control + Home to get to A1 cell | Excel Discussion (Misc queries) | |||
all tabs control home | Excel Discussion (Misc queries) | |||
Shortcut keys: CNTRL+HOME vs. HOME | Excel Discussion (Misc queries) |