Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

Frank from Holland posed today's question: Hello, I want to make a sort of database in Excel. Therefore, I need to find a Macro or something like that, with which I can create a command that makes the cursor jump from (for example) A16 to B2 after using the enter-button on A16. Would you know the solution for this problem? Thanks!

One easy way to achieve this is to make A16 and B2 the only unlocked cells on the worksheet. By default, all cells are locked. You can use Format - Cells - Protection - uncheck the Locked checkbox to unlock the cells.

In Excel 2000 and earlier you can simply protect the worksheet with Tools - Protection - Protect Sheet. In Excel 2002 you will have to use Tools - Protection - Protect Sheet and then uncheck the option for "Select locked cells".

After the sheet is successfully protected, hitting enter in one unprotected cell will take you to the next unprotected cell.

If protecting the worksheet is not practical, you would have to resort to a Worksheet_Change macro that sensed which cell just changed. For information on how to enter worksheet event handler macros, see tip055

Here is a sample macro:


Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$2"
Range("A16").Select
Case "$A$16"
Range("B2").Select
End Select
End Sub

By Bill Jelen on 12-Oct-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.