Userform Answer Validation Problem

[Guest]

Hello All,

I have a userform that tabulates the number of times a task was required
versus the number of times the task was actually done. A lot of my users have
been transposing numbers so I thought I would validate that the required
value is equal or greater than the done value. So I wrote the following line
of code and attached it to the Exit event for the field.

If QCMonitor.QC1Done.Value > QCMonitor.QC1Req.Value Then
Response = MsgBox("Your 'Done value' is greater that your 'Required value.'"
)Cancel = True
QCMonitor.QC1Req.SetFocus
End If

Problem is I can't get the cursor to go back to the Required field. I keep
getting a "Run-Time Error '-2147467259 (80004005)': Unspecified Error"

Any ideas on how to fix this?

Will

 

[Bob Phillips]

This works fine for me

Private Sub QC1Req_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim response
With Me
With QC1Req
If CDbl(.Text) < CDbl(QC1Done.Text) Then
response = MsgBox("Your 'Done value' is greater that your
'Required value.'")
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End With

End Sub

 

[Tom Ogilvy]

If this is the exit event of the Required field, then setting cancel = True
should cause the cursor not to leave the field/textbox. Perhaps you could
query in your msgbox "Switch values?" and just switch them if the response
is positive.

 

[Guest]

That's a great idea!, . . .

but ahhh, ahem, . . . .

how would I do that?

: )

Will

 

[Guest]

I tried this but still get the same error message. The problem seems to
revolve around the SetFocus.

Weird!

 

[Guest]

I wrote the following for another set of questions. I just can't figure how
to get the code to simply switch the values.

Private Sub QC2Done_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Msg, Style, Title, Response, MyString
Msg = "Your 'Done value' is greater that your 'Required value.'" _
& Chr(13) & Chr(13) _
& "Ensure that the 'Number of Times Done' is less than or equal to
your'Number of Times Required.'" _
& Chr(13) & Chr(13) & _
"It is possible that you merely switched the values." _
& Chr(13) & Chr(13) _
& "Click 'Yes' to switch your numbers." _
& Chr(13) & Chr(13) _
& "Click 'No' to reset the answers to zero."
Style = vbYesNo + vbDefaultButton1 ' Define the buttons and its default
Title = "Input Value Error" ' Defines the title.
If QCMonitor.QC2Done.Value > QCMonitor.QC2Req.Value Then
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' This is just a placeholder until I can
figure out the switch!
MsgBox "Your numbers were switched!" 'Confirms that the user
switched values
Else ' User chose No.
Cancel = True
QCMonitor.QC2Done.Value = 0 ' Resets values to Zero
QCMonitor.QC2Req.Value = 0
End If
End If
End Sub

How the heck do I get it to switch values of the 2 answers!??

 

[Tom Ogilvy]

QCMonitor.QC2Done.Value > QCMonitor.QC2Req.Value

v = QCMonitor.QC2Done.Value
QCMonitor.QC2Done.Value = QCMonitor.QC2Req.Value
QCMonitor.QC2Req.Value = v