Use ComboBox input to find itself in the workbook?

Question

I am new to VBA and UserForms.

I have a ComboBox where the user will enter a unique Sales Order # (SalesOrder). I want my form to take this input and find it in the workbook and then update the status with the user's inputs in later ComboBoxes (CommentBox & OrderStatus). The issue I am facing is the actual code to find the Sales Order # in the workbook. I've tried what is seen below in several different variations.

If I replace all the ComboBox inputs with the actual inputs as a string, the code runs fine in a module.

Ideally, the code will loop through the sheet array finding all the lines with the Sales Order # and apply the inputs to the row.

Sub AddDataToList()

    Dim shtarray As Variant, shtname As Variant
    Dim Data As Worksheet, ws As Worksheet
    Dim wbk As Workbook
    Dim Strg As String

shtarray = Array("EMAUX", "Irene", "Cassandra", "Patricia", "EMREL", "Maria", "Jason", "Peedie", "MICRO", "PARTS", "NAVY", "DELTA")

Set wbk = ThisWorkbook

    For Each shtname In shtarray
        Set ws = Nothing
        On Error Resume Next
        Set ws = wbk.Worksheets(shtname)
        On Error GoTo 0
        If Not (ws Is Nothing) Then
            ActiveSheet.Cells.Find(StatusUpdateForm.SalesOrder.Text).Offset(0, 17).Select
            ActiveCell.Value = CommentBox.Text
            ActiveCell.Offset(0, 2).Value = OrderStatus.Text
        End If
    Next
    
    MsgBox SalesOrder.Value & "was updated."

End Sub

Thank you for the assistance!

More Information *** Below is the code for the Update command button. This is a standard two button system, one updates the records and the other cancels the form.

Private Sub UpdateButton_Click()
    
    If Not EverythingFilledIn Then Exit Sub
    
    Me.Hide
    
    AddDataToList
    
    Unload Me
    
End Sub

And code for the EverthingFilledIn

Private Function EverythingFilledIn() As Boolean

    Dim ctl As MSForms.Control
    Dim AnythingMissing As Boolean
    
    EverthingFilledIn = True
    AnythingMissing = False
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
            If ctl.Value = "" Then
                ctl.BackColor = rgbPink
                Controls(ctl.Name & "Label").ForeColor = rgbRed
                If Not AnythingMissing Then ctl.SetFocus
                AnythingMissing = True
                EverythingFilledIn = False
            End If
        End If
    Next ctl
End Function

Why is your loop looking through sheets which may not exist? — SJR, Jul 06 '20 at 17:05
Either way your loop should reference `ws` not the activesheet or cell (which never change) and check you've found something first to avoid errors. — SJR, Jul 06 '20 at 17:09
Hi SJR, the sheets do exist in the Workbook. There is a table where external data is pulled from another workbook and I don't want the code to try and update the table (if that's even possible). So I created an array to only target certain sheets. — The1stRazgriz, Jul 06 '20 at 17:45
So why do you check that `ws` isn't `Nothing` if you know it exists? — SJR, Jul 06 '20 at 18:47
That's how it was written in another form. If it's not needed then I can take it out, but it didn't affect the code when I ran this with all strings. — The1stRazgriz, Jul 06 '20 at 19:00
No I don't think it would cause your problem, it's just not necessary. I've amended my answer. — SJR, Jul 06 '20 at 19:01

SJR · Answer 1 · 2020-07-06T19:02:02.557

0

Try this (my first comment notwithstanding):

Sub AddDataToList()

Dim shtarray As Variant, shtname As Variant
Dim Data As Worksheet, ws As Worksheet
Dim wbk As Workbook
Dim Strg As String
Dim r As Range

shtarray = Array("EMAUX", "Irene", "Cassandra", "Patricia", "EMREL", "Maria", "Jason", "Peedie", "MICRO", "PARTS", "NAVY", "DELTA")

Set wbk = ThisWorkbook

For Each shtname In shtarray
    Set ws = wbk.Worksheets(shtname)
    Set r = ws.Cells.Find(StatusUpdateForm.SalesOrder.Text) 'better to specify all parameters
    If Not r Is Nothing Then
         r.Offset(0, 17).Value = CommentBox.Text
         r.Offset(0, 2).Value = OrderStatus.Text
    End If
Next

MsgBox SalesOrder.Value & "was updated."

End Sub

There is no need to select things.

edited Jul 06 '20 at 19:02

answered Jul 06 '20 at 17:16

SJR

22,986
6
18
26

Hi, I've made the updates as shown but when I click the Update command button, nothing happens. I'm not sure what the issue is, but I do appreciate your input for cleaning up the selections. – The1stRazgriz Jul 06 '20 at 17:46
I've provided the code I use for the Update and Cancel command buttons above. – The1stRazgriz Jul 06 '20 at 17:52
If nothing happens probably the text is not found. – SJR Jul 06 '20 at 18:47
The ComboBox is populated by a list from the first sheet in the array. I've been testing this by taking a Sales Order # that is found on multiple sheets in the array and trying to update it with one form entry. When I click update, nothing happens and the form remains open and populated. – The1stRazgriz Jul 06 '20 at 18:57
You need to step through the code to see what's going on, I have no way of knowing what is the problem. – SJR Jul 06 '20 at 19:00
I'm not sure how to step through a form. I can do this in a module but I don't have any knowledge of this when inputs are required by the user. – The1stRazgriz Jul 06 '20 at 19:01
Try adding a breakpoint at the start and then you can step through each line when the code is interrupted. – SJR Jul 06 '20 at 19:03
So I half solved the issue. I had an extra "End If" in the first set of code. I can now update the Sales Order in my workbook. The next issue is something in the EverythingFilledIn subroutine. I'll have to play around with that tomorrow. Thanks again SJR! – The1stRazgriz Jul 06 '20 at 19:50

Use ComboBox input to find itself in the workbook?

1 Answers1