akinator
07-19-2005, 11:50 AM
Hi folks - I'm new to VBA - so please forgive me if this is simple. I've searched the forums to no avail.
I have 2 xls fils. File1 has cmdbutton1 in it on sheet#4 that runs some code. I would like to run file1's cmdbutton1 from File2.
I am able to open the file, set the active worksheet, but can't seem to get the syntatx to work to actually run the code.
What I have so far is:
Worksheet containing the button with the code: "Sheet4"
Button with code: "Sched1"
The code is contained in the click proc "Sched1_Click"
Private Sub CommandButton1_Click()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook
Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean
'Set file name and location. You will need to update this info!
NameOfFile = "XX_Test.xls"
PathToFile = "C:\mydir\subdir"
'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameOfFile)
If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
CloseIt = True
End If
'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error GoTo 0
Worksheets("Sheet4").Activate
'CANT GET THIS TO WORK
Sched1_Click()
If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
END Sub
Thank you so much!
I have 2 xls fils. File1 has cmdbutton1 in it on sheet#4 that runs some code. I would like to run file1's cmdbutton1 from File2.
I am able to open the file, set the active worksheet, but can't seem to get the syntatx to work to actually run the code.
What I have so far is:
Worksheet containing the button with the code: "Sheet4"
Button with code: "Sched1"
The code is contained in the click proc "Sched1_Click"
Private Sub CommandButton1_Click()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook
Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean
'Set file name and location. You will need to update this info!
NameOfFile = "XX_Test.xls"
PathToFile = "C:\mydir\subdir"
'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameOfFile)
If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
CloseIt = True
End If
'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error GoTo 0
Worksheets("Sheet4").Activate
'CANT GET THIS TO WORK
Sched1_Click()
If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
END Sub
Thank you so much!