Pause a macro to allow editing of a worksheet

  • Thread starter Thread starter captbluefin
  • Start date Start date
C

captbluefin

Hello All,

The problem: I want to pause a macro to allow editing of a few cells on
a worksheet.

How do I stop the macro to allow me to work on the currently selected
sheet, and then have the macro continue when I am finished entering
data???


Any help would be greatly appreciated!!!!!
 
Captain,

Either use two macros, one for before and one for after editing, or
incorporate your editing into the macro by setting cell values equal
to the returned value from an inputbox, or you could loop through a
section of code that shows a userform with a refedit and a textbox to
get input and assign it a user-selected cell.

HTH,
Bernie
MS Excel MVP

captbluefin said:
Hello All,

The problem: I want to pause a macro to allow editing of a few cells on
a worksheet.

How do I stop the macro to allow me to work on the currently selected
sheet, and then have the macro continue when I am finished entering
data???


Any help would be greatly appreciated!!!!!


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
By using the ontime method you can pause your macro.
what i did was if I selected cells A1 then my macro put the word "Hello" in
the range, then it paused for 20 seconds. I typed in other cells, then when
the macro resumed it called a module to change the range to the word
"Worked". Of course you could just end your macro at the point you need to
enter your data then resume it afterwards. The problem with using my method
is that if you don't have the pause time correct then it will resume before
you are done entering your data. But this will give you an option.

Dim rng As Range
Set rng = Range("a2:c6")
If ActiveCell = Cells(1, 1) Then
rng.Value = "Hello"
Application.OnTime Now + TimeValue("00:00:20"), "enable_resume"
End If

Private Sub enable_resume()
Dim rng As Range
Set rng = Range("A2:C6")
rng.Value = "Worked"
End Sub

HTH
captbluefin said:
Hello All,

The problem: I want to pause a macro to allow editing of a few cells on
a worksheet.

How do I stop the macro to allow me to work on the currently selected
sheet, and then have the macro continue when I am finished entering
data???


Any help would be greatly appreciated!!!!!


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top