User Form

[Rockee052]

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 tha
uses a user form for input then displays it on my worksheet. I'm almos
there (I thought). When I tested the user form it gives me an error i
I leave one of the two phone number text boxes blank.
I need help with creating some sort of message box that will resume i
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

 

[Kieran]

Without the form to test it, this is just a guess but the followin
replacements may make a difference.

' The issues is below

' Transfer the phone numbers
If TextBoxHN1 Then Cells(NextRow, 2).value = TextBoxHN1.value
TextBoxHN2.value & TextBoxHN3.value ' added .value to define th
properties a bit better
If TextBoxCN1 Then Cells(NextRow, 3).value = TextBoxCN1.value
TextBoxCN2.value & TextBoxCN3.value


' Clear the name control for the next entry
TextBoxName.Text = ""
TextBoxName.SetFocus ' This line not necessary - can be deleted

' Clear the home number
TextBoxHN1.Text = ""
TextBoxHN1.SetFocus ' This line not necessary - can be deleted
TextBoxHN2.Text = ""
TextBoxHN2.SetFocus ' This line not necessary - can be deleted
TextBoxHN3.Text = ""
TextBoxHN3.SetFocus ' This line not necessary - can be deleted


' Clear the cell number
TextBoxCN1.Text = ""
TextBoxCN1.SetFocus ' This line not necessary - can be deleted
TextBoxCN2.Text = ""
TextBoxCN2.SetFocus ' This line not necessary - can be deleted
TextBoxCN3.Text = ""
TextBoxCN3.SetFocus ' This line not necessary - can be deleted
TextBoxName.SetFocu

 

[Rockee052]

It still does the same thing. I get a runtime error.

 

[Kieran]

Does TextBoxHN1 return a true/false result or do you mean
to say if TextBoxHN1 <> "" then do Cells(NextRow, 2).value =
TextBoxHN1.value & TextBoxHN2.value & TextBoxHN3.value
and again on the next line.

What is the runtime error type?

 

[Patrick Molloy]

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