ZipCode UserForm

Option Explicit
Public i As Long
Public rFound As Range


Private Sub cbxCity_Change()


Dim strCity As String


With Me.cbxCity


    If .Value <> vbNullString Then
        strCity = .Value
    Else
        Exit Sub
    End If
    
   With Sheet1
    Set rFound = .Columns(i).Find(what:=strCity, after:=.Cells(3, i), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
    If Not rFound Is Nothing Then
        Me.tbxZipCode = rFound.Offset(0, 1)
    Else
        MsgBox ("The selected City was not found"), vbExclamation
        Set rFound = Nothing
        Exit Sub
    End If
   End With
End With


End Sub


Private Sub cbxRegion_Change()


    Dim lastrow As Long, lastcol As Long
    Dim strRegion As String
    Dim rngCity As Range
    
    lastcol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Me.cbxRegion
        If .Value <> vbNullString Then
            strRegion = .Value
            Me.cbxCity.RowSource = ""
            Me.cbxCity.Value = ""
            Me.tbxZipCode.Value = ""
        Else
            Exit Sub
        End If
        
        With Sheet1
            Set rFound = .Rows(1).Find(what:=strRegion, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious)
            If Not rFound Is Nothing Then
                i = rFound.Column
                lastrow = .Cells(Rows.Count, i).End(xlUp).Row
                Set rngCity = .Range(.Cells(3, i), .Cells(lastrow, i))
            Else
                MsgBox ("The City list for the selection region was not found"), vbExclamation
                Exit Sub
            End If
        End With
        
    End With
    
    Me.cbxCity.RowSource = "Lists!" & rngCity.Address
    
    Set rFound = Nothing


End Sub