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.
hhnetworksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Fabrice LambertFabrice LambertCommented:
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.
0

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:
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.
0
Fabrice LambertFabrice LambertCommented:
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.
0
hhnetworksAuthor Commented:
Thanks Fabrice, your feedback helped. I have it resolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.