|
|
|
|
|
|
|
|
|
Excel
|
Simplify data entry to restricted columns or rows.
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000,2003
|
|
Submitted by:
|
mdmackillop
|
|
Description:
|
The code limits data entry to selected adjoining columns, rows or a block of cells. By choosing the appropriate MoveAfterEnter section, the next cell will be selected automatically.
Toggle on/off as required.
|
|
Discussion:
|
If you need to create data to two or three adjoining columns or rows, this simple macro limits the cell selection to the desired area.
For a hard coded method which permits multiple data entry regions the method here can be used.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=346
|
|
Code:
|
instructions for use
|
Sub SetScroll()
If Selection.Cells.Count <> 1 Then
ActiveSheet.ScrollArea = Selection.Address
Else
ActiveSheet.ScrollArea = ""
End If
End Sub
|
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Create a button or shortcut and assign it to SetScroll
- Save workbook before any other changes
|
|
Test the code:
|
- Select a range of cells, typically two or three columns
- Click on your button or run your shortcut
- Set Tools/Options/Edit/MoveAfterEnter to suit your data ebtry
- Select a cell in the range and start to enter your data (followed by Enter; not TAB)
- To clear the selection, click on the Button or run the shortcut while a single cell is selected.
|
|
Sample File:
|
ScrollArea.zip 7.11KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 427 times.
|
|
|