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

On Aug 27, 1:56*am, Dave Peterson wrote:
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

=======================
Hi Dave,

Have plugged in your code in the Page1 Button's Click event which i
use to navigate to page2, which has the textboxes. Works well!

Please tell me what i need to change here, in order to Enable the
Page1 button, on fully filling atleast one Row of textboxes.

With TBoxGroup.Parent
.Controls("Lab_" & Format(WhichRow, "00")).Caption = ErrMsg

OkToContinue = True
' OkToContinue = False

For iRow = 1 To NumberOfRows
If .Controls("Lab_" & Format(iRow, "00")).Caption <
"OK!" Then
'there's a warning there!
OkToContinue = False
Exit For
' Else
' OkToContinue = True
' Exit For
End If
Next iRow

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

End With

-----------------

On Aug 27, 3:19*am, noname wrote:
On Aug 27, 1:56*am, Dave Peterson wrote:









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

=======================
Hi Dave,

Have plugged in your code in the Page1 Button's Click event which i
use to navigate to page2, which has the textboxes. Works well!

Please tell me what i need to change here, in order to Enable the
Page1 button, on fully filling atleast one Row of textboxes.

* * *With TBoxGroup.Parent
* * * * *.Controls("Lab_" & Format(WhichRow, "00")).Caption = ErrMsg

* * * * *OkToContinue = True
* * * * *' OkToContinue = False

* * * * *For iRow = 1 To NumberOfRows
* * * * * * *If .Controls("Lab_" & Format(iRow, "00")).Caption <
"OK!" Then
* * * * * * * * *'there's a warning there!
* * * * * * * * *OkToContinue = False
* * * * * * * * *Exit For
* * * * * *' Else
* * * * * *' * * *OkToContinue = True
* * * * * *' * * *Exit For
* * * * * * *End If
* * * * *Next iRow

* * * * *If AtLeastOneEntry = True Then
* * * * * * *eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * *Else
* * * * * * *eqForm.cmd_AddFactors01.Enabled = False
* * * * *End If

* * *End With

-----------------

===============
EDIT: Please tell me what i need to change here, in order to Enable
the
Page2 button, on fully filling atleast one Row of textboxes.

Maybe...

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
whateverthenextbuttonis.enabled = oktocontinue
Else
eqForm.cmd_AddFactors01.Enabled = False
whateverthenextbuttonis.enabled = false
End If


On 08/26/2010 17:19, noname wrote:
If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

--
Dave Peterson

On Aug 27, 5:03*am, Dave Peterson wrote:
Maybe...

* * * * * If AtLeastOneEntry = True Then
* * * * * * * eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * * * whateverthenextbuttonis.enabled = oktocontinue
* * * * * Else
* * * * * * * eqForm.cmd_AddFactors01.Enabled = False
* * * * * * * whateverthenextbuttonis.enabled = false
* * * * * End If

On 08/26/2010 17:19, noname wrote:

* * * * * If AtLeastOneEntry = True Then
* * * * * * * eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * Else
* * * * * * * eqForm.cmd_AddFactors01.Enabled = False
* * * * * End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
Else
If NonNumericCtr 0 Then
ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
Else
If NumericCtr = NumberOfCols And TotalCtr <= MaxTotal
Then
'perfect!
ErrMsg = "OK"
Else
ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
End If
End If
End If

With TBoxGroup.Parent
.Controls("Lab_" & Format(WhichRow, "00")).Caption = ErrMsg
If ErrMsg = "OK" Then
.Controls("Lab_" & Format(WhichRow, "00")).ForeColor =
RGB(0, 255, 0)
.Controls("Lab_" & Format(WhichRow, "00")).Font.Size = 24
Else
.Controls("Lab_" & Format(WhichRow, "00")).ForeColor =
RGB(255, 0, 0)
.Controls("Lab_" & Format(WhichRow, "00")).Font.Size = 10
End If

