running a macro in another workbook

[nwille7400]

Hello,
I'm trying to change several workbooks (target) in one folder using vba in a separate (source) workbook. All the target workbooks are pretty much the same, small differences. After I make my changes I need to run a macro that exists in the target workbook to format some other areas in the target workbook. I'm opening the target files in the folder one at a time and everything works fine until I try to run the macro. The macro is in Module 8 and is called 'Update'. It is used on Sheet1 in a button. Gives the error attached but when I just open one of the target workbooks and run the macro it works fine. Can someone help on this? Let me know if you need any info that I may have left out. In some of the target workbooks the button has been removed and I would like to know how to detect if the button itself is still showing in sheet1. Thanks


Sub Button1_Click()
'this sets your template workbook/worksheet
Dim copyWB As Workbook
Set copyWB = ThisWorkbook
'open insp report to copy from
file_name = "new inspection report_Rev E beta 7.xls"
Set myTextFile = Workbooks.Open("X:\Inspection Reports\test\Beta version\" & file_name)
'this creates a collection of all filenames to be processed

Dim loopFolder As String
Dim fileNm As Variant
Dim myFiles As New Collection


'don't forget the backslash before the final double-quote below
loopFolder = ThisWorkbook.Sheets("Sheet1").Cells(3, 4) 'this is where the folder location is stated
fileNm = Dir(loopFolder & "*.xls")

Do While fileNm <> ""
myFiles.Add fileNm
fileNm = Dir
'MsgBox fileNm
Loop
Application.DisplayAlerts = False
'this loops through all filenames and copies your copyWS to the beginning
Dim wb As Workbook
insp_report_name = ""
For Each fileNm In myFiles
Set wb = Workbooks.Open(Filename:=(loopFolder & fileNm))
wb.Sheets("Input Sheet").Range("A5:I13").Copy _
myTextFile.Sheets("Input Sheet").Range("A5") 'copy input sheet data to new sheet
wb.Sheets("Input Sheet").Range("A15:I23").Copy _
myTextFile.Sheets("Input Sheet").Range("A16")
wb.Sheets("Input Sheet").Range("E30:E37").Copy _
myTextFile.Sheets("Input Sheet").Range("E31")

If wb.Sheets("Input Sheet").Checkbox8 = True Then
myTextFile.Sheets("Input Sheet").Checkbox8 = True
Else
myTextFile.Sheets("Input Sheet").Checkbox8 = False
myTextFile.Sheets("Input Sheet").Checkbox9 = True
myTextFile.Activate
Application.Run "CheckBox9_Click"
End If

insp_report_name = wb.Name 'record name of specific insp report
wb.Close 'close specific insp report
Kill loopFolder & insp_report_name 'delete specific insp report
myTextFile.SaveAs Filename:=loopFolder & insp_report_name 'save new inspection report as specific insp report name
Sheets("Insp. Sheet Final").Activate
Next
Application.DisplayAlerts = True
End Sub

 

[davesexcel]

Application.Run "CheckBox9_Click"

Use workbook variable name to run correct macro

Sub RunFromOtherWB()
    Dim wb As Workbook
    Set wb = Workbooks("2018.xls")
    Application.Run ("'" & wb.Name & "'!CheckBox2_Click")
End Sub

 

[nwille7400]

Thanks Davesexcel, that works well with a macro that I have that is made with formcontrol and in a module. I also have one that is made with active x and is in sheet1 code. It gives me the error code attached when I try to run that code.

Also, any thoughts on this part of my request?
'In some of the target workbooks the button has been removed and I would like to know how to detect if the button itself is still showing in sheet1.'