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.