OkToContinue = False
' For iRow = 1 To NumberOfRows
If .Controls("Lab_" & Format(WhichRow, "00")).Caption =
"OK" _
Or .Controls("Lab_" & Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
OkToContinue = True
' Exit For
Else
'there's a warning there!
OkToContinue = False
' Exit For
End If
' Next iRow

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

End With
End Sub


Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.

You're going to have to create the same kind of class module using nice names
for each of those rows.

Then you can check to see if each row follows the rules you need.

On 08/26/2010 19:12, noname wrote:
On Aug 27, 5:03 am, Dave wrote:
Maybe...

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
whateverthenextbuttonis.enabled = oktocontinue
Else
eqForm.cmd_AddFactors01.Enabled = False
whateverthenextbuttonis.enabled = false
End If

On 08/26/2010 17:19, noname wrote:

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
Else
If NonNumericCtr 0 Then
ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
Else
If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then
'perfect!
ErrMsg = "OK"
Else
ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
End If
End If
End If

With TBoxGroup.Parent
.Controls("Lab_"& Format(WhichRow, "00")).Caption = ErrMsg
If ErrMsg = "OK" Then
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(0, 255, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 24
Else
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(255, 0, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 10
End If

OkToContinue = False
' For iRow = 1 To NumberOfRows
If .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"OK" _
Or .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
OkToContinue = True
' Exit For
Else
'there's a warning there!
OkToContinue = False
' Exit For
End If
' Next iRow

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

End With
End Sub


Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.


--
Dave Peterson

On Aug 27, 4:14*pm, Dave Peterson wrote:
You're going to have to create the same kind of class module using nice names
for each of those rows.

Then you can check to see if each row follows the rules you need.

On 08/26/2010 19:12, noname wrote:









On Aug 27, 5:03 am, Dave *wrote:
Maybe...

* * * * * *If AtLeastOneEntry = True Then
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * * * *whateverthenextbuttonis.enabled = oktocontinue
* * * * * *Else
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = False
* * * * * * * *whateverthenextbuttonis.enabled = false
* * * * * *End If

On 08/26/2010 17:19, noname wrote:

* * * * * *If AtLeastOneEntry = True Then
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * *Else
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = False
* * * * * *End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
* * * Else
* * * * * If NonNumericCtr *0 Then
* * * * * * * ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
* * * * * Else
* * * * * * * If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then
* * * * * * * * * 'perfect!
* * * * * * * * * ErrMsg = "OK"
* * * * * * * Else
* * * * * * * * * ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
* * * * * * * End If
* * * * * End If
* * * End If

* * * With TBoxGroup.Parent
* * * * * .Controls("Lab_"& *Format(WhichRow, "00")).Caption = ErrMsg
* * * * * If ErrMsg = "OK" Then
* * * * * * *.Controls("Lab_"& *Format(WhichRow, "00"))..ForeColor =
RGB(0, 255, 0)
* * * * * * *.Controls("Lab_"& *Format(WhichRow, "00"))..Font.Size = 24
* * * * *Else
* * * * * * *.Controls("Lab_"& *Format(WhichRow, "00"))..ForeColor =
RGB(255, 0, 0)
* * * * * * *.Controls("Lab_"& *Format(WhichRow, "00"))..Font.Size = 10
* * * * * End If

* * * * * OkToContinue = False
' * * * * For iRow = 1 To NumberOfRows
* * * * * * * If .Controls("Lab_"& *Format(WhichRow, "00")).Caption =
"OK" _
* * * * * * * *Or .Controls("Lab_"& *Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
* * * * * * * * * OkToContinue = True
' * * * * * * * * Exit For
* * * * * * *Else
* * * * * * * * * 'there's a warning there!
* * * * * * * * * OkToContinue = False
' * * * * * * * * Exit For
* * * * * * * End If
' * * * * Next iRow

* * * * * If AtLeastOneEntry = True Then
* * * * * * * eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * Else
* * * * * * * eqForm.cmd_AddFactors01.Enabled = False
* * * * * End If

* * * End With
End Sub

Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.

--
Dave Peterson

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



Hi Dave,

tell me one thing...what is the reason for this loop when you are
already determining WhichRow, WhichCol at the onset? what does the
loop do other than scan all rows for determining which row has atleast
one input? Isn't it then evident that its just determining the Row?
But thats already determined by WhichRow, right?

Can't we avoid this loop? I am just trying to understand the logic
behind it...


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


Best Regards.

I figured that at least one entry in at least one row has to be filled in. This
loop looks for that entry.



On 08/27/2010 12:00, noname wrote:
On Aug 27, 4:14 pm, Dave wrote:
You're going to have to create the same kind of class module using nice names
for each of those rows.

Then you can check to see if each row follows the rules you need.

On 08/26/2010 19:12, noname wrote:









On Aug 27, 5:03 am, Dave wrote:
Maybe...

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
whateverthenextbuttonis.enabled = oktocontinue
Else
eqForm.cmd_AddFactors01.Enabled = False
whateverthenextbuttonis.enabled = false
End If

On 08/26/2010 17:19, noname wrote:

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
Else
If NonNumericCtr 0 Then
ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
Else
If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then
'perfect!
ErrMsg = "OK"
Else
ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
End If
End If
End If

With TBoxGroup.Parent
.Controls("Lab_"& Format(WhichRow, "00")).Caption = ErrMsg
If ErrMsg = "OK" Then
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(0, 255, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 24
Else
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(255, 0, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 10
End If

OkToContinue = False
' For iRow = 1 To NumberOfRows
If .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"OK" _
Or .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
OkToContinue = True
' Exit For
Else
'there's a warning there!
OkToContinue = False
' Exit For
End If
' Next iRow

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

End With
End Sub

Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.

--
Dave Peterson

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



Hi Dave,

tell me one thing...what is the reason for this loop when you are
already determining WhichRow, WhichCol at the onset? what does the
loop do other than scan all rows for determining which row has atleast
one input? Isn't it then evident that its just determining the Row?
But thats already determined by WhichRow, right?

Can't we avoid this loop? I am just trying to understand the logic
behind it...


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


Best Regards.

--
Dave Peterson

On Aug 28, 12:11*am, Dave Peterson wrote:
I figured that at least one entry in at least one row has to be filled in.. *This
loop looks for that entry.

On 08/27/2010 12:00, noname wrote:









On Aug 27, 4:14 pm, Dave *wrote:
You're going to have to create the same kind of class module using nice names
for each of those rows.

Then you can check to see if each row follows the rules you need.

On 08/26/2010 19:12, noname wrote:

On Aug 27, 5:03 am, Dave * *wrote:
Maybe...

* * * * * * If AtLeastOneEntry = True Then
* * * * * * * * eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * * * * whateverthenextbuttonis.enabled = oktocontinue
* * * * * * Else
* * * * * * * * eqForm.cmd_AddFactors01.Enabled = False
* * * * * * * * whateverthenextbuttonis.enabled = false
* * * * * * End If

On 08/26/2010 17:19, noname wrote:

* * * * * * If AtLeastOneEntry = True Then
* * * * * * * * eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * * Else
* * * * * * * * eqForm.cmd_AddFactors01.Enabled = False
* * * * * * End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
* * * *Else
* * * * * *If NonNumericCtr * *0 Then
* * * * * * * *ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
* * * * * *Else
* * * * * * * *If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then
* * * * * * * * * *'perfect!
* * * * * * * * * *ErrMsg = "OK"
* * * * * * * *Else
* * * * * * * * * *ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
* * * * * * * *End If
* * * * * *End If
* * * *End If

* * * *With TBoxGroup.Parent
* * * * * *.Controls("Lab_"& * *Format(WhichRow, "00")).Caption = ErrMsg
* * * * * *If ErrMsg = "OK" Then
* * * * * * * .Controls("Lab_"& * *Format(WhichRow, "00")).ForeColor =
RGB(0, 255, 0)
* * * * * * * .Controls("Lab_"& * *Format(WhichRow, "00")).Font.Size = 24
* * * * * Else
* * * * * * * .Controls("Lab_"& * *Format(WhichRow, "00")).ForeColor =
RGB(255, 0, 0)
* * * * * * * .Controls("Lab_"& * *Format(WhichRow, "00")).Font.Size = 10
* * * * * *End If

* * * * * *OkToContinue = False
' * * * * For iRow = 1 To NumberOfRows
* * * * * * * *If .Controls("Lab_"& * *Format(WhichRow, "00")).Caption =
"OK" _
* * * * * * * * Or .Controls("Lab_"& * *Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
* * * * * * * * * *OkToContinue = True
' * * * * * * * * Exit For
* * * * * * * Else
* * * * * * * * * *'there's a warning there!
* * * * * * * * * *OkToContinue = False
' * * * * * * * * Exit For
* * * * * * * *End If
' * * * * Next iRow

* * * * * *If AtLeastOneEntry = True Then
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = OkToContinue
* * * * * *Else
* * * * * * * *eqForm.cmd_AddFactors01.Enabled = False
* * * * * *End If

* * * *End With
End Sub

Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.

--
Dave Peterson

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

Hi Dave,

tell me one thing...what is the reason for this loop when you are
already determining WhichRow, WhichCol at the onset? what does the
loop do other than scan all rows for determining which row has atleast
one input? Isn't it then evident that its just determining the Row?
But thats already determined by WhichRow, right?

Can't we avoid this loop? I am just trying to understand the logic
behind it...

* * * 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

Best Regards.

--
Dave Peterson

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

Hi Dave,

But doesn't these statements guarantee that?

If LCase(TBoxGroup.Name) Like LCase("tbox_##_##") Then

WhichRow = CLng(Mid(TBoxGroup.Name, 6, 2))
WhichCol = CLng(Right(TBoxGroup.Name, 2))

The 3rd page looks like this and i have shown below what i have
labelled the controls accordingly:


Image ---
txtFactor1 ------- Label1

[TB_01_01] LB_01_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_01_02] [TB_01_03]
[TB_01_04] LB_01_02


Product ---
txtFactor1 ------- Label1

[TB_02_01] LB_02_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_02_02] [TB_02_03]
[TB_02_04] LB_02_02


Sales ---
txtFactor1 ------- Label1

[TB_03_01] LB_03_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_03_02] [TB_03_03]
[TB_03_04] LB_03_02


There are altogether 10 Rows like this and 4 Textboxes on each row as
shown above. The 1st Textbox (TxtFactor1 e.g. TB_01_01) is a textual
control in which a name can be entered for the Factor. The remaining
Textboxes in the row e.g. TB_01_02, TB_01_03, TB_01_04 accept Numeric
scores. There are 2 Labels in each row viz. LB_01_01 & LB_01_02. The
1st Label LB_01_01 is for the TxtFactor1 & the 2nd Label LB_01_02 is
for the remaining Numeric Textboxes viz., TB_01_02, TB_01_03,
TB_01_04.


Validations:
-----------------
1] txtImportance is not taken into account while calculating
CUMULATIVE SCORES.

2] CUMULATIVE SCORE is : TxtTop2Box + TxtBottom2Box < = 103

3] TxtImportance valid range 1 - 100

4] TxtTop2Box valid range 0 - 100

