Hi
I have a user form which displays a message asking if the user wants to continue (yes/no) before writing the data to a tab, I have 2 questions:
1. How can I ensure the data entered into the combo box is only selected from the drop down list (currently a user could type anything into this box and the user form would still work)?
2. How can I ensure that the yes/no continue message only pops up when all fields are filled? Currently, even though I have a check that the fields are filled, the yes/no box will still pop up (see code below)
Thanks.
'*****************************************************************
Private Sub cmdOK_Click()
Dim RowCount As Long
'Ensure Origination area field is filled
If Me.cboOrigArea.Value = "" Then
MsgBox "Please enter an Origination area.", vbExclamation, "Add a new RCA"
Me.cboOrigArea.SetFocus
End If
'Ensure RCA name field is filled
If Me.txtRCAname.Value = "" Then
MsgBox "Please enter an RCA name.", vbExclamation, "Add a new RCA"
Me.txtRCAname.SetFocus
End If
'Ensure RCA code field is filled
If Me.txtRCAcode.Value = "" Then
MsgBox "Please enter an RCA reference code.", vbExclamation, "Add a new RCA"
Me.txtRCAcode.SetFocus
End If
'Are you sure?
iResult = MsgBox("Are you sure you want to add the following new RCA?" & vbCrLf & "" & vbCrLf & cboOrigArea.Value & ": " & _
txtRCAname.Value & " (" & txtRCAcode.Value & ")", vbYesNo + vbInformation, "Are you sure?")
'Write to 'Data' tab in workbook
RowCount = Worksheets("RCA_Data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("RCA_Data").Range("A1")
.Offset(RowCount, 0) = Me.cboOrigArea.Value
.Offset(RowCount, 1) = Me.txtRCAname.Value
.Offset(RowCount, 2) = Me.txtRCAcode.Value
End With
End Sub
Bookmarks