Search code examples
excelvbacomboboxuserformxlookup

Userform combobox to lookup number from table, to replace a number in another table

I have an Excel UserForm ("UserFormEdit") that has a TextBox (TextBox1) and a ComboBox ("HolidaySelect").
In a table ("Holiday") I need the number (Column 1) that corresponds to the string (Column 2) selected in the ComboBox ("HolidaySelect").
I then need to take that number and replace a number in my table "Index" that corresponds to the name from "TextBox1".

The two tables are both on Sheet1.

"Holiday" Table:
"Holiday" Table:

"Index" Table:
Column 1 contains the names (to match TextBox1)
Column 4 contains a number (this number needs to change to the number in the "Holiday" Table column 1 that is referenced by the holiday listed in column 2[obtained from ComboBox1]).

My UserForm is called "UserFormEdit"

With my code I get:

Run-time error '9':
Subscript out of range.

Private Sub ButtonSave_Click()
    If MsgBox("Confirm the change?", vbYesNo, "Save record") = vbYes Then
        Call WriteToSheet
    Else: Unload Me
    End If
End Sub


Public Sub WriteToSheet()
    
    Dim tbl As ListObject
    Set tbl = Sheet1.ListObjects("Holiday")
    
    Dim lookupRng As Range
    Set lookupRng = tbl.ListColumns(2).DataBodyRange
    Dim returnRng As Range
    Set returnRng = tbl.ListColumns(1).DataBodyRange
    
    Dim holidayValue As Integer
    holidayValue = Application.WorksheetFunction.XLookup(UserFormEdit.HolidaySelect.Value, lookupRng, returnRng, "")
    
    Dim tbl2 As ListObject
    Set tbl2 = Sheet1.ListObjects("Index")
    
    Dim lookupRng2 As Range
    Set lookupRng2 = tbl2.ListColumns(1).DataBodyRange
    Dim returnRng2 As Range
    Set returnRng2 = tbl2.ListColumns(4).DataBodyRange
    
    Dim holidayNumber As Integer
    holidayNumber = Application.WorksheetFunction.XLookup(UserFormEdit.TextBox1.Value, lookupRng2, returnRng2, "")
    
    holidayNumber = holidayValue
    
End Sub
Solution

  • This worked for me:

    Public Sub WriteToSheet()
    
    Dim tblIndx As ListObject
    Dim selectedHol As String, holidayNum As Integer, tbox1Val
    Dim m As Variant
    
    selectedHol = Me.HolidaySelect.Value 'use `Me` to the current instance of the form
    holidayNum = HolidayNameToNumber(selectedHol)
    
    If holidayNum <> -1 Then
        Set tblIndx = Sheet1.ListObjects("Index")
        tbox1Val = Me.TextBox1.Value
        'match on first column...
        m = Application.Match(tbox1Val, tblIndx.ListColumns(1).DataBodyRange, 0)
        If Not IsError(m) Then
            'got match: update corresponding cell in column 4
            tblIndx.ListColumns(4).DataBodyRange.Cells(m).Value = holidayNum
        Else
            MsgBox "'" & tbox1Val & "' not found in Index table!"
        End If
    Else
        MsgBox "Selected holiday '" & selectedHol & "' not found in Holiday table!"
    End If
    
End Sub

'Get holiday number from its name
'    Returns -1 if not found
Function HolidayNameToNumber(holName As String) As Long
    With Sheet1.ListObjects("Holiday")
        HolidayNameToNumber = Application.WorksheetFunction.XLookup(holName, _
                                            .ListColumns(2).DataBodyRange, _
                                            .ListColumns(1).DataBodyRange, -1)
    End With
End Function