Not all boxes have been entered

D

damorrison

I have a UserForm with several List and text boxes, is it possible to
have a message pop up, if the user clicks ok
but hasn't touched all the boxes yet
and the message box saying so and which box has been missed
 
D

Dave Peterson

How about an alternative?

Disable the Ok button until all the textboxes and listboxes have something
entered/selected.

I created a userform with
5 textboxes
2 listboxes (multiselect)
1 label (for messages)
2 command buttons (ok and cancel).

This is the code that I had behind the userform:

Option Explicit
Dim BlkProc As Boolean
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
MsgBox "Congratulations--you filled in all the boxes!"
End Sub
Private Sub ListBox1_change()
Call ChkOkBTN
End Sub
Private Sub ListBox2_change()
Call ChkOkBTN
End Sub
Private Sub TextBox1_Change()
Call ChkOkBTN
End Sub
Private Sub TextBox2_Change()
Call ChkOkBTN
End Sub
Private Sub TextBox3_Change()
Call ChkOkBTN
End Sub
Private Sub TextBox4_Change()
Call ChkOkBTN
End Sub
Private Sub TextBox5_Change()
Call ChkOkBTN
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

For iCtr = 1 To 10
Me.ListBox1.AddItem "hi" & iCtr
Me.ListBox2.AddItem "Bye" & iCtr
Next iCtr

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
End With

With Me.CommandButton2
.Caption = "Ok"
.Enabled = False
End With

BlkProc = True
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.Tag = "Hello Listbox"
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.Tag = "Goodbye ListBox"
End With
BlkProc = False

Me.TextBox1.Tag = "textbox #1 description here"
Me.TextBox2.Tag = "textbox #2 description here"
Me.TextBox3.Tag = "textbox #3 description here"
Me.TextBox4.Tag = "textbox #4 description here"
Me.TextBox5.Tag = "textbox #5 description here"

Me.Label1.Caption = "Please fill in: " & Me.TextBox1.Tag

End Sub
Sub ChkOkBTN()
Dim ctrl As Control
Dim iCtr As Long
Dim OkBtnEnabled As Boolean
Dim ListBoxHasSelection As Boolean
Dim CtrlWithProblem As String

If BlkProc = True Then Exit Sub

OkBtnEnabled = True
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
If ctrl.Value = "" Then
OkBtnEnabled = False
CtrlWithProblem = ctrl.Tag
Exit For
End If
Else
If TypeOf ctrl Is MSForms.ListBox Then
ListBoxHasSelection = False
For iCtr = 1 To ctrl.ListCount
If ctrl.Selected(iCtr) = True Then
ListBoxHasSelection = True
Exit For
End If
Next iCtr
If ListBoxHasSelection = False Then
OkBtnEnabled = False
CtrlWithProblem = ctrl.Tag
Exit For
End If
End If
End If
Next ctrl

If OkBtnEnabled = False Then
Me.Label1.Caption = "Please fill in: " & CtrlWithProblem
Else
Me.Label1.Caption = ""
End If

Me.CommandButton2.Enabled = OkBtnEnabled

End Sub
 
Top