Link to home
Create AccountLog in
Avatar of hhnetworks
hhnetworksFlag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of hhnetworks

ASKER

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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Fabrice, your feedback helped. I have it resolved