How to use combobox value from UserForm to another macro procedure

[PAngane]

i have created Macro to data entry in excel sheet. in that all the details update automatically. but for 1 field, we need user input through user form.

UserForm is as below.

Combobox1 - 1. AFRICA
2. MIDDLE-EAST

Commandbutton1 - OK

-------------------------------------

my macro is as below.:-

SUB FER Update()
Dim KPReg As String
If destination.Cells(emptyrow, 3) = "MW" Then
destination.Cells(emptyrow, 10) = "ASIA"
ElseIf destination.Cells(emptyrow, 3) = "SR" Then
destination.Cells(emptyrow, 10) = "AMERICAS"
ElseIf destination.Cells(emptyrow, 3) = "KP" Then
UserForm2.Show
End if
end sub ()



command button click event :-
Private Sub CommandButton1_Click()
Unload Me

End Sub

Pls help to modify code

 

[dmt32]

Hi,
not fully tested but try following:

Update to your code in STANDARD Module:

Sub FERUpdate()
    Dim KPReg As String
    Dim m As Variant
     
    With Destination
        m = Application.Match(.Cells(emptyrow, 3), Array("MW", "SR", "KP"), False)
        If Not IsError(m) Then
            If CInt(m) < 3 Then
                .Cells(emptyrow, 10) = Choose(CInt(m), "ASIA", "AMERICAS")
            Else
                .Cells(emptyrow, 10) = UserForm2.GetCountry
            End If
        End If
    End With
    
End Sub

Place ALL following code in your USERFORM Code Page:

Private Sub CommandButton1_Click()
    Me.Tag = CStr(Me.ComboBox1.Text)
    Me.Hide
End Sub


Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .RowSource = ""
        .List = Array("AFRICA", "MIDDLE-EAST")
    End With
    Me.CommandButton1.Enabled = True
End Sub


Function GetCountry() As String
    Me.Show
    GetCountry = CStr(Me.Tag)
    Unload Me
End Function


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True: Me.Tag = "": Me.Hide
End Sub

Hope Helpful

Dave