5] TxtBottom2Box valid range 0 - 100

6] All Boxes can be EMPTY or FILLED. As seen from 4] & 5], TxtTop2Box
& TxtBottom2Box can have a ZERO.



I have defined the below Public Constants in a Module :
-----------------------------------------------------------------------------
Public Const NumRows As Long = 10
Public Const NumCols As Long = 4


I have added the same Form_Initialize code to the 2nd Page button
"AddFactors01" which takes me to 3rd page on which these TextBoxes &
Labels are present.

Dim TBoxes1() As New cls_TBOX2

AddFactor1 Button code:
----------------------------------
' Code for Initialising Textboxes

Dim TBoxCount As Long
Dim iRow As Long
Dim iCol As Long

' For Labels
Dim str(2) As String
str(0) = "Enter the name of the factor from your study that
matches this factor"
str(1) = "Enter factor Importance and T2B and Bottom 2 box
rating"


ReDim TBoxes1(1 To NumRows * NumCols)

TBoxCount = 0
For iRow = 1 To NumRows

'fix the row label captions while we're here
Me.Controls("LB_" & Format(iRow, "00") & "_" & Format(1,
"00")).Caption = str(0)
Me.Controls("LB_" & Format(iRow, "00") & "_" & Format(2,
"00")).Caption = str(1)

