Below, I changed your code just a little bit...note that
with this code the sheet itself does not need to be the
active sheet.
The issue with your code was your IF statement.
If requires the condition to evalute to True or False
Your statement
If TextBoxCN1 Then
is wrong since TextBNocCN1 is a control, with the default
property, text, which is a character string
The simple solution:
If TextBoxCN1<"" Then
and of course...
If TextBoxHN1 Then
Private Sub OKButton_Click()
Dim NextRow As Long
Dim ws As Worksheet
Set ws = Sheets("Phone_List")
If TextBoxName.Text = "" Then
MsgBox "You must enter a name."
TextBoxName.SetFocus
Exit Sub
End If
' Determine the next empty row
NextRow = ws.Range("A65000").End(xlUp).Row + 1
ws.Cells(NextRow, 1) = TextBoxName.Text
' Transfer the shift
If OptionButton117 Then ws.Cells(NextRow, 4) = "11-7"
If OptionButton73 Then ws.Cells(NextRow, 4) = "7-3"
If OptionButton311 Then ws.Cells(NextRow, 4) = "3-11"
' Transfer the phone numbers
If TextBoxHN1 < "" Then
ws.Cells(NextRow, 2) = _
TextBoxHN1 & TextBoxHN2 & TextBoxHN3
End If
If TextBoxCN1 < "" Then
ws.Cells(NextRow, 3) = _
TextBoxCN1 & TextBoxCN2 & TextBoxCN3
End If
' Clear the name control for the next entry
TextBoxName.Text = ""
' Clear the home number
TextBoxHN1.Text = ""
TextBoxHN2.Text = ""
TextBoxHN3.Text = ""
' Clear the cell number
TextBoxCN1.Text = ""
TextBoxCN2.Text = ""
TextBoxCN3.Text = ""
End Sub
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
First I am NEW to VBA/VBE, so try not to laugh too much
at my codes.
What I'm trying to do (key word trying) is create a
phone list that
uses a user form for input then displays it on my
worksheet. I'm almost
there (I thought). When I tested the user form it gives
me an error if
I leave one of the two phone number text boxes blank.
I need help with creating some sort of message box that
will resume if
the user clicks OK, or something of the sort.
Here is the code
(Any suggestions will be greatly appreciated)
Private Sub OKButton_Click()
Dim NextRow As Long
' Making sure Sheet1 is active
Sheets("Phone_List").Activate
' Make sure a name is entered
If TextBoxName.Text = "" Then
MsgBox "You must enter a name."
TextBoxName.SetFocus
Exit Sub
End If
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A") ) + 1
' Transfer the name
Cells(NextRow, 1) = TextBoxName.Text
' Transfer the shift
If OptionButton117 Then Cells(NextRow, 4) = "11-7"
If OptionButton73 Then Cells(NextRow, 4) = "7-3"
If OptionButton311 Then Cells(NextRow, 4) = "3-11"
' The issues is below
' Transfer the phone numbers
If TextBoxHN1 Then Cells(NextRow, 2) = TextBoxHN1 &
TextBoxHN2 &
TextBoxHN3
If TextBoxCN1 Then Cells(NextRow, 3) = TextBoxCN1 &
TextBoxCN2 &
TextBoxCN3
' Clear the name control for the next entry
TextBoxName.Text = ""
TextBoxName.SetFocus
' Clear the home number
TextBoxHN1.Text = ""
TextBoxHN1.SetFocus
TextBoxHN2.Text = ""
TextBoxHN2.SetFocus
TextBoxHN3.Text = ""
TextBoxHN3.SetFocus
' Clear the cell number
TextBoxCN1.Text = ""
TextBoxCN1.SetFocus
TextBoxCN2.Text = ""
TextBoxCN2.SetFocus
TextBoxCN3.Text = ""
TextBoxCN3.SetFocus
TextBoxName.SetFocus
' Ya I thought I was finished
End Sub:
---
Message posted from http://www.ExcelForum.com/
.