Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why this code works and other do not? Newbie here.
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 ----- TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why this code works and other do not? Newbie here.
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. --- Regards, Norman wrote in message oups.com... 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 ----- TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why this code works and other do not? Newbie here.
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. -- Ian -- wrote in message oups.com... 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 ----- TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code works for any WBK except the PMW | Excel Programming | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) | |||
VBA Newbie: Help with Do Loop code | Excel Programming | |||
Newbie : How to sum cells via VBA code. | Excel Programming | |||
Newbie : Autofilter thru code ? | Excel Programming |