VBA Problem?

[PeCoNe]

Hallo,

I am trying to solve following:

Sheet Monitor

BEG END IEX IEX
Time Time Date Time
15:15:00 17:30:00 31-12-2012 15:14:36 <= format cells is *13:30:55
Fields are named as headers

VBA Project Blad1 (Monitor)

Private Sub Worksheet_Calculate()
With Sheets("Monitor")
If .Range("IEXtime") < .Range("BEGtime") Then
Exit Sub
End If

IEXtime is less than BEGtime and still program does not exit sub.


Why?

 

[GS]

PeCoNe has brought this to us :

Hallo,

I am trying to solve following:

Sheet Monitor

BEG END IEX IEX
Time Time Date Time
15:15:00 17:30:00 31-12-2012 15:14:36 <= format cells is *13:30:55
Fields are named as headers

VBA Project Blad1 (Monitor)

Private Sub Worksheet_Calculate()
With Sheets("Monitor")
If .Range("IEXtime") < .Range("BEGtime") Then
Exit Sub
End If

IEXtime is less than BEGtime and still program does not exit sub.


Why?

This event fires every time a calculation is made. That's determined by
the number of formulas that autocalc, and so will fire this event
recursively. This will make it appear as though it's not exiting.

Otherwise, why are you using this event in this way? What the heck are
you trying to accomplish?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

Sheet Monitor
BEG END IEX IEX Time Time Date Time 15:15:00 17:30:00
31-12-2012 15:14:36 [....]
Private Sub Worksheet_Calculate()
With Sheets("Monitor")
If .Range("IEXtime") < .Range("BEGtime") Then
Exit Sub
End If
IEXtime is less than BEGtime and still program does not exit sub.
Why?

The expression will return False if Range("IEXTime") is type Text(!) and
Range("BEGtime") is type Numeric. And of course, it might return False if
things are not as they seem.

Try adding the following debugging statements:

With .Range("IEXtime")
Debug.Print .Address(external:=True)
Debug.Print WorksheetFunction.IsNumber(.Value)
Debug.Print Chr(34) & .Value & Chr(34)
End With

With the last statement, we are looking for leading and trailing spaces that
might alter Excel's interpretation of the cell content.

Do the same for .Range("BEGtime").

To see Debug.Print results, press ctrl+G to open the Immediate Window.

PS: If you are simply implementation data entry validation, you might
consider using the Data Validation feature instead of an event macro.