VBA UserForm - Contact sheet; Using a ComboBox array to write data to specific column

Hi all,

Apologies if this is not as informative as it should be; I'm relatively new to VBA.

I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.

I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.

What I've already achieved:

The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.

a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.

Public Sub UserForm_Initialize()

ArtistAgentAddEmail.SetFocus

    Dim MultiColumnArray As Range
    Dim Element As Range
    
    Set MultiColumnArray = Worksheets(1).Range("a1:ac1")
    
    For Each Element In MultiColumnArray
        Me.ArtistAgentAddDestination.AddItem Element.Value
    Next Element
End Sub

What I would like to do next:

The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.

E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.

The code that I am currently using for my confirm button is:

Private Sub ArtistAgentAddConfirm_Click()

Dim MSG1 As Integer

With CreateObject("VBScript.RegExp")
        .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$" 'This is to validate certain characters in an Email address
        If Not .test(ArtistAgentAddEmail.Value) Then
            MsgBox "Please include Email address; E.G. joe.bloggs@email.com"
                  Cancel = True
                Else
                MSG1 = MsgBox("Are you sure you would like to add " & ArtistAgentAddEmail.Value & " to the " & ArtistAgentAddDestination.Value & " column?", Buttons:=vbYesNo)
                    If MSG1 = vbYes Then
                        MsgBox ArtistAgentAddEmail.Value & " has been added to the spreadsheet!"
                        ArtistAgentAddEmail.Text = ""
                    
                        Else
                            MsgBox "You have cancelled the addition of " & ArtistAgentAddEmail.Value
                    End If
        End If
End With

End Sub

I apologise if I've been too vague, and any assistance is appreciated.