PDA

View Full Version : Solved: Validate Input Data in Userform



tlchan
06-05-2008, 06:33 AM
Hi there,
I had prepared a periodical report for accounts with excesses during the period. The excess amount approved based on the authority level eg BM or ABM. Under remarks column of the report to provide justification for the approved excess amount.

Now I wish to prompt/forced user to input remarks column if the approved excess amount exceeded the authority level prior to adding to the weekly report.

Authorithy Level is as follows:

Position
Excess Type BM ABM
TF 5,000 500
TE 10,000 1,000
SLTE 25,000 1,000
TOD No limit No limit

Attached my working file :banghead:
Thank you

tlchan
06-07-2008, 07:54 PM
I managed to try out using the following include Private Sub CmdAdd_Click() event and works well. Can anyone fine tuned my code ? I marked solved for this post.thanks
:hi:

'****Option selected under BM *****

If Optbm.Value Then
Worksheets("WeeklyOD").Cells(lastrow, 5).Value = Optbm.Caption
If Tbremark.Value = "" Then

If OptTF.Value = True And tbexcessamt.Value > 5000 Then
MsgBox "Exceeded BM's authority limit of RM5,000 under Temp Facility, Please provide justification in remarks !", vbOKOnly + vbExclamation, "BM Temp Facility Limit"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF

Exit Sub

ElseIf OptTE.Value = True And tbexcessamt.Value > 10000 Then
MsgBox "Exceeded BM's authority limit under Temporary Excess, Please provide justification in remarks !", vbOKOnly + vbExclamation, "BM Temp Excess Limit"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub

ElseIf OptSLTE.Value = True And tbexcessamt.Value > 25000 Then
MsgBox "Exceeded BM's authority limit under self-liquidating Temporary Excess, Please provide justification in remarks !", vbOKOnly + vbExclamation, "BM SLTE Limit"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub

ElseIf OptTOD.Value = True And tbexcessamt.Value > 5000 Then
MsgBox "Please provide TOD detail as per approval from relevant authority! eg Aprroval level,Amount & expiry date.", vbOKOnly + vbExclamation, "BM TOD Limit"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub


End If
End If
End If


'****Option selected under ABM *****

If Optabm.Value Then
Worksheets("WeeklyOD").Cells(lastrow, 5).Value = Optabm.Caption
If Tbremark.Value = "" Then

If OptTF.Value = True And tbexcessamt.Value > 500 Then
MsgBox "Exceeded ABM's authority limit of RM500 under Temp Facility, Please provide justification in remarks !", vbOKOnly + vbExclamation, "Temp Facility limit under ABM"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub

ElseIf OptTE.Value = True And tbexcessamt.Value > 1000 Then
MsgBox "Exceeded ABM's authority limit of RM1,000 under temporary Excess, Please provide justification in remarks !", vbOKOnly + vbExclamation, "emp Excess Limit Under ABM"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub

ElseIf OptSLTE.Value = True And tbexcessamt.Value > 500 Then
MsgBox "Exceeded ABM's authority limit under self-liquidating temporary Excess, Please provide justification in remarks !", vbOKOnly + vbExclamation, "SLTE Limit under ABM"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub


ElseIf OptTOD.Value = True And tbexcessamt.Value > 500 Then
MsgBox "Please provide TOD detail as per approval from relevant authority ! eg Aprroval level,Amount & expiry date.", vbOKOnly + vbExclamation, "TOD Limit Under ABM"
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub


End If
End If
End If

Aussiebear
06-07-2008, 08:56 PM
Hi tlchan, I've edited your post, simply to place your code within the VBA tags. You'll notice how much easier it is to read?

You too could do the same simply by using the VBA tags option at the top of the post you are trying to make.

mdmackillop
06-08-2008, 03:21 AM
If Optbm.Value Then
Worksheets("WeeklyOD").Cells(lastrow, 5).Value = Optbm.Caption
If Tbremark.Value = "" Then
If OptTF.Value = True And tbexcessamt.Value > 5000 Then
Msg = "Exceeded BM's authority limit of RM5,000 under Temp Facility, Please provide justification in remarks !"
ttle = "BM Temp Facility Limit"
ElseIf OptTE.Value = True And tbexcessamt.Value > 10000 Then
Msg = "Exceeded BM's authority limit under Temporary Excess, Please provide justification in remarks !"
ttle = "BM Temp Excess Limit"
ElseIf OptSLTE.Value = True And tbexcessamt.Value > 25000 Then
Msg = "Exceeded BM's authority limit under self-liquidating Temporary Excess, Please provide justification in remarks !"
ttle = "BM SLTE Limit"
ElseIf OptTOD.Value = True And tbexcessamt.Value > 5000 Then
Msg = "Please provide TOD detail as per approval from relevant authority! eg Aprroval level,Amount & expiry date."
ttle = "BM TOD Limit"
End If
End If
MsgBox Msg, vbOKOnly + vbExclamation, ttle
Tbremark.SetFocus
Tbremark.BackColor = &H80FFFF
Exit Sub
End If