Using Call function in Sheet Button

[lionelnz]

I have a macro that copies a sheet to a new workbook, opens Outlook & attaches the workbook to email and it works fine as macro called from the workbook.

However when I use the call function to active this macro via a sheet button it does not work. I am guessing I will need to use ByRef but not sure how.

Any advice appreciated.

Cheers

 

[Firefly2012]

Hi

It's really hard to offer up any meaningful advice when we can't see the code that isn't working, so please do post up the code.

What is required:

1. code that opens Outlook (in its entirety) and what workbook it is in and what module (ie is it a standard module or a sheet module or the ThisWOrkbook module).

2. Code behind the button (in its entirety. State which workbook the button is contained in

 

[lionelnz]

All the codes & buttons are in the same workbook. This is the code that works fine from workbook but comes up as an error when trying to call it from sheet button in the same workbook.

Sub CopyAsht2NwWbk()
'This macro developed for EW timesheet
'01Timesheet Master.xls
'Copies active sheet into new workbook,
'deletes the empty sheets &
'renames the new workbook &
'emails the new workbook

    'Hwb is source/active wbk and Nwb is new wbk
    Dim Hwb, Nwb As Workbook
    'Hws is source/active ws and Nws is new ws
    Dim Hws, Nws As Worksheet
    'Gives filename and keeps new file in current directory
    Dim Sn, Nm, Dt, FN, myPath, msg As String
    
    Dim OutApp As Object
    Dim OutMail As Object
           
    Set Hwb = ActiveWorkbook
    Set Hws = ActiveSheet
    
    'Application.ScreenUpdating = False
     ' Set variable to path of active workbook
    myPath = ActiveWorkbook.Path
    Set Nwb = Workbooks.Add
    Hws.Copy Before:=Sheets(1)
    
    'Staff name
    Sn = Range("D7").Value
    'Adds " WE "
    Nm = Sn & " WE "

'Date format for filename
  Dt = Format(Range("C25").Value, "DDMMYYYY")
'Workbook name
  FN = "Timesheet " & Nm & Dt
Application.DisplayAlerts = False
        'Deletes excess sheets
        For Each Nws In Sheets
            If Left(Nws.Name, 2) = "Sh" Then Nws.Delete
        Next Nws
Application.DisplayAlerts = True

ActiveWorkbook.SaveAs (myPath & "/" & FN)

 'Opens OL & sets OL visible
 'Set OutApp = New Outlook.Application
 'OutApp.Application.Visible = True
 'Set OutMail = OutApp.CreateItem(olMailItem)
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(olMailItem)
 
        msg = "Hi Des." & vbCrLf
        msg = msg & vbCrLf
        msg = msg & "Please find my timesheet attached, thanks." & vbCrLf
        msg = msg & vbCrLf
        msg = msg & Sn

    'On Error Resume Next
    With OutMail
        .To = "email@2u.com"
        .CC = ""
        .BCC = ""
        .Subject = "TimeSheet " & Sn 'Staff Name in subject
        .Body = msg
        .Attachments.Add (myPath & "/" & FN & ".xls")
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Display 'or use .Send
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Nwb.Close
    Hwb.Activate
    Kill (myPath & "/" & FN & ".xls")
End Sub

This is the code from the sheet button

Private Sub CopyAsht2NwWbk_Click()
Call CopyAsht2NwWbk
End Sub

I also have another sheet button that works fine using call function as above i.e.,

Private Sub CopySheet_Click()
Call CopySheetAs
End Sub

 

[Firefly2012]

And what do you mean when you say it doesn't work? Does it error? If so, which line is highlighted when you want to debug?

I also asked which modules the code was contained in - please indicate which module for all pieces of code (ie sheet module, standard module or Thisworkbook module. This is important.

 

[lionelnz]

The main codes (for both macros) are in standard module, sheet button is in the sheet module (in design mode right click the sheetbutton & go to code).

The Copysheet code works fine when calling it from the sheetbutton but when I click sheetbutton for Call CopyAsht2NwWbk I get error msg "Compile error: Expected procedure, not variable". When I close down the error msgbox the heading line of the code is highlighted in yellow.

I have also tried making the standard Module code a public sub but still no joy. Should I try dimming outlook variables as public variables?

I expect that this means I have to use ByVal or ByRef syntax to send outlook info to the sheet button procedure. Not sure how tho????

I have also tried making the standard Module code a public sub but still no joy. Should I try dimming outlook variables as public variables?


Thanks anyway

 

[Firefly2012]

You need to rename your button to something other than the name of your procedure that deals with the Outlook stuff:

Private Sub CopyAsht2NwWbk_Click()   'you are getting a conflict between the name of the button (in red) and the name of the procedure (in blue)
Call CopyAsht2NwWbk
End Sub

It wouldn't be good practice, but I suspect you could alternatively preface the call routine with the name of the module in which the Outlook routine resides eg:

Call Module1.CopyAsht2NwWbk

 

[lionelnz]

Solved!!!! Re: Using Call function in Sheet Button

Solved. The simple problem was that I had the same name for the sheetbutton as the procedure name. So sheetbutton name goes from this

Private Sub [B][COLOR=Red]CopyAsht2NwWbk[/COLOR][/B]_Click()
Call CopyAsht2NwWbk
End Sub

TO This

Private Sub [B][COLOR=Blue]CopyAsh2NwWbk[/COLOR][/B]_Click()
Call CopyAsht2NwWbk
End Sub

& Now hunky dory!!!!!!!!

 

[lionelnz]

Re: Solved!!!! Re: Using Call function in Sheet Button

Thanks Firefly. I did figure it out & went to send the above reply but didn't submit because I used too many smilies & only realised 2 day.

Thanks for your assistance.