How to Pause Macro then resume via keystrokes

[cruss]

Ladies, Gents,

When the macro is running, it effectively stops the user from selecting any cells, or working within the spreadsheet in any way.

I need the spreadsheet user to be able to pause the running macro via a set of key strokes eg CTRL p for Pause (or other method?).
When the macro is paused, the user can jump into the spreadsheet and "do their thing" eg select a cell for a title, do a graph and print it etc etc. Then when he or she is ready, resume the macro from where it paused with another set of keystrokes (or other method - button?).

They should be able to do this as many times as they like while the macro is running.

The application is Data acquisition from a measuring device, where there can be an hour or more between 1 reading and the next.

Any help here would be appreciated.

Regards

Chris

 

[oldmanwilly]

This code will do what you want I think, run past the waiting times given in column J and it assumes the results are written in column H.
When the user pauses it will continue where it leftr off once the macro is restarted.

Sub CaptureData()
'
    Dim LastRow As Long, StartRow As Long, x As Long, t As Long
    
' ###    column of measurement interval times in minutes
' ###   starts from J22
' ###   result will be written in column H next to time interval
' ###   User can interupt measurement and restart at last point
    
    LastRow = Range("J65536").End(xlUp).Row
    StartRow = Range("H65536").End(xlUp).Row + 1 ' find last entry
    If StartRow < 22 Then StartRow = 22          ' no entries yet
    
    With Range("H" & StartRow)
        Do While .Offset(x, 2).Value <> vbNullString ' while column J has value
            ' value in J  is in minutes to wait for next measurement
            For t = 0 To .Offset(x, 2).Value * 60 '* 2     ' number of  seconds to wait
                Application.Wait (Now + 0.0000115741)  ' 1 second loop
                'Check for user interuption
                If KeyDown(vbKeyF10) Then
                    MsgBox "User Interuption, when finished restart macro to continue"
                    Exit Sub
                End If
            Next t
        
            .Offset(x, 0).Select
' ###             Code to get data from Digimatic Indicator
        x = x + 1
        Loop
    End With
    MsgBox "All measurements done"
End Sub

I have split up the waiting loop in 1 second intervals to check for user interuption. If you would set application.wait for the whole period, then the user would not be able to interupt. Wait is Wait in VBA, nothing happens.

You will notice (with taskManager) that the processor is making overtime during the loop, agian as explained above, VBA keeps it busy...

Hi There I wanted to do something similar and tried this but it didn't work do you know why:

Sub Button2_Click()

Dim Myselect As String
Dim myfile2 As Workbook
 
 
Set myfile2 = Workbooks(1)
Call openfile
Dim pg1data As Range
 
Sheets("DCO Report").Select
MsgBox ("Please select your DCO from the drop down at the top")
Dim B As Boolean
    B = ThisWorkbook.WaitForUserInput(WaitSeconds:=15, _
        WaitCell:=Sheets("analysis").Range("A1"))
    If B = True Then
        MsgBox "cell changed"
    Else
        MsgBox "cell not changed"
    End If
Set pg1data = Range("B6:ac45")
pg1data.Copy
myfile2.Select
Sheets("page 1").Select
Range("a1").Paste
 
 
 

End Sub

and function placed in thisworkbook:

[Private WatchCell As Range
Private CellChanged As Boolean
Public Function WaitForUserInput(WaitSeconds As Long, _
        WaitCell As Range) As Boolean
    Dim TimeStart As Double
    CellChanged = False
    Set WatchCell = WaitCell
    TimeStart = Now
    Do
        DoEvents
        If CellChanged = True Then
            WaitForUserInput = True
            Exit Function
        End If
    Loop While Now - TimeStart < TimeSerial(0, 0, WaitSeconds)
    WaitForUserInput = False
End Function
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
        ByVal Target As Range)
    If Not WatchCell Is Nothing Then
        If Not Application.Intersect(Target, WatchCell) Is Nothing Then
            CellChanged = True
        End If
    End If
End Sub

/CODE]

I select an option from drop down menu and I assumed this would count as a change?

 

[sijpie]

what type of dropdown list? A validation list in the cell? then yes it should register as a change. But you are using the wrong function. You should use

Private Sub Worksheet_Change(ByVal Target As Range)

You want to detect a change in the sheet, not a change of sheet.

Even then I think the logic in WaitForUserInput is wrong.

 

[oldmanwilly]

what type of dropdown list? A validation list in the cell? then yes it should register as a change. But you are using the wrong function. You should use

Private Sub Worksheet_Change(ByVal Target As Range)

You want to detect a change in the sheet, not a change of sheet.

Even then I think the logic in WaitForUserInput is wrong.

thanks sIJPIE I got this code form a website earlier and just hoped it would work. Any idea how the code should look like?

Its a combo box control drop down

thanks

 

[sijpie]

oldmanwilly, did you get this sorted?