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?

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

On Aug 26, 10:07*pm, Dave Peterson wrote:
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...form_buttons_w...

(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


========================================


Hi Dave,

WOW! This is sheer Brilliance! Never came across a solution so
Brilliant and that too when i least expected a reply so FAST!
:D

Its ALMOST exactly what i wanted!

See, the textboxes are actually in a Frame2 on 2nd page of a
Multipage1, which is placed on a UserForm. So, the Form is already
Initialized and loaded. I am Clicking on a Button on 1st page, which
selects the 2nd page. In such a scenario, how do i invoke the code?
Your code is invoked from the Module:
Sub ShowTheForm()
UserForm1.Show
End Sub

But in my case, how do i go about invoking the code?

Do i add the above Form_Initialize code in my own Form_Initialize
code?

In Form_Initialize event, do i have to do the following:

For iRow = 1 To NumberOfRows
'fix the row label captions while we're here
Userform1.Multipage1.frame2.Controls("Lab_" & Format(iRow,
"00")).Caption = ""
For iCol = 1 To NumberOfCols
TBoxCount = TBoxCount + 1
Set TBoxes(TBoxCount).TBoxGroup _
= Userform1.Multipage1.frame2.Controls("TBox_" &
Format(iRow, "00") & "_" & Format(iCol, "00"))
Next iCol
Next iRow



Please advice.

Best regards.

On Aug 27, 12:52*am, noname wrote:
On Aug 26, 10:07*pm, Dave Peterson wrote:









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...form_buttons_w...

(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

========================================

Hi Dave,

WOW! This is sheer Brilliance! Never came across a solution so
Brilliant and that too when i least expected a reply so FAST!
:D

Its ALMOST exactly what i wanted!

See, the textboxes are actually in a Frame2 on 2nd page of a
Multipage1, which is placed on a UserForm. So, the Form is already
Initialized and loaded. I am Clicking on a Button on 1st page, which
selects the 2nd page. In such a scenario, how do i invoke the code?
Your code is invoked from the Module:

Sub ShowTheForm()
* * *UserForm1.Show
End Sub

But in my case, how do i go about invoking the code?

Do i add the above Form_Initialize code in my own Form_Initialize
code?

In Form_Initialize event, do i have to do the following:

* * *For iRow = 1 To NumberOfRows
* * * * *'fix the row label captions while we're here
* * * * *Userform1.Multipage1.frame2.Controls("Lab_" & Format(iRow,
"00")).Caption = ""
* * * * *For iCol = 1 To NumberOfCols
* * * * * * *TBoxCount = TBoxCount + 1
* * * * * * *Set TBoxes(TBoxCount).TBoxGroup _
* * * * * * * * *= Userform1.Multipage1.frame2.Controls("TBox_" &
Format(iRow, "00") & "_" & Format(iCol, "00"))
* * * * *Next iCol
* * *Next iRow

Please advice.

Best regards.


===============================================

Ok. I selected & cut the controls (textboxes, labels, buttons) and
added a Multipage and then a Frame on one of the pages, and then
pasted the controls. It works Ok. However, i have other textboxes on
the other pages, in frames, which i see are not getting effected.
Thats Good! The other textboxes need to have some other validations
similar to these ones (for e.g. Cumulative of scores in textboxes not
103) and they look like this:

Image ---
txtFactorName1 ----
LabelErr1
txtImportance txtTop2Box txtBottom2Box

Product ---
txtFactorName2 ----
LabelErr2
txtImportance txtTop2Box txtBottom2Box

Sales ---
txtFactorName3 ----
LabelErr3
txtImportance txtTop2Box txtBottom2Box


Validations:
----------------
1] Importance is not related to Top2Box & Bottom2Box. i.e. Its not
counted in Cumulative score.

2] Importance - Value between 1 & 100

3] Top2Box - Value between 0 & 100

4] Bottom2Box - Value between 0 & 100

5] Cumulative score of Top2Box & Bottom2Box NOT 103 i.e. <=103


Questions:
--------------

1] Do i need to create another class for this page controls? what
parts do i need to modify inorder to incorporate the individual
validations for textboxes (e.g. 1 to 100, 0 to 100 etc)

2] Again, do i have to add some additional code to Form_Initialize,
just like the one you showed before?

3] i am selecting pages, by clicking a button on 1st page, which
selects 2nd page. Similarly i am clicking a button on 2nd page, which
selects the 3rd page. Do i have to Enable / Disable these buttons
(just like your CommandButton OK) as per the Error messages made by
incorrect or incomplete entries in the textboxes?

Your help would be most appreciated!

Thanks & Best regards.

I'm not sure what the best approach would be.

I'd start by creating another class module for the textboxes that contain 1-100.
But that means that they should be named nicely, too.

Well, if the number of these textboxes is small, I'd just validate each with its
own _change event.

And maybe add a label to hold an error message if there are no items in the
group of 5 textboxes. In fact, you could name all the error message labels
nicely so that you could use them as an indicator when the Ok button should be
enabled.

You'd just check all the labels looking for some string different from "". If
you find one, the ok button is disabled.

===
And if you don't want the user to advance to the next page when there's an
error, you'll have to use that same kind of technique.

Since you didn't say that you were building something like a wizard, I just
enabled/disabled the ok button.



On 08/26/2010 15:35, noname wrote:
<<snipped
Questions:
--------------

1] Do i need to create another class for this page controls? what
parts do i need to modify inorder to incorporate the individual
validations for textboxes (e.g. 1 to 100, 0 to 100 etc)

2] Again, do i have to add some additional code to Form_Initialize,
just like the one you showed before?

3] i am selecting pages, by clicking a button on 1st page, which
selects 2nd page. Similarly i am clicking a button on 2nd page, which
selects the 3rd page. Do i have to Enable / Disable these buttons
(just like your CommandButton OK) as per the Error messages made by
incorrect or incomplete entries in the textboxes?

Your help would be most appreciated!

Thanks& Best regards.

--
Dave Peterson

ps.

If you decide that you have too many textboxes of each type, you could create
those error message labels for each validity check you need for each of the
several class modules you need.

Then each class module will have to check all the labels to see if the
Ok/Next/Previous button should be enabled.



On 08/26/2010 15:52, Dave Peterson wrote:
I'm not sure what the best approach would be.

I'd start by creating another class module for the textboxes that contain 1-100.
But that means that they should be named nicely, too.

Well, if the number of these textboxes is small, I'd just validate each with its
own _change event.

And maybe add a label to hold an error message if there are no items in the
group of 5 textboxes. In fact, you could name all the error message labels
nicely so that you could use them as an indicator when the Ok button should be
enabled.

You'd just check all the labels looking for some string different from "". If
you find one, the ok button is disabled.

===
And if you don't want the user to advance to the next page when there's an
error, you'll have to use that same kind of technique.

Since you didn't say that you were building something like a wizard, I just
enabled/disabled the ok button.



On 08/26/2010 15:35, noname wrote:
<<snipped
Questions:
--------------

1] Do i need to create another class for this page controls? what
parts do i need to modify inorder to incorporate the individual
validations for textboxes (e.g. 1 to 100, 0 to 100 etc)

2] Again, do i have to add some additional code to Form_Initialize,
just like the one you showed before?

3] i am selecting pages, by clicking a button on 1st page, which
selects 2nd page. Similarly i am clicking a button on 2nd page, which
selects the 3rd page. Do i have to Enable / Disable these buttons
(just like your CommandButton OK) as per the Error messages made by
incorrect or incomplete entries in the textboxes?

Your help would be most appreciated!

Thanks& Best regards.


--
Dave Peterson