For iCol = 1 To NumCols

TBoxCount = TBoxCount + 1

Set TBoxes1(TBoxCount).TBoxGroup = Me.Controls("TB_" &
Format(iRow, "00") & "_" & Format(iCol, "00"))

Next iCol

Next iRow



Code in Class Module cls_TBOX1
------------------------------------------------

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 ErrMsg1 As String
Dim ErrMsg2 As String
Dim AtLeastOneEntry As Boolean
Dim OkToContinue As Boolean

MaxTotal = 103

' Figure out row & column of current textbox
If LCase(TBoxGroup.Name) Like LCase("TB_##_##") Then

WhichRow = CLng(Mid(TBoxGroup.Name, 4, 2))

WhichCol = CLng(Right(TBoxGroup.Name, 2))

Else

'unwanted error. textbox not found
MsgBox "Unexpected error has occured!"

Exit Sub

End If


' try to get value of current textbox for testing/validation
AtLeastOneEntry = False

With TBoxGroup.Parent ' i.e. the Frame2 in which we have placed
all controls

For iRow = 1 To NumRows

For iCol = 1 To NumCols

myVal = .Controls("TB_" & 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


ErrMsg1 = ""
ErrMsg2 = ""
EmptyCtr = 0
NumericCtr = 0
NonNumericCtr = 0
NumericCtr = 0

' validate or test the value of current textbox.
For iCol = 1 To NumCols

myVal = .Controls("TB_" & Format(WhichRow, "00") & "_" &
Format(iCol, "00")).Value

'if textbox empty / blank
If myVal = "" Then

EmptyCtr = EmptyCtr + 1

Else

AtLeastOneEntry = True

End If


' if textbox contains characters / string / blanks
If IsNumeric(myVal) = False Then

NonNumericCtr = NonNumericCtr + 1

Else

' textbox contains Numeric value
TotalCtr = TotalCtr + myVal

NumericCtr = NumericCtr + 1

End If

Next iCol

End With


' if all boxes are empty, that means he is not making any entry
in row
If EmptyCtr = NumCols Then

ErrMsg1 = "Enter the name of the factor from your study that
matches this factor"
ErrMsg2 = "Enter factor importance and T2B and Bottom 2 box
rating"

Else

' if there are non-numeric characters entered or row
incomplete
If NonNumericCtr 0 Then

ErrMsg1 = "Factor is non-Numeric"
ErrMsg2 = "Importance, Top2Box, Bottom2Box are Numeric"

Else

'if all boxes filled with numeric data & total <=102
If NumericCtr = NumberOfCols And TotalCtr <= MaxTotal
Then

'perfect!
ErrMsg1 = "OK"
ErrMsg2 = "OK"

Else

'if all boxes filled but total 102
ErrMsg1 = ""
ErrMsg2 = "Cumulative Score of Top2Box, Bottom2Box
should not be more than 102!"

End If

End If

End If


' TboxGroup is the textbox, Parent here means frame2
With TBoxGroup.Parent

' set error message to Label caption now
.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(1,
"00")).Caption = ErrMsg1
.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(2,
"00")).Caption = ErrMsg2

' if Ok msg, then color Font GREEN & Size 24
If ErrMsg1 = "OK" Then

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(1,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(1,
"00")).Font.Size = 24

' if other msg, revert to original color & size
Else

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(1,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(1,
"00")).Font.Size = 10

End If


' if Ok msg, then color Font GREEN & Size 24
If ErrMsg2 = "OK" Then

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(2,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(2,
"00")).Font.Size = 24

' if other msg, revert to original color & size
Else

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(2,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_" & Format(WhichRow, "00") & "_" & Format(2,
"00")).Font.Size = 10

End If


' Check if Label has Ok msg or original default message.
OkToContinue = False

' For iRow = 1 To NumRows
If .Controls("LB_" & Format(WhichRow, "00") & "_" &
Format(1, "00")).Caption = "OK" _
And .Controls("LB_" & Format(WhichRow, "00") & "_" &
Format(2, "00")).Caption = "OK" _
And .Controls("LB_" & Format(WhichRow, "00") & "_" &
Format(1, "00")).Caption = "Enter the name of the factor from your
study that matches this factor" _
And .Controls("LB_" & Format(WhichRow, "00") & "_" &
Format(2, "00")).Caption = "Enter factor importance and T2B and Bottom
2 box rating" Then

OkToContinue = True
' Exit For

Else

' Error!
OkToContinue = False
' Exit For

End If

' Next iRow


' Enable / Disable AddFactors01 button
If AtLeastOneEntry = True Then

eqForm.cmd_AddFactors02.Enabled = OkToContinue

Else

eqForm.cmd_AddFactors02.Enabled = False

End If


End With

End Sub


Code works, but not the validations!

Can you help me to plug in the Validations? I know the above Class
code is not the correct code.

Nope. The change could have been to remove the existing entry.

