Passing variables from sub to user form

**boc_est1986** · 08-18-2009, 06:52 AM

I can pass a variable from sub to sub no problem and have tried to do it the same way between a sub and user form but i keep getting the error

Compile Error:
ByRef argument type mismatch

This is the code i am using

Private Sub CommandButton1_Click()
    
    Dim click As Integer

    click = ActiveSheet.Range("Z1")
    
    click = click + 1

    ActiveSheet.Range("Z1") = click
    
    UserForm1.Show (click)

End Sub

Private Sub CommandButton1_Click()

    Dim addresses As String
    
    Select Case ComboBox1.Text
    
    End Select
    
    Me.Hide

    Call NotifyDepartments(click, addresses)

End Sub

Can someone help please

**se1429** · 08-18-2009, 07:36 AM

Hi,

This message means that one or more of the data types defined in your form are not the same as those in the module. If your module is declared like this:

SUB MyModule (ABC, XYZ)
.
.
END SUB

Then ABC should have the same data type as CLICK and XYZ should have the same data type as ADDRESSES in your CALL statement.

**royUK** · 08-18-2009, 07:39 AM

What are you actually trying to do? What aer you expecting UserForm1.Show(click) to do?

**boc_est1986** · 08-18-2009, 08:08 AM

i have a sheet that i need to run a filter on to pull out certain data, is then saved as a temp file and attached to an email, the userform contains a combo box with several cases in, for each case there is a number of email addresses that the attachment is then sent to. Because the new workbook is book1, book2 etc the click is counting how many new sheets are created and using that to get to the relevant window. As the name of the main workbook changes with each week i cannot deirectly reference the sheets as it can get messy. I need to pass the click variable and the addresses from the appropriate case to a sub that then sets up and sends the email. i can attach a copy of the spreadsheet if it would help

**boc_est1986** · 08-18-2009, 08:15 AM

ok my bad sorry, i hadn't declared addresses in the sendmail function

this is the working code

Main Page

Private Sub CommandButton1_Click()
    
    Dim click As Integer
    
    click = ActiveSheet.Range("Z1")
    
    click = click + 1

    ActiveSheet.Range("Z1") = click
    
    Load UserForm1
    UserForm1.Label2.Caption = click
    UserForm1.Show

End Sub

User Form

Private Sub CommandButton1_Click()

    Dim addresses As String
    Dim click As Integer
    click = Label2.Caption
    
    Select Case ComboBox1.Text
    
    Case ("All")
        addresses = ("matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com")
    Case ("Assembly")
        addresses = ("matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com")
    Case ("Machine Shop")
        addresses = ("matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com")
    Case ("S08 - Press Shop")
        addresses = ("matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com")
    Case ("S09 - Tinsmiths")
        addresses = ("matthew.phillips@gknaerospace.com; matthew.phillips@gknaerospace.com")
    Case ("S25 - Coppersmiths")
        addresses = ("matthew.phillips@gknaerospace.com")
    
    End Select
    
    Me.Hide

    Call NotifyDepartments(click, addresses)

End Sub

Private Sub UserForm_Activate()

    ComboBox1.AddItem ("All")
    ComboBox1.AddItem ("Assembly")
    ComboBox1.AddItem ("Machine Shop")
    ComboBox1.AddItem ("S08 - Press Shop")
    ComboBox1.AddItem ("S09 - Tinsmiths")
    ComboBox1.AddItem ("S25 - Coppersmiths")

End Sub

Private Sub UserForm_Terminate()

    Dim click As Integer

    click = ActiveSheet.Range("Z1")
    
    click = click - 1

    ActiveSheet.Range("Z1") = click

    End

End Sub

send mail

Sub NotifyDepartments(click As Integer, addresses As String)

    Application.ScreenUpdating = False

    Columns("C:E").Select
    Selection.EntireColumn.Hidden = False
    Range("A10:M3146").Select
    Selection.AutoFilter
    Range("D11").Select
    Selection.AutoFilter Field:=4, Criteria1:="<=7", Operator:=xlAnd
    Selection.AutoFilter Field:=11, Criteria1:="="
    
    Range("A10:C10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    ActiveWindow.ActivateNext
    Range("F10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    On Error GoTo ErrorHandler
    Windows("Book" & click).Activate
    On Error GoTo 0
    Range("D1").Select
    ActiveSheet.Paste
    Columns("D:D").EntireColumn.AutoFit
    
    Call Mail(click, addresses)
    
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter
    Range("A1").Select
        
    response = MsgBox("Email sent", vbCritical, "Complete")
    
    Exit Sub
    
ErrorHandler:
        Select Case Err.Number
            Case 9
                ActiveWindow.ActivateNext
                ActiveWorkbook.Saved = True
                ActiveWorkbook.Close
                ActiveWorkbook.Saved = True
                response = MsgBox("Critical Error, Excel is now closing, please restart and try again", vbCritical, "Error")
                Application.Quit
        End Select
        End

End Sub

Sub Mail(click As Integer, addresses As String)

    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim count As Integer
    Dim olApp As Object, olMail As Object
    Set olApp = CreateObject("Outlook.Application")
    
    Application.ScreenUpdating = False
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    count = Selection.count

    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("A1:D" & count).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Tooling due to be calibrated within the next 7 days"

    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If

    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
    End With
    
    Set olMail = olApp.CreateItem(0)
    olMail.To = addresses
    olMail.Subject = "The attached tools are due for calibration within the next 7 days"
    olMail.Body = "Please arrange for tooling to be calibrated"
    olMail.Attachments.Add (ActiveWorkbook.FullName)
    olMail.Send

    With Dest
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Windows("Book" & click).Activate
    
    ActiveWorkbook.Saved = True
    
    ActiveWorkbook.Close
    
    Sheet1.Range("A1").Select
    
End Sub

**royUK** · 08-18-2009, 09:09 AM

Sounds messy to me, can you attach an example workbook with dummy data in? I can't see why you don't create the temp file, email it then delete it. Certainly you could rename it on creation.