VB Script
VB Script

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: 2017-11-10

Who is Participating?

LVL 10

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.

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.

LVL 10

Fabrice LambertFabrice LambertCommented: 2017-11-10

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.