On 08/27/2010 14:34, noname wrote:
On Aug 28, 12:11 am, Dave wrote:
I figured that at least one entry in at least one row has to be filled in. This
loop looks for that entry.

On 08/27/2010 12:00, noname wrote:









On Aug 27, 4:14 pm, Dave wrote:
You're going to have to create the same kind of class module using nice names
for each of those rows.

Then you can check to see if each row follows the rules you need.

On 08/26/2010 19:12, noname wrote:

On Aug 27, 5:03 am, Dave wrote:
Maybe...

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
whateverthenextbuttonis.enabled = oktocontinue
Else
eqForm.cmd_AddFactors01.Enabled = False
whateverthenextbuttonis.enabled = false
End If

On 08/26/2010 17:19, noname wrote:

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

--
Dave Peterson

=================================
Thanks! I made considerable changes to the Class file and works
according to my needs.

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 = "Enter ratings obtained across 5 points of the
scale."
Else
If NonNumericCtr 0 Then
ErrMsg = "All 5 boxes should be filled, or left blank.
Zero is a valid Entry."
Else
If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then
'perfect!
ErrMsg = "OK"
Else
ErrMsg = "Cumulative Score of all 5 Boxes should not
be more than 102!"
End If
End If
End If

With TBoxGroup.Parent
.Controls("Lab_"& Format(WhichRow, "00")).Caption = ErrMsg
If ErrMsg = "OK" Then
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(0, 255, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 24
Else
.Controls("Lab_"& Format(WhichRow, "00")).ForeColor =
RGB(255, 0, 0)
.Controls("Lab_"& Format(WhichRow, "00")).Font.Size = 10
End If

OkToContinue = False
' For iRow = 1 To NumberOfRows
If .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"OK" _
Or .Controls("Lab_"& Format(WhichRow, "00")).Caption =
"Enter ratings obtained across 5 points of the scale." Then
OkToContinue = True
' Exit For
Else
'there's a warning there!
OkToContinue = False
' Exit For
End If
' Next iRow

If AtLeastOneEntry = True Then
eqForm.cmd_AddFactors01.Enabled = OkToContinue
Else
eqForm.cmd_AddFactors01.Enabled = False
End If

End With
End Sub

Can you please help me with the 2nd Page code, the validations of
which i stated above?
As far as i can see, i would be defining another Class file, and again
invoking it from another button (cmd_AddFactors01) on the page2, which
should open page3 which contains the textboxes to validate. However, i
need the logic. The one you defined right now, i am going through it
slowly, but its a little complicated for my small brain, right now.
Atleast, give me a starter!

Thanks.

--
Dave Peterson

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

Hi Dave,

tell me one thing...what is the reason for this loop when you are
already determining WhichRow, WhichCol at the onset? what does the
loop do other than scan all rows for determining which row has atleast
one input? Isn't it then evident that its just determining the Row?
But thats already determined by WhichRow, right?

Can't we avoid this loop? I am just trying to understand the logic
behind it...

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

Best Regards.

--
Dave Peterson

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

Hi Dave,

But doesn't these statements guarantee that?

If LCase(TBoxGroup.Name) Like LCase("tbox_##_##") Then

WhichRow = CLng(Mid(TBoxGroup.Name, 6, 2))
WhichCol = CLng(Right(TBoxGroup.Name, 2))

--
Dave Peterson

Maybe you can look at the name and use that to do the validations. Without
doing any testing(!):

I don't have time to do all the stuff, but maybe this'll get you closer. But
watch out for typos/syntax errors and even logic errors!

And .controls(...) is that long "tbox_" & format(whichrow, 2) & "_" & ...
stuff. But I didn't type it!

dim mySum as double
dim iCtr as long

with tboxgroup.parent 'the userform
'only look at the 2 rightmost characters
select case right(tboxgroup.name,2)
case is = "01"
'do the validation for column 1
if tboxgroup.value = "" then
'can't be empty if this row is used
'so you'll have to loop through the other tboxes in that row
for iCtr = 2 to 4
if .parent.controls(...) < "" then
'the row is used, so the first column can't be empty
'set your label and all the other stuff you need
exit for 'stop looking
end if
next ictr
end if
case is = "02", "03", "04" 'any of the 0-100 values
'column 2
'check for numbers
if isnumeric(...) = false then
'mark it as an error
else
'check to see if it's between 0 and 100
if cdbl(tboxgroup.value) < 0 _
or cdbl(tboxGroup.value) 100 then
'mark it as an error
end if
end if
end select

'now check to see if the values on the row are less than your max

mySum = 0
for ictr = 2 to 4
if isnumeric(.controls(...).value) then
mysum = mysum + .controls(...).value
end if
next ictr

if mysum 100 _
or mysum < 0 then 'whatever your max is
'mark the row/label as an error
else
'leave the row/label as-is
end if
end with


On 08/27/2010 16:55, noname wrote:

The 3rd page looks like this and i have shown below what i have
labelled the controls accordingly:


Image ---
txtFactor1 ------- Label1

[TB_01_01] LB_01_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_01_02] [TB_01_03]
[TB_01_04] LB_01_02


Product ---
txtFactor1 ------- Label1

[TB_02_01] LB_02_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_02_02] [TB_02_03]
[TB_02_04] LB_02_02


Sales ---
txtFactor1 ------- Label1

