We help IT Professionals succeed at work.
Get Started

Excel VB user form issue when passing values to sub

hhnetworks
hhnetworks asked
on
127 Views
Last Modified: 2018-02-02
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.
Comment
Watch Question
Consulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE