create a macro that opens, edits then closes cell

[Dale Saunders]

I want to create a macro that opens a cell, allows me to edit the cell (with
a pause for input) then close the cell and go down 4 lines. I come from a
Lotus background where I could just make the formula, give it a shortcut key
and perform the task
(<f2><home><right>IF($AG$<pause>=â€â€,â€â€,<enter><down><down><down><end>)

Starting cell contents
=IF($AG$26="W",$AE$26,$AE27)

Ending cell contents after running macro
=IF($AG$24="","",IF($AG$24="W",$AE$24,$AE25))

Some help would be great

Dale Saunders

 

[JLGWhiz]

VBA does not lend itself to pauses for input directly to the worksheet and
then continue with the macro. You can, however, use an InputBox feature or
a contol such as a TextBox to make input to cells on the worksheet while the
macro is running. Below is a brief example of how an input box would work.
Copy this code to your public code module, Alt + F11, and test it. If the
code window is dark, on the VBE menu bar, select Insert>Module, then paste
the code into it. To run the code from Excel, click Tools>Macro>Macros then
select the macro name and click run.

Sub testInput()
'Some code here
Range("A5") = Application.InputBox("Enter a Formula", "INPUT
FORMULA", Type:=0)
Range("A9") = Application.InputBox("Enter a Formula", "INPUT
FORMULA", Type:=0)
'Other code here
End Sub

When entering a formula in the input box, use absolute reference, i.e. $A$1,
or you could create circular references, since the cell references would
otherwise be relative to the cell in which the formula is entered.