[TB_03_01] LB_03_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_03_02] [TB_03_03]
[TB_03_04] LB_03_02


There are altogether 10 Rows like this and 4 Textboxes on each row as
shown above. The 1st Textbox (TxtFactor1 e.g. TB_01_01) is a textual
control in which a name can be entered for the Factor. The remaining
Textboxes in the row e.g. TB_01_02, TB_01_03, TB_01_04 accept Numeric
scores. There are 2 Labels in each row viz. LB_01_01& LB_01_02. The
1st Label LB_01_01 is for the TxtFactor1& the 2nd Label LB_01_02 is
for the remaining Numeric Textboxes viz., TB_01_02, TB_01_03,
TB_01_04.


Validations:
-----------------
1] txtImportance is not taken into account while calculating
CUMULATIVE SCORES.

2] CUMULATIVE SCORE is : TxtTop2Box + TxtBottom2Box< = 103

3] TxtImportance valid range 1 - 100

4] TxtTop2Box valid range 0 - 100

5] TxtBottom2Box valid range 0 - 100

6] All Boxes can be EMPTY or FILLED. As seen from 4]& 5], TxtTop2Box
& TxtBottom2Box can have a ZERO.



I have defined the below Public Constants in a Module :
-----------------------------------------------------------------------------
Public Const NumRows As Long = 10
Public Const NumCols As Long = 4


I have added the same Form_Initialize code to the 2nd Page button
"AddFactors01" which takes me to 3rd page on which these TextBoxes&
Labels are present.

Dim TBoxes1() As New cls_TBOX2

AddFactor1 Button code:
----------------------------------
' Code for Initialising Textboxes

Dim TBoxCount As Long
Dim iRow As Long
Dim iCol As Long

' For Labels
Dim str(2) As String
str(0) = "Enter the name of the factor from your study that
matches this factor"
str(1) = "Enter factor Importance and T2B and Bottom 2 box
rating"


ReDim TBoxes1(1 To NumRows * NumCols)

TBoxCount = 0
For iRow = 1 To NumRows

'fix the row label captions while we're here
Me.Controls("LB_"& Format(iRow, "00")& "_"& Format(1,
"00")).Caption = str(0)
Me.Controls("LB_"& Format(iRow, "00")& "_"& Format(2,
"00")).Caption = str(1)

For iCol = 1 To NumCols

TBoxCount = TBoxCount + 1

Set TBoxes1(TBoxCount).TBoxGroup = Me.Controls("TB_"&
Format(iRow, "00")& "_"& Format(iCol, "00"))

Next iCol

Next iRow



Code in Class Module cls_TBOX1
------------------------------------------------

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 ErrMsg1 As String
Dim ErrMsg2 As String
Dim AtLeastOneEntry As Boolean
Dim OkToContinue As Boolean

MaxTotal = 103

' Figure out row& column of current textbox
If LCase(TBoxGroup.Name) Like LCase("TB_##_##") Then

WhichRow = CLng(Mid(TBoxGroup.Name, 4, 2))

WhichCol = CLng(Right(TBoxGroup.Name, 2))

Else

'unwanted error. textbox not found
MsgBox "Unexpected error has occured!"

Exit Sub

End If


' try to get value of current textbox for testing/validation
AtLeastOneEntry = False

With TBoxGroup.Parent ' i.e. the Frame2 in which we have placed
all controls

For iRow = 1 To NumRows

For iCol = 1 To NumCols

myVal = .Controls("TB_"& 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


ErrMsg1 = ""
ErrMsg2 = ""
EmptyCtr = 0
NumericCtr = 0
NonNumericCtr = 0
NumericCtr = 0

' validate or test the value of current textbox.
For iCol = 1 To NumCols

myVal = .Controls("TB_"& Format(WhichRow, "00")& "_"&
Format(iCol, "00")).Value

'if textbox empty / blank
If myVal = "" Then

EmptyCtr = EmptyCtr + 1

Else

AtLeastOneEntry = True

End If


' if textbox contains characters / string / blanks
If IsNumeric(myVal) = False Then

NonNumericCtr = NonNumericCtr + 1

Else

' textbox contains Numeric value
TotalCtr = TotalCtr + myVal

NumericCtr = NumericCtr + 1

End If

Next iCol

End With


' if all boxes are empty, that means he is not making any entry
in row
If EmptyCtr = NumCols Then

ErrMsg1 = "Enter the name of the factor from your study that
matches this factor"
ErrMsg2 = "Enter factor importance and T2B and Bottom 2 box
rating"

Else

' if there are non-numeric characters entered or row
incomplete
If NonNumericCtr 0 Then

ErrMsg1 = "Factor is non-Numeric"
ErrMsg2 = "Importance, Top2Box, Bottom2Box are Numeric"

Else

'if all boxes filled with numeric data& total<=102
If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then

'perfect!
ErrMsg1 = "OK"
ErrMsg2 = "OK"

Else

'if all boxes filled but total 102
ErrMsg1 = ""
ErrMsg2 = "Cumulative Score of Top2Box, Bottom2Box
should not be more than 102!"

End If

End If

End If


' TboxGroup is the textbox, Parent here means frame2
With TBoxGroup.Parent

' set error message to Label caption now
.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Caption = ErrMsg1
.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Caption = ErrMsg2

' if Ok msg, then color Font GREEN& Size 24
If ErrMsg1 = "OK" Then

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Font.Size = 24

' if other msg, revert to original color& size
Else

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Font.Size = 10

End If


' if Ok msg, then color Font GREEN& Size 24
If ErrMsg2 = "OK" Then

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Font.Size = 24

' if other msg, revert to original color& size
Else

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Font.Size = 10

End If


' Check if Label has Ok msg or original default message.
OkToContinue = False

' For iRow = 1 To NumRows
If .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(1, "00")).Caption = "OK" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(2, "00")).Caption = "OK" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(1, "00")).Caption = "Enter the name of the factor from your
study that matches this factor" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(2, "00")).Caption = "Enter factor importance and T2B and Bottom
2 box rating" Then

