| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cursor to upper left of frozen cells (in macro)
I have crudely written a macro that takes the cursor to A1 on all worksheets
in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Hi,
Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Application.ActiveWindow.ScrollRow
and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
SendKeys isn't working for me.. Sheeloo, can you give me an example of how
to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Sub ctrlHome()
Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Thanks for the help, but it's still not doing what I want. That does take me
to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
I'm confused.
When you manually hit CTRL + Home which cell becomes active? Should be the top left cell below the "Frozen" area. i.e. rows 1:5 are frozen. CTRL + Home will select A6 Rows 1:5 and columns A:C are frozen. CTRL + Home will select D6 Sheeloo's code does that. Where do you want to go? Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 13:40:01 -0800, Mike wrote: Thanks for the help, but it's still not doing what I want. That does take me to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
When I use Sheeloo's code, it takes me to the top left frozen cell *of the
cells I can currently see on my screen*... Not the top left of the frozen cells as in your example below. Ideally I would like to put a line in the code below that would take the cursor (and thereby the view on the screen) to the top left frozen cell, and then leave the cursor on A1. And repeat for each sheet. The idea is to use this macro before saving and closing a workbook so that when it is opened next, the user sees continuous both down and right starting at A1. Sub Home() Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate End Sub Right now, it just goes to A1, but, for example if colums 1:3 are frozen, the result could be to see colums 1:3 and then 30:50ish if the previous user was working in the lower range. Thanks a lot for the help, Mike "Gord Dibben" wrote: I'm confused. When you manually hit CTRL + Home which cell becomes active? Should be the top left cell below the "Frozen" area. i.e. rows 1:5 are frozen. CTRL + Home will select A6 Rows 1:5 and columns A:C are frozen. CTRL + Home will select D6 Sheeloo's code does that. Where do you want to go? Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 13:40:01 -0800, Mike wrote: Thanks for the help, but it's still not doing what I want. That does take me to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Rows 1:3 are frozen.
User has scrolled down so 1:3 are visible and rows 30:59 are visible You want the view changed from seeing rows 1:3 and rows 30:59 to continuous rows 1:32 ? Application.Goto Range("A1"), Scroll:=True Gord On Thu, 5 Mar 2009 06:27:01 -0800, Mike wrote: When I use Sheeloo's code, it takes me to the top left frozen cell *of the cells I can currently see on my screen*... Not the top left of the frozen cells as in your example below. Ideally I would like to put a line in the code below that would take the cursor (and thereby the view on the screen) to the top left frozen cell, and then leave the cursor on A1. And repeat for each sheet. The idea is to use this macro before saving and closing a workbook so that when it is opened next, the user sees continuous both down and right starting at A1. Sub Home() Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate End Sub Right now, it just goes to A1, but, for example if colums 1:3 are frozen, the result could be to see colums 1:3 and then 30:50ish if the previous user was working in the lower range. Thanks a lot for the help, Mike "Gord Dibben" wrote: I'm confused. When you manually hit CTRL + Home which cell becomes active? Should be the top left cell below the "Frozen" area. i.e. rows 1:5 are frozen. CTRL + Home will select A6 Rows 1:5 and columns A:C are frozen. CTRL + Home will select D6 Sheeloo's code does that. Where do you want to go? Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 13:40:01 -0800, Mike wrote: Thanks for the help, but it's still not doing what I want. That does take me to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Perfect! Thanks so much--y'all are always such a huge help.
"Gord Dibben" wrote: Rows 1:3 are frozen. User has scrolled down so 1:3 are visible and rows 30:59 are visible You want the view changed from seeing rows 1:3 and rows 30:59 to continuous rows 1:32 ? Application.Goto Range("A1"), Scroll:=True Gord On Thu, 5 Mar 2009 06:27:01 -0800, Mike wrote: When I use Sheeloo's code, it takes me to the top left frozen cell *of the cells I can currently see on my screen*... Not the top left of the frozen cells as in your example below. Ideally I would like to put a line in the code below that would take the cursor (and thereby the view on the screen) to the top left frozen cell, and then leave the cursor on A1. And repeat for each sheet. The idea is to use this macro before saving and closing a workbook so that when it is opened next, the user sees continuous both down and right starting at A1. Sub Home() Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate End Sub Right now, it just goes to A1, but, for example if colums 1:3 are frozen, the result could be to see colums 1:3 and then 30:50ish if the previous user was working in the lower range. Thanks a lot for the help, Mike "Gord Dibben" wrote: I'm confused. When you manually hit CTRL + Home which cell becomes active? Should be the top left cell below the "Frozen" area. i.e. rows 1:5 are frozen. CTRL + Home will select A6 Rows 1:5 and columns A:C are frozen. CTRL + Home will select D6 Sheeloo's code does that. Where do you want to go? Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 13:40:01 -0800, Mike wrote: Thanks for the help, but it's still not doing what I want. That does take me to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cursor to upper left of frozen cells (in macro)
Thanks Mike.
Took me a while to find out what you wanted but we got there. Gord On Thu, 5 Mar 2009 14:48:01 -0800, Mike wrote: Perfect! Thanks so much--y'all are always such a huge help. "Gord Dibben" wrote: Rows 1:3 are frozen. User has scrolled down so 1:3 are visible and rows 30:59 are visible You want the view changed from seeing rows 1:3 and rows 30:59 to continuous rows 1:32 ? Application.Goto Range("A1"), Scroll:=True Gord On Thu, 5 Mar 2009 06:27:01 -0800, Mike wrote: When I use Sheeloo's code, it takes me to the top left frozen cell *of the cells I can currently see on my screen*... Not the top left of the frozen cells as in your example below. Ideally I would like to put a line in the code below that would take the cursor (and thereby the view on the screen) to the top left frozen cell, and then leave the cursor on A1. And repeat for each sheet. The idea is to use this macro before saving and closing a workbook so that when it is opened next, the user sees continuous both down and right starting at A1. Sub Home() Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate End Sub Right now, it just goes to A1, but, for example if colums 1:3 are frozen, the result could be to see colums 1:3 and then 30:50ish if the previous user was working in the lower range. Thanks a lot for the help, Mike "Gord Dibben" wrote: I'm confused. When you manually hit CTRL + Home which cell becomes active? Should be the top left cell below the "Frozen" area. i.e. rows 1:5 are frozen. CTRL + Home will select A6 Rows 1:5 and columns A:C are frozen. CTRL + Home will select D6 Sheeloo's code does that. Where do you want to go? Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 13:40:01 -0800, Mike wrote: Thanks for the help, but it's still not doing what I want. That does take me to the top-left cell of the frozen pane, but what I really want is to take the cursor to the first frozen cell (where it would take you if you pressed Ctrl+Home). SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding shift I highlight everything from the "Home" cell to A1... can I fix this? "Sheeloo" wrote: Sub ctrlHome() Cells(Application.ActiveWindow.ScrollRow, _ Application.ActiveWindow.ScrollColumn).Select End Sub will take you to the tol-left cell below the frozen pane... that is what you wanted, right? Basically Application.ActiveWindow.ScrollRow gives you the rowno and Application.ActiveWindow.ScrollColumn gives you the columnno of the first cell... "Mike" wrote: SendKeys isn't working for me.. Sheeloo, can you give me an example of how to use those commands in my code? "Sheeloo" wrote: Application.ActiveWindow.ScrollRow and Application.ActiveWindow.ScrollColumn will give you the row and column numbers which you can use in your code... "Shane Devenshire" wrote: Hi, Look at the sendkeys command, here is some code: SendKeys "^{Home}" -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote: I have crudely written a macro that takes the cursor to A1 on all worksheets in a workbook. I'm hoping to add a command that will send the curso to the upper left of any frozen cells first, then go to A1 (basically I want it to perform Ctrl-Home first). Does anyone have any advice on this? Below is the code I am currently using. Dim wk As Worksheet For Each wk In ActiveWorkbook.Worksheets wk.Activate Range("A1").Select Next wk Sheets(1).Activate Thanks for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cursor movement frozen | Excel Discussion (Misc queries) | |||
| Third left part of the screen is frozen | Excel Discussion (Misc queries) | |||
| I have arrowheads in upper left of some cells. How can I remove? | Excel Discussion (Misc queries) | |||
| Remove green mark upper left several cells Excel? | Excel Worksheet Functions | |||
| How to change location A1 cell from upper right to upper left? | Excel Discussion (Misc queries) | |||
Linear Mode
