Automate moving to different cells in Excel 2000

jesse
have a look at this post - it's a bit of overkill but it's reasonably straightforward and will get you the result.
18855.html - it's the top post on archive 2 of this page. it describes a way to go to any cell you want on pressing enter by using cell protection. the only catch is that it gets a bit clumsy if you frequently have to change the cells you jump to.
if you get stuck just holler.

Anno,

Thanks for your reply.

I tried that but when I hit the tab key, the cursor goes to any tab that isn't protected. Can I make the cursor go to a specific cell?

Thanks,
Jesse

Put this in the worksheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then [A6].Select
End Sub

jesse
i think the instructions in the post i sent you to were a bit confusing. i’ll try to clarify.
1. select the cells you want to jump to, holding down the ctrl key to select multiple cells
2. with these cells selected, go to the format menu, protection tab and uncheck the ‘locked’ box
3. go to the tools menu, protection|protect sheet and enter a password as instructed
Once you’ve done this, when you press enter the cursor should only go to the specific cells you selected and unlocked in step 2. like i said though, the limitation on this is that you'll need to go through this process every time you want to change or add to the cells you want the cursor to jump to.
there may be a better vba solution, perhaps something like what ramon's proposed, but from what i can see with his suggestion you'll still have to modify the vba code if you want to change the cells to jump to. i don't know anything about vba but it's probably possible to write a macro which allows you to select the cells you want through a dialog box or something like that. over to the vba experts.
good luck

What does this do? I created a module and copied the info but when I press enter, nothing happens..

Could you explain the script?

Thanks,
Jesse

jesse
this macro won't run off the enter key. to run it you'll have to go to tools|macro|macros, select the macro name and press the 'run' button. that gets a bit laborious so you could assign a shortcut key or create a button for it to run from on your worksheet. not sure how to do the former with an existing macro but you can create a button from the 'forms' toolbar, which is accessible by placing the mouse arrow on a toolbar and right clicking and selecting 'forms'. the tool for buttons is a grey rectangle. press it and your cursor will change to a thin black cross. click&drag to create the button. once you create it it will prompt you to assign a macro to the button and you'll be away. the only thing is i think you'll need something more than this particular macro to get what you wanted originally.
by the way, was my second post to you of any use? it's the one at 18855.html

No,anno. What you've said is all wrong.
No buttons, run commands, etc. are needed.

The macro I posted has to go in the ThisWorkbook code module - not in a normal module. To go to the ThisWorkbook module, in the VBE select ThisWorkbook in the Project window(on the left) and put the macro in the code module window(on the right).

Correction ......

Sorry, not the ThisWorkbook code module, but the relevant sheet module (select the relevant sheet in the VBE Project window).
Note : you can go to the sheet's code module by right-clicking on the sheet tab and selecting View Code.

fair enough...a vba user i ain't

hi ramon
see - i really don't know anything about vba [apart from creating a macro button ;)]. can you tell me how you get this thing to run though? should it work by just pressing enter?
while i'm asking a few questions, out of curiosity could you design a macro which would do something like i suggested previously - allow jesse to jump to whatever cells he wants, and have him be able to change these cells using a drop down menu or something? the solution i came up with will work but it's a bit clumsy if you need to change the cells to jump to frequently.
thanks

Re: fair enough...a vba user i ain't

The macro is an "event procedure" which will run when a change to any cell is entered by pressing the Enter key or the Tab key or one of the Arrow keys. The macro will make A6 the active cell whenever A1 is the cell that is changed.

Regarding your second query, yes, it would be possible to do a macro that produces a listing from which to select the next cell to be activated. However, I would think that it's probably easier just to manually navigate to and select the desired cell.
It is, of course, possible with VBA to navigate to any predetermined seqence of cells required.
Have a look at "cell_tab_control" at the following for some examples :-
http://geocities.com/aaronblood/pages/vba.html