Why this code works and other do not? Newbie here.

  • Thread starter Thread starter Steve2005black
  • Start date Start date
S

Steve2005black

Hi

I am a newbie to VBA.

I have a UserForm with a Calendar and a TextBox. I wrote a procedure to
check the number entered in the TextBox do not exceed a limit. The
following function triggers the MsgBox and resets the TextBox1.Value to
0, even when the TextBox1.Value DO NOT exceed the limit set.

----
Private Sub TextBox1_AfterUpdate()

If TextBox1.Value > ((ActiveSheet.Range("$C$5").Value + 1)) -
Calendar1.Day Then
MsgBox "Leave cannot exceed " & ((ActiveSheet.Range("$C$5").Value +
1)) - Calendar1.Day & " days!", vbCritical, "Wrong number of leave"

TextBox1.Value = 0

End If

End Sub
----

Finally after a couple of hours I came with this solution and it works
the way I need. However I am curious why the first code didn't work.
Can someone please guide me?


----
Private Sub TextBox1_AfterUpdate()
Dim DateLimit As Integer
DateLimit = TextBox1.Value
If DateLimit > ((ActiveSheet.Range("$C$5").Value + 1)) -
Calendar1.Day Then
MsgBox "Leave cannot exceed " & ((ActiveSheet.Range("$C$5").Value +
1)) - Calendar1.Day & " days!", vbCritical, "Wrong number of leave"

TextBox1.Value = 0

End If

End Sub
 
Hi Steve,

Your first version works for me with an explicit conversion of TextBox1's
string value to an integer:

Private Sub TextBox1_AfterUpdate()

If CInt(TextBox1.Value) > (ActiveSheet.Range("$C$5").Value + 1) _
- Calendar1.Day Then
MsgBox "Leave cannot exceed " & _
(ActiveSheet.Range("$C$5").Value + 1) _
- Calendar1.Day & " days!", vbCritical, _
"Wrong number of leave"

TextBox1.Value = 0

End If
End Sub

Your second version makes an implicit conversion by assigning the TextBox
value to the DateValue variable which you have dimmed as integer.
 
Are you sure your calculation is correct? Is C5 definitely a numerical
value?

Try adding Range("A1").value= (ActiveSheet.Range("$C$5").Value + 1) -
Calendar1.Day before the IF statement to double check that the calculated
value is what you expect.
 
Back
Top