PDA

View Full Version : Is there a home command?



macro_man
03-10-2011, 08:36 AM
This probably seems like a trivial question but need a command that can be applied to all files:

Is there a VBS command that will place the cursor at the "home" position? This position may change depending on the spreadsheet and if there are title locks in place.

Thanks.

Bob Phillips
03-10-2011, 08:46 AM
With ActiveWindow
ActiveSheet.Cells(.ScrollRow, .ScrollColumn).Select
End With

macro_man
03-10-2011, 08:51 AM
With ActiveWindow
ActiveSheet.Cells(.ScrollRow, .ScrollColumn).Select
End With


Thank you. It works perfectly.

Allen

macro_man
03-10-2011, 08:56 AM
With ActiveWindow
ActiveSheet.Cells(.ScrollRow, .ScrollColumn).Select
End With


I spoke (typed) too soon. I tried this when the cursor was down a few pages in my spreadsheet and it did nothing. It appears to only work when the "home" cell is on-screen.

Allen

macro_man
03-10-2011, 09:14 AM
I received this suggestion for a "home" key that would take me to the top left cell of a spreadsheet (not always A1). This was on a previous posting that I had marked as solved, but when I tested it further I discovered that if the cursor was scrolled down a page or more it would not work. It only works when the home key is on screen (in view). I appreciate the suggestion. Is there a way to improve this given this discovery?




With ActiveWindow
ActiveSheet.Cells(.ScrollRow, .ScrollColumn).Select
End With

Kenneth Hobs
03-10-2011, 11:49 AM
I don't why "home" would not be a1.

[A1].Select

macro_man
03-10-2011, 12:12 PM
I don't why "home" would not be a1.

[A1].Select

Because if you have a spreadsheet with title locks set up home might be A5 or if you have hidden rows home might be A500.

Allen

Kenneth Hobs
03-10-2011, 12:44 PM
Cells(ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Row, _
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Column).Select

macro_man
03-10-2011, 02:19 PM
Cells(ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Row, _
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Column).Select


Thanks but this does not work with a spreadsheet that has title-locks. I tried it with one that has title-locks at cell F4. The macro put the cursor at A1 (not home in this case).

Thanks anyway.

Allen

Aussiebear
03-10-2011, 03:38 PM
Which cell is "Home"?

p45cal
03-10-2011, 04:23 PM
SendKeys "^{HOME}", True?

Kenneth Hobs
03-10-2011, 04:24 PM
If you code it right, you should know which is the freeze pane key.

Turn off UAC if you have Vista or Win7, and use this as p45cal said though I tend to avoid SendKeys() when I can.
SendKeys "^{Home}", True

macro_man
03-10-2011, 06:54 PM
Which cell is "Home"?
The definition of "home" regarding spreadsheets (going way back to Lotus 123) is the top-most left cell (up to the title locks) of a spreadsheet.

If a spreadsheet has a title lock at F4 (locking titles at columns A-E and rows 1-3) the home cell is F4.

Allen

macro_man
03-10-2011, 07:02 PM
SendKeys "^{HOME}", True?

Perhaps I am not familiar with this but putting this into the VBS editor and running it yielded a cursor that moves to the left-most cell of any area of a spreadsheet. I put a title lock at F4 scrolled down several pages and executed the macro. The cursor went to F397, the same row but only as far to the left as possible to the title lock.

Allen

Kenneth Hobs
03-10-2011, 09:35 PM
The manner of running a macro makes a difference. The reason why Sendkeys() fails are usually one of two. (1) Focus and (2) timing. So, if you run a macro from the VBE, the focus is in the VBE. Run the macro as you would during production.

IF it still fails, then you most likely have UAC on.

Rob342
03-11-2011, 04:42 AM
Hi macro_man

All depends where Home is ? haven't sent a menu item called Home

I always set "Home" as ("A1")

Code

Range("A1").Select

macro_man
03-11-2011, 07:21 AM
Hi macro_man

All depends where Home is ? haven't sent a menu item called Home

I always set "Home" as ("A1")

Code

Range("A1").Select


Thanks for the suggestion but please read the previous posts. Actually, there is much more on the original thread (the one I mistakingly marked as solved and so started this new one - caused more confusion than anything). Thanks anyway.
Allen

Frosty
03-11-2011, 09:18 AM
1) suggesting someone trying to help you read a different thread without even putting in a link is kind of tough to follow... maybe you should sum up your needs in this thread, rather than expecting people to jump around :)

2) why don't you just combine the two things. Select A1 first, then do the other action... You're saying that if the "home key" isn't in view, then the one line of code doesn't work? So... put it in view.

I'm not an excel guy... but I thought I'd help you define your problem a little better, so that others may help you. I don't think you meant to come off as rude... but it did.

macro_man
03-11-2011, 09:27 AM
The manner of running a macro makes a difference. The reason why Sendkeys() fails are usually one of two. (1) Focus and (2) timing. So, if you run a macro from the VBE, the focus is in the VBE. Run the macro as you would during production.

IF it still fails, then you most likely have UAC on.

I forgot to mention - I'm using Windows XP. I don't have a clue of what UAC is or where it can be turned on/off.

GTO
03-11-2011, 09:29 AM
1) suggesting someone trying to help you read a different thread without even putting in a link is kind of tough to follow... maybe you should sum up your needs in this thread, rather than expecting people to jump around :)

LOL :-)

Allen,

This is a guess, but if you are trying to get a certain cell to be at the upper/left of the window, look at Application.GoTo

Hope that helps,

Mark

macro_man
03-11-2011, 09:43 AM
1) suggesting someone trying to help you read a different thread without even putting in a link is kind of tough to follow... maybe you should sum up your needs in this thread, rather than expecting people to jump around :)

2) why don't you just combine the two things. Select A1 first, then do the other action... You're saying that if the "home key" isn't in view, then the one line of code doesn't work? So... put it in view.

I'm not an excel guy... but I thought I'd help you define your problem a little better, so that others may help you. I don't think you meant to come off as rude... but it did.

Sorry if I came across as rude. I certainly didn't intend to. Here are my responses:

1) You were correct. I should have put a link in my post. As mentioned I had prematurely marked it as solved without testing it in all situations and so assumed that it would not be looked at anymore. I was surprised that many more looked at the old "solved" thread. I was frustrated that I couldn't find a way to "unsolve" it and continue with the original.

2) I'm not sure what you mean. A1 is not always home. I simply want a command that I can put within a larger macro that will reposition the cursor in the top-most left column (the definition of "home"), ready to use the next time it is opened. A1 is not always home because of hidden rows and especially, title locks. The desired macro command will be stored an my Personal.xlsm file, meaning it needs to be applicable to all spreadsheets that are open.

Thanks for you reply.

Here is the link.

http://www.vbaexpress.com/forum/showthread.php?t=36504

Allen

p45cal
03-11-2011, 10:12 AM
I forgot to mention - I'm using Windows XP. I don't have a clue of what UAC is or where it can be turned on/off. Well, you don't have to worry since
Turn off UAC if you have Vista or Win7you don't have either OS.
To test it with the sheet having the focus, run it from the Excel Menus or perhaps assign a shortcut/hotkey to it.

macro_man
03-11-2011, 10:26 AM
Well, you don't have to worry since you don't have either OS.
To test it with the sheet having the focus, run it from the Excel Menus or perhaps assign a shortcut/hotkey to it.

Thanks. I've heard of the word focus being used in this context before but have not found a definition anywhere (even in Excel 2007 Power Programing with VBA). Please elaborate.

Allen

p45cal
03-11-2011, 10:50 AM
call it the active window instead - make Excel the active window, not the visual basic editor.