Excel macro which prompts for input and moves to a cell - repeated
 

To: All pundits in Microsoft Excel
From: Ken Spencer in Zimbabwe (Email: )
Subject: Kindly requests help on Microsoft Excel.
Date: 8 September 2008

In Lotus 123, (which I have long since stopped using) I used a simple Macro
to:
1. Go to a cell
2. Get a prompt to enter a value, e.g. €œEnter €˜Month of Year€�
3. Enter the value and press Enter to move to another cell.
4. Get another prompt to enter another value, e.g. €œEnter €˜Sales Volume for
the Month€�
5. Enter the value and press Enter to move to another cell
6. Etc Etc.

Can anybody out there please tell me how I can do this in Excel by recording
keystrokes in a Macro? Please note I havent the first clue about Microsoft
Visual Basic.

Thanks in advance

Kindest regards

Ken Spencer

Excel macro which prompts for input and moves to a cell - repeated
 

Hi,

You could do this

Do
response = Int(Val(InputBox("Enter Month of Year")))
If IsNumeric(response) And response = 1 And response <= 12 Then
Range("A1").Value = response
Else
response = ""
MsgBox ("You must enter a number between 1 & 12")
End If
Loop Until response < ""


This gets a numner between 1 & 12 and puts it in a1. repeat these steps
ammending the validation as necessary.

Mike



"KenZimbo" wrote:

To: All pundits in Microsoft Excel
From: Ken Spencer in Zimbabwe (Email: )
Subject: Kindly requests help on Microsoft Excel.
Date: 8 September 2008

In Lotus 123, (which I have long since stopped using) I used a simple Macro
to:
1. Go to a cell
2. Get a prompt to enter a value, e.g. €œEnter €˜Month of Year€�
3. Enter the value and press Enter to move to another cell.
4. Get another prompt to enter another value, e.g. €œEnter €˜Sales Volume for
the Month€�
5. Enter the value and press Enter to move to another cell
6. Etc Etc.

Can anybody out there please tell me how I can do this in Excel by recording
keystrokes in a Macro? Please note I havent the first clue about Microsoft
Visual Basic.

Thanks in advance

Kindest regards

Ken Spencer

Excel macro which prompts for input and moves to a cell - repeated
 

Many ways. Here is one idea. You could also unlock the cells to gotoput a
comment in eachprotect the sheet.

Sub enterdatainselectedcells()
q1 = InputBox("enterdata1")
If q1 < "" Then Application.Goto Range("b2")
q2 = InputBox("enterdata1")
If q2 < "" Then Application.Goto Range("b12")
'etc
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KenZimbo" wrote in message
...
To: All pundits in Microsoft Excel
From: Ken Spencer in Zimbabwe (Email: )
Subject: Kindly requests help on Microsoft Excel.
Date: 8 September 2008

In Lotus 123, (which I have long since stopped using) I used a simple
Macro
to:
1. Go to a cell
2. Get a prompt to enter a value, e.g. €œEnter €˜Month of Year€�
3. Enter the value and press Enter to move to another cell.
4. Get another prompt to enter another value, e.g. €œEnter €˜Sales Volume
for
the Month€�
5. Enter the value and press Enter to move to another cell
6. Etc Etc.

Can anybody out there please tell me how I can do this in Excel by
recording
keystrokes in a Macro? Please note I havent the first clue about
Microsoft
Visual Basic.

Thanks in advance

Kindest regards

Ken Spencer