OkToContinue = True
' Exit For

Else

' Error!
OkToContinue = False
' Exit For

End If

' Next iRow


' Enable / Disable AddFactors01 button
If AtLeastOneEntry = True Then

eqForm.cmd_AddFactors02.Enabled = OkToContinue

Else

eqForm.cmd_AddFactors02.Enabled = False

End If


End With

End Sub


Code works, but not the validations!

Can you help me to plug in the Validations? I know the above Class
code is not the correct code.


--
Dave Peterson

ps.

If the class modules and the validation for determining whether a button should
be disabled/enabled gets to be too complex, then maybe you could just keep the
nice names and add all the validation to the "ok/next" button(s).

Since you've set up the textboxes with nice naming conventions, you could use
lots of your existing code to just loop though the rows and columns to see if
each row is valid.

If you find an error, you could have another big, bold label that issues the
error/warning -- or even a msgbox???

On 08/27/2010 17:39, Dave Peterson wrote:
Maybe you can look at the name and use that to do the validations. Without
doing any testing(!):

I don't have time to do all the stuff, but maybe this'll get you closer. But
watch out for typos/syntax errors and even logic errors!

And .controls(...) is that long "tbox_" & format(whichrow, 2) & "_" & ...
stuff. But I didn't type it!

dim mySum as double
dim iCtr as long

with tboxgroup.parent 'the userform
'only look at the 2 rightmost characters
select case right(tboxgroup.name,2)
case is = "01"
'do the validation for column 1
if tboxgroup.value = "" then
'can't be empty if this row is used
'so you'll have to loop through the other tboxes in that row
for iCtr = 2 to 4
if .parent.controls(...) < "" then
'the row is used, so the first column can't be empty
'set your label and all the other stuff you need
exit for 'stop looking
end if
next ictr
end if
case is = "02", "03", "04" 'any of the 0-100 values
'column 2
'check for numbers
if isnumeric(...) = false then
'mark it as an error
else
'check to see if it's between 0 and 100
if cdbl(tboxgroup.value) < 0 _
or cdbl(tboxGroup.value) 100 then
'mark it as an error
end if
end if
end select

'now check to see if the values on the row are less than your max

mySum = 0
for ictr = 2 to 4
if isnumeric(.controls(...).value) then
mysum = mysum + .controls(...).value
end if
next ictr

if mysum 100 _
or mysum < 0 then 'whatever your max is
'mark the row/label as an error
else
'leave the row/label as-is
end if
end with


On 08/27/2010 16:55, noname wrote:

The 3rd page looks like this and i have shown below what i have
labelled the controls accordingly:


Image ---
txtFactor1 ------- Label1

[TB_01_01] LB_01_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_01_02] [TB_01_03]
[TB_01_04] LB_01_02


Product ---
txtFactor1 ------- Label1

[TB_02_01] LB_02_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_02_02] [TB_02_03]
[TB_02_04] LB_02_02


Sales ---
txtFactor1 ------- Label1

[TB_03_01] LB_03_01
txtImportance txtTop2Box
txtBottom2Box ------- Label2
[TB_03_02] [TB_03_03]
[TB_03_04] LB_03_02


There are altogether 10 Rows like this and 4 Textboxes on each row as
shown above. The 1st Textbox (TxtFactor1 e.g. TB_01_01) is a textual
control in which a name can be entered for the Factor. The remaining
Textboxes in the row e.g. TB_01_02, TB_01_03, TB_01_04 accept Numeric
scores. There are 2 Labels in each row viz. LB_01_01& LB_01_02. The
1st Label LB_01_01 is for the TxtFactor1& the 2nd Label LB_01_02 is
for the remaining Numeric Textboxes viz., TB_01_02, TB_01_03,
TB_01_04.


Validations:
-----------------
1] txtImportance is not taken into account while calculating
CUMULATIVE SCORES.

2] CUMULATIVE SCORE is : TxtTop2Box + TxtBottom2Box< = 103

3] TxtImportance valid range 1 - 100

4] TxtTop2Box valid range 0 - 100

5] TxtBottom2Box valid range 0 - 100

6] All Boxes can be EMPTY or FILLED. As seen from 4]& 5], TxtTop2Box
& TxtBottom2Box can have a ZERO.



I have defined the below Public Constants in a Module :
-----------------------------------------------------------------------------
Public Const NumRows As Long = 10
Public Const NumCols As Long = 4


I have added the same Form_Initialize code to the 2nd Page button
"AddFactors01" which takes me to 3rd page on which these TextBoxes&
Labels are present.

Dim TBoxes1() As New cls_TBOX2

