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:
Public Sub UserForm_Initialize() EditForm.Show Dim MgrNotes As String Dim Deactivate As Boolean MgrNotes = tbMgrNotes.Value Deactivate = cbDeactivate.ValueEnd SubPublic Sub btnSave_Click()Call OnSave(MgrNotes, Deactivate)End Sub
Sub OnSave(MgrNotes, Deactivate)Dim cnn As New ADODB.ConnectionDim iRowNo As IntegerDim StockUnit As StringWith 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 WithEnd Sub
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.