Excel VB user form issue when passing values to sub

hhnetworks used Ask the Experts™
Im stuck on trying to get two values (a string, and a checkbox) from an Excel user form to another Sub. Receiving 'Object Required' on the User Form code. I have researched and tried different methods to no avail. My codes are below:

User Form:
Public Sub UserForm_Initialize()
    Dim MgrNotes As String
    Dim Deactivate As Boolean
    MgrNotes = tbMgrNotes.Value
    Deactivate = cbDeactivate.Value
End Sub
Public Sub btnSave_Click()
Call OnSave(MgrNotes, Deactivate)
End Sub

Open in new window

General Module:
Sub OnSave(MgrNotes, Deactivate)

Dim cnn As New ADODB.Connection
Dim iRowNo As Integer
Dim StockUnit As String
With Sheets("Sheet1")
    cnn.Open "my connection string"
    'Read Stock Unit # From Selected Row and Run UPDATE Queries
    iRowNo = ActiveCell.Row
    StockUnit = .Cells(iRowNo, 1)
    cnn.Execute ("UPDATE Table SET fMgrNotes = '" & MgrNotes & "' WHERE (StockUnit = '" & StockUnit & "') UPDATE Table SET ActiveFlag = 0 WHERE " & Deactivate & " = TRUE AND (StockUnit = '" & StockUnit & "')")
    'Close All and Cleanup
    Set cnn = Nothing
    Set iRow = Nothing
    End With
End Sub

Open in new window

The Call statement line is where the error occurs.

Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Start by adding the Using Explicit directive at top of your modules, and explicitly declare all missing variables, including fully qualified data type (this goes for procedures and functions parameters as well).

Additional notes:
Never ever use objects such as ActiveWorkbook, ActiveCell, Selection, ect .....
These object's value are user dependant, and by nature chaotic and unpredictable.
As a programmer, you don't want to use objects on wich you have no control.



I should have stated that I am very new to VB code; I have added my Option Explicit statements and declared Public variables.  The issue seems to be my syntax with the call statement. Can you please provide an example of calling a sub from a user form while passing two variables?  Thanks again.
Fabrice LambertConsulting
Distinguished Expert 2017
Your function call is fine (albeit, deprecated).

Your issue is that procedure's data type parameters, and the data type you pass to it don't match. So explicitly qualify them.

Plus, do you have any object named MgrNotes ?
Functions and procédures parameters are variant by default, the fact that the error indicate an object required seems to show a confusion somewhere.


Thanks Fabrice, your feedback helped. I have it resolved

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial