So if any are filled in, then they all have to be filled in -- all 0's are ok
and all non-zeros (summing to less than 102) is ok.
I'm not sure if this is better, but I like to keep the Ok button disabled until
all the input is ok.
I'm guessing that you have multiple rows of 5 textboxes. I would name them
nicely so that you can inspect each group by name:
TBox_01_01,TBox_01_02,TBox_01_03,TBox_01_04,TBox_0 1_05
TBox_02_01,TBox_02_02,TBox_02_03,TBox_02_04,TBox_0 2_05
....
TBox_row#_column#, ...
Then add a label for each of the rows to hold the error/warning message:
Lab_01, Lab_02, ...
And I would assume that at least one of the boxes on one of the rows has to be
used (which means that at least one of the rows is valid). Is that right?
(If yes, then this is more complicated than I first expected!)
But this seemed to work ok for me.
I created a test workbook with a userform with 2 rows (10 textboxes), 2 labels,
and 2 commandbuttons on it.
This was the code in a General module:
Option Explicit
Public Const NumberOfRows As Long = 2
Public Const NumberOfCols As Long = 5
Sub ShowTheForm()
UserForm1.Show
End Sub
Then I inserted a new Class module (where most of the real validation work is
done). This module is called Class1 (that's important!).
This is the code in that class module:
Option Explicit
Public WithEvents TBoxGroup As MSForms.TextBox
Private Sub TBoxGroup_Change()
Dim WhichRow As Long
Dim WhichCol As Long
Dim myVal As Variant 'could be anything!
Dim iRow As Long
Dim iCol As Long
Dim EmptyCtr As Long
Dim TotalCtr As Long
Dim NonNumericCtr As Long
Dim NumericCtr As Long
Dim MaxTotal As Long
Dim ErrMsg As String
Dim AtLeastOneEntry As Boolean
Dim OkToContinue As Boolean
MaxTotal = 102
If LCase(TBoxGroup.Name) Like LCase("tbox_##_##") Then
WhichRow = CLng(Mid(TBoxGroup.Name, 6, 2))
WhichCol = CLng(Right(TBoxGroup.Name, 2))
Else
'this shouldn't happen
MsgBox "Design error! Contact xxxx!"
Exit Sub
End If
AtLeastOneEntry = False
With TBoxGroup.Parent
For iRow = 1 To NumberOfRows
For iCol = 1 To NumberOfCols
myVal = .Controls("tbox_" & Format(iRow, "00") _
& "_" & Format(iCol, "00")).Value
If myVal < "" Then
'keep looking
AtLeastOneEntry = True
Exit For
End If
Next iCol
If AtLeastOneEntry = True Then
Exit For
End If
Next iRow
ErrMsg = ""
EmptyCtr = 0
NumericCtr = 0
NonNumericCtr = 0
NumericCtr = 0
For iCol = 1 To NumberOfCols
myVal = .Controls("tbox_" & Format(WhichRow, "00") _
& "_" & Format(iCol, "00")).Value
If myVal = "" Then
EmptyCtr = EmptyCtr + 1
Else
AtLeastOneEntry = True
End If
If IsNumeric(myVal) = False Then
NonNumericCtr = NonNumericCtr + 1
Else
TotalCtr = TotalCtr + myVal
NumericCtr = NumericCtr + 1
End If
Next iCol
End With
If EmptyCtr = NumberOfCols Then
ErrMsg = ""
Else
If NonNumericCtr 0 Then
ErrMsg = "All Numbers!"
Else
If NumericCtr = NumberOfCols _
And TotalCtr <= MaxTotal Then
'perfect!
Else
ErrMsg = "Check Entries on this row!"
End If
End If
End If
With TBoxGroup.Parent
.Controls("Lab_" & Format(WhichRow, "00")).Caption _
= ErrMsg
OkToContinue = True
For iRow = 1 To NumberOfRows
If .Controls("Lab_" & Format(iRow, "00")).Caption < "" Then
'there's a warning there!
OkToContinue = False
Exit For
End If
Next iRow
If AtLeastOneEntry = True Then
.CommandButton2.Enabled = OkToContinue
Else
.CommandButton2.Enabled = False
End If
End With
End Sub
And this was the code behind the userform:
Option Explicit
Dim TBoxes() As New Class1
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
MsgBox "Ok was clicked!"
End Sub
Private Sub UserForm_Initialize()
Dim TBoxCount As Long
Dim iRow As Long
Dim iCol As Long
ReDim TBoxes(1 To NumberOfRows * NumberOfCols)
TBoxCount = 0
For iRow = 1 To NumberOfRows
'fix the row label captions while we're here
Me.Controls("Lab_" & Format(iRow, "00")).Caption = ""
For iCol = 1 To NumberOfCols
TBoxCount = TBoxCount + 1
Set TBoxes(TBoxCount).TBoxGroup _
= Me.Controls("TBox_" & Format(iRow, "00") _
& "_" & Format(iCol, "00"))
Next iCol
Next iRow
With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
End With
With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With
End Sub
=======
By using the class module, I didn't need individual routines for each textbox.
I could just tie them together (as a group) and if one of the group changes,
this class module will do the work for that member.
John Walkenbach explains how this works in class modules:
http://spreadsheetpage.com/index.php..._one_procedure
(that link is one line)
http://is.gd/eFbzy
On 08/26/2010 07:53, noname wrote:
Hi,
I have a form which has rows of 5 textboxes.
Each row of textboxes needs to be validated for Emptiness.
The Validation rules that need to be applied to each row of textboxes
a
1] All textboxes in a row can be EMPTY, or All ZERO.
2] If one or more are NOT EMPTY, then Error MsgBox should be
displayed, and Focus should be set on each of them, one by one, and
they should be filled in, in order to move ahead.
3] The Cumulative total of all 5 TextBoxes (if filled), should not be
102.
TextBoxes look something like this:
lbl_Loyalty --- txt_VeryStrong txt_Strong
txt_Medium txt_Weak txt_VeryWeak --- (Total<=102)
lbl_Dedication --- txt_VeryStrong txt_Strong txt_Medium
txt_Weak txt_VeryWeak --- (Total<=102)
lbl_Empathy --- txt_VeryStrong txt_Strong txt_Medium
txt_Weak txt_VeryWeak --- (Total<=102)
I tried Exit& Enter events for each textbox, but if i set
Cancel=true, then the cursor does not leave the Active textbox which
has focus right now, even though it may be filled and there may be
other textboxes which are empty. I think the Empty validation check
should be evaluated not from a textbox event but from outside. I am
right now Stumped!.
Anyone come across something like this? Any ideas?
--
Dave Peterson