I have 6 textboxes on a userform but require only 1 entry.

[danbates]

Hi,

I have 6 textboxes numbered 2-7 on a userform.

I only want 1 with a value in when I press the command button to transfer the value to the worksheet.

If there is a value in 2 or more I'd like the textboxes cleared and a message saying "only one textbox value please"

any help would be appreciated.

Kind Regards

Dan

 

[mikerickson]

Here's one way

Dim i As Long
Dim FirstFull As Long

For i = 2 To 7
    With Me.Controls("TextBox" & i)
        If .Text <> vbNullString Then
            If FirstFull <> 0 Then
                .Text = vbNullString
                FirstFull = -1 * Abs(FirstFull)
            Else
                FirstFull = i
            End If
        End If
    End With
Next i
If FirstFull < 0 Then
    Me.Controls("TextBox" & Abs(FirstFull)).Text = vbNullString
    MsgBox "only one textbox please"
End If

Another approach would be to disable the other textboxes whenever a textbox gets an entry

Private Sub TextBox2_Change()
    TextBoxEnabelment (TextBox2.Text = vbNullString)
End Sub
Private Sub TextBox3_Change()
    TextBoxEnabelment (TextBox3.Text = vbNullString)
End Sub
Private Sub TextBox4_Change()
    TextBoxEnabelment (TextBox4.Text = vbNullString)
End Sub
'...
Private Sub TextBox7_Change()
    TextBoxEnabelment (TextBox7.Text = vbNullString)
End Sub

Sub TextBoxEnabelment(flag As Boolean)
    Dim i As Long
    For i = 2 To 7
        With Me.Controls("TextBox" & i)
           .Enabled = (.Text = vbNullString) Imp flag
        End With
    Next i
End Sub

 

[wideboydixon]

Something along these lines:

Private Sub CommandButton1_Click()

Dim filledBoxes As Long
Dim thisBox As Long

filledBoxes = 0
For thisBox = 2 To 7
    If Controls("TextBox" & CStr(thisBox)).Text <> "" Then
        filledBoxes = filledBoxes + 1
    End If
Next thisBox

If filledBoxes > 1 Then
    MsgBox "only one textbox value please"
    For thisBox = 2 To 7
        Controls("TextBox" & CStr(thisBox)).Text = ""
    Next thisBox
End If

End Sub

WBD

 

[danbates]

Thank you mikerickson & WBD for your responses.

mikerickson I have used your first suggestion and I was wondering if you would modify it slightly for me so a message box pops up if none of the textboxes have been edited?

Thanks again

Dan

 

[mikerickson]

... so a message box pops up if none of the textboxes have been edited?

What have you tried?