UserForm Data Correction and Input Boxes

[reberryjr]

I need some help in evaluating UserForm data entries. When the User clicks a cmd button, I have some code that evaluates the controls on the UserForm. When something is incorrect, I want to give the User an opportunity to make the necessary correction, and then have the code continue. Now, when the User makes the correction, I want that updated data to map back to the appropriate field on the UserForm.

I did some Googling and found that an Input Box might be the best solution, so I have the code listed below (2nd set). The code is identifying the error, and throwing the Input Box. However, the value entered into the Input Box isn't mapping back to the UserForm. Instead, it just flows to the next evaluation.

How can I get the data on the Input Box to flow back to the UserForm?

In all reality, my preference is to return the User to the actual field on the UserForm, since I have a lot of code validating the data entered in those controls, and reformatting them. I was using this code (1st set), but I couldn't get the focus to set on the field.

Case Me.txt_Nickname.Value = ""    MsgBox "Please enter a Nickname for the Client."
    If response = vbOK Then Me.txt_Nickname.SetFocus
    Exit Sub
    IsOk = False

Private Sub cmd_Submit_Click()

Dim cGender, cEntryType, cPymtFreq, cPymtStatus, cStatus, cPymtMethod, cRefCat, cFacilitator, cPymtReason, cBiosClientID, cBiosNickname, cStatsClientID As Range
Dim ws1, ws2, ws3, ws4, ws5, ws6, ws7 As Worksheet
Dim DataCorr As Variant
Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True


Set ws1 = ThisWorkbook.Sheets("Management")
Set ws2 = ThisWorkbook.Sheets("Summaries")
Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws4 = ThisWorkbook.Sheets("Stats")
Set ws5 = ThisWorkbook.Sheets("Pymt Tracker")
Set ws6 = ThisWorkbook.Sheets("Financials")
Set ws7 = ThisWorkbook.Sheets("Variables")


LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row
LastRow3 = ws3.Range("E" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row
LastRow5 = ws5.Range("C" & Rows.Count).End(xlUp).Row
LastRow6 = ws6.Range("D" & Rows.Count).End(xlUp).Row
LastRow7 = ws7.Range("J" & Rows.Count).End(xlUp).Row


If Len(Me.txt_Nickname.Value) = 0 Then
    DataCorr = InputBox("Please enter a unique nickname for the Client.", "Data Correction")
    If Response = vbOK Then Me.txt_Nickname.Value = DataCorr.Value
End If


If Len(Me.txt_Email.Value) = 0 Then
    DataCorr = InputBox("Please enter the Client's email address.", "Data Correction")
    If Response = vbOK Then Me.txt_Email.Value = DataCorr.Value
End If

 

[Fluff]

I tend to validate textboxes on exit like

Private Sub txt_Nickname_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(txt_Nickname) = 0 Then
   MsgBox "Please enter a unique nickname for the Client."
   Cancel = True
End If
End Sub

 

[reberryjr]

@Fluff, I have a slew of sub that evaluate data when the User exits a cell. This piece is evaluating the data in the Form after they click a cmd button to move the data to worksheets. I'm trying to avoid some instances where the User might never enter a control, but that control cannot be empty, as well as a few other validations.

Ideally, I'd push them back to the control itself, so that the data validation occurs.

 

[Fluff]

Have you tried my suggestion?

 

[reberryjr]

Yes. I already have that code built in. It would only catch the issue if the User exits the text box without entering data. I'm trying to account for the possibility that they completely bypass the text box.

 

[Fluff]

OK, how about

If Len(Me.txt_Nickname.Value) = 0 Then
    DataCorr = InputBox("Please enter a unique nickname for the Client.", "Data Correction")
    Me.txt_Nickname.Value = DataCorr
End If

 

[reberryjr]

Ok, so that gets the value into the text box, which helps. Is there a way to "force" exiting of that text box, so that the formatting and validation occurs? An example is the same theory, but a different text box.

The txt_Phone has the following code on it. If I were to enter "a" in the Input box, it places an "a" in the text box, but the validation events don't occur. Or, am I just going about this the wrong way? It seems like the better approach, which is what I was trying to do originally (before Googling), is to recognize the error and set focus back to the offending control.

Private Sub txt_Phone_Exit(ByVal Cancel As MSForms.ReturnBoolean)Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True
If IsNumeric(Me.txt_Phone.Value) And Len(Me.txt_Phone.Value) = 10 Then
    Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
Else
    MsgBox "Please enter the Client's phone number."
    If Response = vbYes Then Me.txt_Phone.SetFocus
    Cancel = True
End If
End Sub

 

[Fluff]

If you're going to do it with an exit event, it should be like this

Private Sub txt_Phone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(Me.txt_Phone.Value) And Len(Me.txt_Phone.Value) = 10 Then
    Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
Else
    MsgBox "Please enter the Client's phone number."
    Cancel = True
End If
End Sub

That will leave the cursor in the textbox.
Also you don't need the IsOk as it isn't doing anything.

 

[Fluff]

What you can do is something like this

[COLOR=#0000ff]Option Explicit
Dim AllOk As Long[/COLOR]

Private Sub txt_Phone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If IsNumeric(Me.txt_Phone.Value) And Len(Me.txt_Phone.Value) = 10 Then
       Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
       AllOk = AllOk + 1
       If AllOk = [COLOR=#ff0000]3 [/COLOR]Then CommandButton1.Enabled = True
   Else
       MsgBox "Please enter the Client's phone number."
       Cancel = True
   End If
End Sub
Private Sub txt_Nickname_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If Len(txt_Nickname) = 0 Then
      MsgBox "Please enter a unique nickname for the Client."
      Cancel = True
   Else
      AllOk = AllOk + 1
      If AllOk = [COLOR=#ff0000]3 [/COLOR]Then CommandButton1.Enabled = True
   End If
End Sub
Private Sub txt_email_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   If Len(Me.txt_Email.Value) = 0 Then
      MsgBox "Please enter a unique nickname for the Client."
      Cancel = True
   Else
      AllOk = AllOk + 1
      If AllOk = [COLOR=#ff0000]3 [/COLOR]Then CommandButton1.Enabled = True
   End If
End Sub
Private Sub UserForm_Initialize()
   CommandButton1.Enabled = False
End Sub

The part in blue must go at the very top of the module. Change the values in red to reflect how many controls must have a value.
The commandbutton wont be enabled until all fields have been filled

 

[reberryjr]

Hmmmm...so every piece that I would validate in the cmd_Submit code, would need to have an AllOK +1, then the If AllOk value would be whatever that sum is? So, 47 validation points, would equate to If AllOK = 47 then cmd_Submit.Enabled = True?

I want to make sure that I understand the concept, before I go re-code.