VBA-code "Time" and format tt:mm

[blaksnm]

Hi guys
I have this routine;

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 2/26/2020 10:33:36 PM EST
If Target.Column = 11 Or 12 Then
Cancel = True
Target.Value = Time
End If
End Sub

The routine "works" for other columns than the expected 11 og 12 - why?
(innstead of target.column I would use some kind of "target.range" (ex for range("M4:N10") - how is this done?
I should have ajusted the result of the value to only be TT:MM - not TT:MM:SS - how do I do that?
Thanks for help

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("M4:N10")) Is Nothing Then
      Cancel = True
      Target.Value = Time
      Target.NumberFormat = "hh:mm"
   End If
End Sub

 

[blaksnm]

Thanks for tips, but the value is still tt:mm:ss - the rest is working all right

 

[Fluff]

Time is measured in hh:mm:ss so I don't understand. The code I suggested should format it to just show Hours & minutes.
You may need to adjust the number format for your locale.

 

[blaksnm]

Well
I have at "test" in a formula that compares to time-values.
When theese two values are not alike the formula responds a "message"
I dont want the values to be evalueted on more than tt:mm (Seconds are not wanted)

 

[Fluff]

In that case use

Target.Value = Int(Time * 1440) / 1440

 

[blaksnm]

Wow!
That did the trick
Thanks!

 

[Fluff]

You're welcome & thanks for the feedback