Andy2011
02-03-2014, 03:39 AM
In the attached example, the user must select an option through 2 checkboxes, be it “yes” or “no” which is associated to a textbox.
The selection may or may not dicatate that the user ‘ough’t to make an entry in the textbox in order to provide some justification for their selection.
What I need is some code to allow for the following to happen…
If the user clicks on the submit button after selecting “yes” and then leaves the associated textbox empty, they will be issued with a msgbox prompt highlighting the fact that they haven’t made an entry into the textbox and inviting them to do so.
The user can of course then choose whether to carry on without making an entry OR the code will force an exit from the process and direct them to the relevant textbox in order to make the entry.
The user would then click submit again and the code would search for the next empty textbox where it ‘believes’ an entry should be made…..and so it would continue until all textboxes have been checked.
The code I have already involves entering a Tag property to the textboxes and the code highlights them ALL in turn using a generic message. When the user identifies that a textbox is to be left blank and the checking process is to continue the code utilising the Tag property identifies the textbox that has already been checked and so will ignore it next time, rather than keep picking it up on one endless loop if it remains blank.
The problem I have at the moment is that the code picks up ALL empty textboxes for validation rather than being dependant on the associated checkbox selection as a flag to validate an empty textbox, for example...
In the first series...
if "yes" is selected then the user should complete the associated textbox. If it's empty then it will get flagged.
In the second series...
If "no" is selected then the user should not complete the associated textbox. If it's empty then it will get ignored.
in the third series...
If "no" is selected then the user should complete the associated textbox. If it's empty then it will get flagged.
and so on... the point is that I would need to specify in the code each possible combination and manually determine which ones get flagged, i.e.
yes + empty - flag
no + empty - flag
yes + empty - ignore
no + empty - ignore
The code I have so far is as follows... can anyone help?
Option Explicit
Dim c As Collection
Private Sub CommandButton1_Click()
Dim x As Variant
For Each x In c
If x.Text = vbNullString Then
If MsgBox("Control '" & Replace(x.Tag, "*", vbNullString) & "' should be completed. Do you want to ignore this?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirm") = vbNo Then
x.SetFocus
Exit Sub
Else
c.Remove (x.Tag)
End If
End If
Next
End Sub
Private Sub UserForm_Initialize()
CheckBox1.SetFocus
LoadCollection
End Sub
Private Sub LoadCollection()
Dim ctrl As Control
If Not c Is Nothing Then
Set c = Nothing
End If
Set c = New Collection
For Each ctrl In Me.Controls
If Left(ctrl.Tag, 1) = "*" Then
c.Add ctrl, ctrl.Tag
End If
Next
End Sub
The selection may or may not dicatate that the user ‘ough’t to make an entry in the textbox in order to provide some justification for their selection.
What I need is some code to allow for the following to happen…
If the user clicks on the submit button after selecting “yes” and then leaves the associated textbox empty, they will be issued with a msgbox prompt highlighting the fact that they haven’t made an entry into the textbox and inviting them to do so.
The user can of course then choose whether to carry on without making an entry OR the code will force an exit from the process and direct them to the relevant textbox in order to make the entry.
The user would then click submit again and the code would search for the next empty textbox where it ‘believes’ an entry should be made…..and so it would continue until all textboxes have been checked.
The code I have already involves entering a Tag property to the textboxes and the code highlights them ALL in turn using a generic message. When the user identifies that a textbox is to be left blank and the checking process is to continue the code utilising the Tag property identifies the textbox that has already been checked and so will ignore it next time, rather than keep picking it up on one endless loop if it remains blank.
The problem I have at the moment is that the code picks up ALL empty textboxes for validation rather than being dependant on the associated checkbox selection as a flag to validate an empty textbox, for example...
In the first series...
if "yes" is selected then the user should complete the associated textbox. If it's empty then it will get flagged.
In the second series...
If "no" is selected then the user should not complete the associated textbox. If it's empty then it will get ignored.
in the third series...
If "no" is selected then the user should complete the associated textbox. If it's empty then it will get flagged.
and so on... the point is that I would need to specify in the code each possible combination and manually determine which ones get flagged, i.e.
yes + empty - flag
no + empty - flag
yes + empty - ignore
no + empty - ignore
The code I have so far is as follows... can anyone help?
Option Explicit
Dim c As Collection
Private Sub CommandButton1_Click()
Dim x As Variant
For Each x In c
If x.Text = vbNullString Then
If MsgBox("Control '" & Replace(x.Tag, "*", vbNullString) & "' should be completed. Do you want to ignore this?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirm") = vbNo Then
x.SetFocus
Exit Sub
Else
c.Remove (x.Tag)
End If
End If
Next
End Sub
Private Sub UserForm_Initialize()
CheckBox1.SetFocus
LoadCollection
End Sub
Private Sub LoadCollection()
Dim ctrl As Control
If Not c Is Nothing Then
Set c = Nothing
End If
Set c = New Collection
For Each ctrl In Me.Controls
If Left(ctrl.Tag, 1) = "*" Then
c.Add ctrl, ctrl.Tag
End If
Next
End Sub