I have restructured the logic a bit. You just need one button which acts as the start and pause / resume. The code for your userform will be as follows:
It is all contained in the attached workbook.
Option Explicit
Dim NextRow As Long
Dim FirstRow As Long
Dim PausePressed As Boolean
Dim nrRows As Long
Private Sub cmdPauseResume_Click()
If NextRow = 0 Then
PausePressed = False
NextRow = FirstRow
cmdPauseResume.Caption = "Pause"
Call ImportData
Else
PausePressed = Not PausePressed
cmdPauseResume.Enabled = False
If Not PausePressed Then
cmdPauseResume.Caption = "Pause"
cmdPauseResume.Enabled = True
Call ImportData
End If
End If
End Sub
Public Sub ImportData()
Do While NextRow <= nrRows
If PausePressed And NextRow > 1000 And NextRow Mod 100 = 0 Then
Exit Do
End If
' Your code for importing row NextRow
NextRow = NextRow + 1
If NextRow Mod 10 = 0 Then
lblProgress.Caption = "Processing row " & NextRow & " of " & nrRows
End If
DoEvents
Loop
If NextRow > nrRows Then
MsgBox "All done"
Else
cmdPauseResume.Caption = "Resume"
cmdPauseResume.Enabled = True
MsgBox "You told me to take a rest"
End If
End Sub
Private Sub UserForm_Initialize()
nrRows = 100000 ' Your code for working out how many rows 'ActiveWorkbook.Sheets("Sheet3").UsedRange.Rows.Count
FirstRow = 1 ' Your code for working out where to start 'Cells(Sheets("Sheet").Rows.Count, "C").End(xlUp).Row - 3
NextRow = 0
cmdPauseResume.Caption = "Start"
End Sub
Bookmarks