Return button code not working

[stillwillyboy]

I have two open workbooks. The first is named Outxfer, the other name varies with a pay date that has been entered. i.e. PayDate01-02-03.xls.

On Outxfer I have a button that takes me to the other workbook to preview the pay info before printing. When I go to this other wb, a button is created (named “Return”) and put on the active sheet. This button is so I can return to Outxfer. This part works. But I am having trouble with the code assigned to the newly created “Return” button to get me back to Outxfer.

Below is what I have most recently tried. I put this in my PersonalMacro book also, but still get errors. One is “User-definded type not defined”. There have been others. I know I am close. Thanks.


Sub ReturnToOutxfer()
Dim ReturnButton As CommandButton
Set ReturnButton = ActiveSheet.OLEObjects("Return").Object
With ReturnButton
Windows("OUTXFER.xls").Activate
End With
End Sub

 

[PHV]

This part works
How did you that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stillwillyboy]

I have two procedures. The first one is assigned to a button on Outxfer. It activates the other workbook and then runs the procedure "CreateButtonToReturnToOutxfer". I created the second proc with the Macro recorder. I know its needs to be cleaned up.

Sub PreviewBeforePrintingTRIRecords()
Windows("ClientPayForWeek" & ReportWeek & ".xls").Activate
'create button proc here
CreateButtonToReturnToOutxfer
End Sub

Sub CreateButtonToReturnToOutxfer()
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=322.5, Top:=26.25, Width:=93.75, Height:=35.25).Select
Selection.Verb Verb:=xlPrimary
ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Return"

Application.CommandBars("Control Toolbox").Visible = False
Range("A1").Select
End Sub

Getting to the other wb was easy. Now how do I get back to Outxfer with code for the newly created button?

Thanks.