open sheet and activate macro straight away

[lpking2005]

hi, im in need of some help...

I currently have the below code, which opens a workbook.

Sub Open_BOS()

' OPEN TIMESHEET
Line = Right(Sheets("HOME PAGE").Range("A1").Value, 3)
monthCode = Sheets("HOME PAGE").Range("B14").Value

If Len(Month(monthCode)) = 1 Then
    monthCode = Year(monthCode) & "0" & Month(monthCode)
Else
    monthCode = Year(monthCode) & Month(monthCode)
End If

tspath = "T:\Production\PROD MOULDING\" & Line & " Time Sheets\" & Line & " Time Sheet " & monthCode
timesheet = Line & " Time Sheet " & monthCode

On Error GoTo errorhandler

        errcode = 1
        Windows(timesheet).Activate
''''            Application.Run "'" & timesheet & "'" & "!GoToBOS2"
        
        If errcode = 2 Then
    
            Workbooks.Open fileName:=tspath, UpdateLinks:=0, ReadOnly:=0
        
        End If


Exit Sub

errorhandler:

    If errcode = 2 Then
        errcode = 0
        
    Else
        errcode = 2
        
    End If
    Resume Next




End Sub

What i need to do is adjust this code so that when the timesheet workbook opens, it then runs a macro which is contained within that workbook called "GotoBOS2" and if the timesheet is already open, switch to that workbook and activate the same macro.

Any help would be greatly appreciated!

 

[lpking2005]

Please, is there anyone that can figure this out? because im all out of ideas

ive tried something along the lines of:

Application.Run "'" & timesheet & "'" & "!" & "GoToBOS2"

but still no joy

 

[bensonsearch]

what you could do is place the macro you wish to run in the workbook open event, so every time its opened its run

 

[lpking2005]

I dont want it to run everytime an employee opens their timesheet, because when an operator opens their timesheet they need to fill out other priority information before doing what the macro does.

Basically ive created a small control center linking to everything they need to do their job, like opening timesheets and accessing other documents and stuff.

On this timesheet their is a "BOS Checklist" that an operator has to complete sometime during their shift, this is activated by a macro button which opens a userform for them to carry out.

All i need is a link in my control center that switches to their timesheet and runs the "BOS Checklist" userform.

Thanks for the reply and i hope this gives more information.

 

[bensonsearch]

Purhaps this link will help you

http://www.vbaexpress.com/kb/getarticle.php?kb_id=279

 

[lpking2005]

Thanks for that!! im alot closer to achieving it but when it gets to running the macro, apparantly it doesnt exist!?

Ive checked the spelling but still no luck, any ideas?

 

[bensonsearch]

The only thing I can think of is to ensure the macro is saved in the file not the standard macros for all files

 

[berty2000]

Did you manage to get this to work because Im also interest in a similar idea