Cancel Button is not clickable when macro is running

chooriang

New Member
Joined
Jan 20, 2014
Messages
7
I create macros to find and replace texts in word documents. Consider the number of documents, I add a Cancel button to let the user to cancel the loop anytime. But the problem is, once the loop is started (start button is clicked) then Cancel button is then no longer clickable. How to keep the button clickable when macro is running?
Tried to google it but can't found solution.

Thanks.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Maybe necessary to add the code
Code:
On Error GoTo errHandler
    With wsRaw
        lrRaw = Last(1, .Columns(1))
        lcRaw = Last(2, .Rows(3))
        
        For i = 4 To lrRaw
        
            If bCancel = True Then GoTo ExitSub
        
            strEmpName = .Cells(i, 2).Value
            
            Application.ScreenUpdating = True
            ws.Range("C13").Value = "Working on OT Form for """ & strEmpName & """"
            Application.ScreenUpdating = False
            DoEvents
            
            strOutputFile = addFileExtension(strTemplateFile, strEmpName)
            strOutputPath = strOutputLocation & strOutputFile
            FileCopy strTemplatePath, strOutputPath
            
            Set wdApp = GetObject(, "Word.application")    'gives error 429 if Word is not open
            If Err = 429 Then
                Set wdApp = CreateObject("Word.application")    'creates a Word application
                Err.Clear
            End If
            wdApp.Visible = False 'True '
            
            Set wdDoc = wdApp.Documents.Open(strOutputPath)
            'With wdDoc
            '    .Application.WindowState = 1
            '    .ActiveWindow.ActivePane.View.Zoom.Percentage = 100
            'End With
            
            For j = 1 To 7 'lcRaw
                strSearch = "< " & .Cells(3, j).Value & " >"
                strReplace = .Cells(i, j).Value
                For Each wdRng In wdDoc.StoryRanges
                    With wdRng.Find
                        .Text = strSearch
                        .Replacement.Text = strReplace
                        .Wrap = wdFindContinue
                        .Execute Replace:=wdReplaceAll
                    End With
                    DoEvents
                Next wdRng
                DoEvents
            Next  'lcRaw (data column)
            
            wdDoc.Close SaveChanges:=True
            Set wdRng = Nothing: Set wdDoc = Nothing
            DoEvents
        Next 'lrRaw (data row)
    End With
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top