I have a script which will cycle through 500+ files and make minor edits using a find and replace procedure. If the string cannot be found, however, I'd like the code to open a Modeless UserForm which will allow for manual editing. I would have like to execute a different subroutine after opening the UserForm, but because I am using a For Each loop, I cannot exit the routine and then return.
The script works, except when the Save_User is opened, it behaves as if it were Modal. There are two windows which will be open, but I can select neither of them, and the preview of each window is white in the taskbar. Any ideas as to why this might be happening?
Option Explicit
Public WB As Workbook, Template As Workbook
Public FindSelection As Range, ReplaceSelection As Range, FirstRow As Range, SecondRow As Range, StaticRange() As String
Public Target As Range, LastRow As Integer
Public FindRange As String, ReplaceRange As String, FindText As String, ReplaceText As String
Public Count As Integer, Updated As Integer
Public Const FilePath = "C:\GenericPath"
Public Sub AddApplicable()
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(FilePath)
Count = 0
Updated = 0
For Each File In Folder.Files
If File.Type Like "Microsoft Excel*" Then
Application.ScreenUpdating = False
Set WB = Workbooks.Open(File.Path)
Set FindSelection = WB.Worksheets("Repair Instruction").Range("K17:W40")
Set ReplaceSelection = WB.Worksheets("Repair Instruction").Range("X17:AJ40")
LastRow = WB.Worksheets("Repair Instruction").Range("K40:W40").End(xlUp).Row
Set Target = FindSelection.Find(FindText, LookAt:=xlWhole)
If Target Is Nothing Then
If LastRow = 39 Then
Save_User.Show vbModeless
While Save_User.Visible
DoEvents
Wend
GoTo NextAdd
Else
If LastRow Mod 2 = 0 Then
WB.Worksheets("Repair Instruction").Range("K" & LastRow + 1, "W" & LastRow + 1) = FindText
WB.Worksheets("Repair Instruction").Range("X" & LastRow + 1, "AJ" & LastRow + 1) = _
Add_User.txtFirst.Value
WB.Worksheets("Repair Instruction").Range("X" & LastRow + 2, "AJ" & LastRow + 2) = _
Add_User.txtSecond.Value
Else
WB.Worksheets("Repair Instruction").Range("K" & LastRow + 2, "W" & LastRow + 2) = FindText
WB.Worksheets("Repair Instruction").Range("X" & LastRow + 2, "AJ" & LastRow + 2) = _
Add_User.txtFirst.Value
WB.Worksheets("Repair Instruction").Range("X" & LastRow + 3, "AJ" & LastRow + 3) = _
Add_User.txtSecond.Value
End If
WB.Close True
Updated = Updated + 1
End If
Else
Set FirstRow = WB.Worksheets("Repair Instruction").Range("X" & Target.Row, "AJ" & Target.Row)
Set SecondRow = WB.Worksheets("Repair Instruction").Range("X" & Target.Row + 1, "AJ" & Target.Row + 1)
FirstRow.Value = Add_User.txtFirst.Value
SecondRow.Value = Add_User.txtSecond.Value
WB.Close True
End If
End If
NextAdd:
Count = Count + 1
Next File
Unload Applicable_User
Unload Add_User
Unload Ref_User
Unload Replace_User
MsgBox (Updated & " of " & Count & " files updated.")
End Sub
EDIT
David, thank you! I wouldn't have even considered that as the reason, but I'm grateful that it is relatively simple! I am not aware of any other functions that would hide the cycling Excel files, so I edited the If statement with the Save_User to be:
If LastRow = 39 Then
Application.ScreenUpdating = True
WB.Activate
Save_User.Show vbModeless
While Save_User.Visible
DoEvents
Wend
Application.ScreenUpdating = False
GoTo NextAdd
...
End If
The cycling Excel windows now appear for editing, but I can't actually make any changes (my cursor is now a crosshair, and I can select cells but not change their values). None of the Workbooks should be protected, so is there something that I'm missing? Or perhaps a better way to hide the activity in the background (because I don't want 500+ files to appear and disappear)?
