Pause for user input

[DaveS]

I am able to use a pop-up dialogue box for user input,

Range("B12").Select
Qty1 = InputBox("Enter Qty 1", "Qty 1", "", 1, 1)
ActiveCell.Value = Qty1

But can I simply run a macro that goes to B12, pauses for
user input, once they enter, it goes to D12, pauses for
user input... etc. etc. without a dialogue box?

 

[Frank Kabel]

Hi
not sure why you're trying to do this with a macro. What are you trying
to achieve?
You may consider using event procedures (worksheet_change for example).
See http://www.cpearson.com/excel/events.htm for more details

 

[DaveS]

I just need to pop to many different pre-defined areas of
the worsheet and have users enter data.

-----Original Message-----
Hi
not sure why you're trying to do this with a macro. What are you trying
to achieve?
You may consider using event procedures

(worksheet_change for example).

 

[Gord Dibben]

Dave

To TAB to desired cells.......

1. Unlock the cells you want to TAB to then protect the worksheet.

If your unlocked cells are in a left to right, top to bottom series, the TAB
key will move you through them as long as Sheet Protection is enabled.

2. If not in this configuration.....you can do it with a Named Range and no
sheet protection.

Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.

Select the Second cell(B2) you want in the range then CRTL + click your way
through the range in the order you wish, ending with the First cell(A1). Name
this range under Insert>Name>Define>OK.

Now click on NameBox(top left corner above row 1 and col A), select the range
name to highlight the range. With these cells selected, you can input data
and Tab or Enter your way through the range in the order you selected.

Note: there is a limit of about 25 - 30 cells to a range using this method due
to a 255 character limit in a named range. Longer sheet names will reduce the
number of cells considerably.

If more needed, you can enter them manually in thr "refers to" box.

From Debra Dalgleish.....

The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3

There is a third method which requires VBA and a Worksheet_Change event.

''moves from C2 through E5 at entry
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
End Select
End Sub

Gord Dibben Excel MVP

 

[Guest]

Gord,

This looks like what I am wanting to do:

"There is a third method which requires VBA and a
Worksheet_Change event."


A newbie to VB, how do I "call" this Worksheet_Change
event? I tried pasting the code in, but that is obviously
not the answer.

Thanks for your time.

Dave

 

[Frank Kabel]

Hi Dave
have a look at
http://www.cpearson.com/excel/events.htm
for more information about event procedures

 

[Gord Dibben]

Dave

You don't "call" it. It is Event code and operates on changes.

With your workbook open and worksheet visible, Right-click on the Worksheet
tab and "View Code". Paste the code in there, not into a General Module.

Adapt the code to suit your sequence. B12, D12 and onward.

NOTE: no particular order as in "left to right, top to bottom" is required.

When you type in the data then hit <ENTER> key the focus will jump to the next
cell in the sequence.

In the example code start in C2, type in data and hit <ENTER> to go to C5 etc.

Gord

 

[Dave]

Gord,

THANK YOU. This is perfect. I have done a lot with
macros, but never this. This certainly opens the door to
much more control.

Thank you for your time.!

Dave