AddFactor1 Button code:
----------------------------------
' Code for Initialising Textboxes

Dim TBoxCount As Long
Dim iRow As Long
Dim iCol As Long

' For Labels
Dim str(2) As String
str(0) = "Enter the name of the factor from your study that
matches this factor"
str(1) = "Enter factor Importance and T2B and Bottom 2 box
rating"


ReDim TBoxes1(1 To NumRows * NumCols)

TBoxCount = 0
For iRow = 1 To NumRows

'fix the row label captions while we're here
Me.Controls("LB_"& Format(iRow, "00")& "_"& Format(1,
"00")).Caption = str(0)
Me.Controls("LB_"& Format(iRow, "00")& "_"& Format(2,
"00")).Caption = str(1)

For iCol = 1 To NumCols

TBoxCount = TBoxCount + 1

Set TBoxes1(TBoxCount).TBoxGroup = Me.Controls("TB_"&
Format(iRow, "00")& "_"& Format(iCol, "00"))

Next iCol

Next iRow



Code in Class Module cls_TBOX1
------------------------------------------------

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 ErrMsg1 As String
Dim ErrMsg2 As String
Dim AtLeastOneEntry As Boolean
Dim OkToContinue As Boolean

MaxTotal = 103

' Figure out row& column of current textbox
If LCase(TBoxGroup.Name) Like LCase("TB_##_##") Then

WhichRow = CLng(Mid(TBoxGroup.Name, 4, 2))

WhichCol = CLng(Right(TBoxGroup.Name, 2))

Else

'unwanted error. textbox not found
MsgBox "Unexpected error has occured!"

Exit Sub

End If


' try to get value of current textbox for testing/validation
AtLeastOneEntry = False

With TBoxGroup.Parent ' i.e. the Frame2 in which we have placed
all controls

For iRow = 1 To NumRows

For iCol = 1 To NumCols

myVal = .Controls("TB_"& 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


ErrMsg1 = ""
ErrMsg2 = ""
EmptyCtr = 0
NumericCtr = 0
NonNumericCtr = 0
NumericCtr = 0

' validate or test the value of current textbox.
For iCol = 1 To NumCols

myVal = .Controls("TB_"& Format(WhichRow, "00")& "_"&
Format(iCol, "00")).Value

'if textbox empty / blank
If myVal = "" Then

EmptyCtr = EmptyCtr + 1

Else

AtLeastOneEntry = True

End If


' if textbox contains characters / string / blanks
If IsNumeric(myVal) = False Then

NonNumericCtr = NonNumericCtr + 1

Else

' textbox contains Numeric value
TotalCtr = TotalCtr + myVal

NumericCtr = NumericCtr + 1

End If

Next iCol

End With


' if all boxes are empty, that means he is not making any entry
in row
If EmptyCtr = NumCols Then

ErrMsg1 = "Enter the name of the factor from your study that
matches this factor"
ErrMsg2 = "Enter factor importance and T2B and Bottom 2 box
rating"

Else

' if there are non-numeric characters entered or row
incomplete
If NonNumericCtr 0 Then

ErrMsg1 = "Factor is non-Numeric"
ErrMsg2 = "Importance, Top2Box, Bottom2Box are Numeric"

Else

'if all boxes filled with numeric data& total<=102
If NumericCtr = NumberOfCols And TotalCtr<= MaxTotal
Then

'perfect!
ErrMsg1 = "OK"
ErrMsg2 = "OK"

Else

'if all boxes filled but total 102
ErrMsg1 = ""
ErrMsg2 = "Cumulative Score of Top2Box, Bottom2Box
should not be more than 102!"

End If

End If

End If


' TboxGroup is the textbox, Parent here means frame2
With TBoxGroup.Parent

' set error message to Label caption now
.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Caption = ErrMsg1
.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Caption = ErrMsg2

' if Ok msg, then color Font GREEN& Size 24
If ErrMsg1 = "OK" Then

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Font.Size = 24

' if other msg, revert to original color& size
Else

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(1,
"00")).Font.Size = 10

End If


' if Ok msg, then color Font GREEN& Size 24
If ErrMsg2 = "OK" Then

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).ForeColor = RGB(0, 255, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Font.Size = 24

' if other msg, revert to original color& size
Else

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).ForeColor = RGB(255, 0, 0)

.Controls("LB_"& Format(WhichRow, "00")& "_"& Format(2,
"00")).Font.Size = 10

End If


' Check if Label has Ok msg or original default message.
OkToContinue = False

' For iRow = 1 To NumRows
If .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(1, "00")).Caption = "OK" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(2, "00")).Caption = "OK" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(1, "00")).Caption = "Enter the name of the factor from your
study that matches this factor" _
And .Controls("LB_"& Format(WhichRow, "00")& "_"&
Format(2, "00")).Caption = "Enter factor importance and T2B and Bottom
2 box rating" Then

OkToContinue = True
' Exit For

Else

' Error!
OkToContinue = False
' Exit For

End If

' Next iRow


' Enable / Disable AddFactors01 button
If AtLeastOneEntry = True Then

eqForm.cmd_AddFactors02.Enabled = OkToContinue

Else

eqForm.cmd_AddFactors02.Enabled = False

End If


End With

End Sub


Code works, but not the validations!

Can you help me to plug in the Validations? I know the above Class
code is not the correct code.



--
Dave Peterson