Excel VB user form issue when passing values to sub

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()
    EditForm.Show
    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
    cnn.Close
    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.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 13

Fabrice LambertFabrice LambertCommented: 2017-11-10

Hi,

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

hhnetworksAuthor Commented: 2017-11-10

Thanks!

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.

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

Excel VB user form issue when passing values to sub

Who is Participating?