Option Explicit
Private Sub NextCmd_Click()
Dim LastColumn As Long
Dim LastRow As Long
LastColumn = Sheets("Form4164").Range("A13").End(xlToRight).Column
LastRow = Sheets("Form4164").Cells(22, LastColumn).End(xlDown).Row + 1
'Error messaging
If ActionCombo.Text = "" Then
CreateObject("WScript.Shell").Popup "You must enter an Action", 2, "FYI"
Exit Sub
ElseIf NameTxt.Text = "" Then
CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Name", 2, "FYI"
Exit Sub
ElseIf EmailTxt.Text = "" Then
CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Email", 2, "FYI"
Exit Sub
End If
'Unload account
Rows(LastRow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Form4164").Cells(LastRow, LastColumn).Value = AlternateAddressForm.ActionCombo.Text
Sheets("Form4164").Cells(LastRow + 1, LastColumn).Value = AlternateAddressForm.NameTxt.Text
Sheets("Form4164").Cells(LastRow + 2, LastColumn).Value = AlternateAddressForm.EmailTxt.Text
'***Rather than Unload the form, just hide it and clear the values'
Me.Hide
DoEvents
Me.ActionCombo.ListIndex = -1
Me.EmailTxt.Value = vbNullString
Me.NameTxt.Value = vbNullString
'Unload AlternateAddressForm <---See below in MsgBox
Sheets("Form4164").Cells(LastRow, 1).Value = "Alternate Contact - Add/Delete"
Sheets("Form4164").Cells(LastRow + 1, 1).Value = "Alternate Contact Name"
Sheets("Form4164").Cells(LastRow + 2, 1).Value = "Alternate Contact Email"
Range(Cells(22, 1), Cells(24, 1)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(LastRow, 1), Cells(LastRow + 2, 1)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
If MsgBox("Do you want to add/delete another Alternate Address Contact?", _
vbYesNo Or vbQuestion, _
"Add/delete Alternate Address Contact?" _
) = vbYes Then
AlternateAddressForm.Show
Else
Unload Me
End If
End Sub