create a macro that opens, edits then closes cell

  • Thread starter Thread starter Dale Saunders
  • Start date Start date
D

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
 
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.
 
Back
Top