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:
General Module:
The Call statement line is where the error occurs.
Thanks in advance.
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
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
The Call statement line is where the error occurs.
Thanks in advance.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial