View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
J.E. McGimpsey J.E. McGimpsey is offline
external usenet poster
 
Posts: 493
Default Converting to macro

Use the Workbook_SheetChange() event, instead (put it in the
ThisWorkbook module):

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub


In article ,
(angelo325) wrote:

The following great bit of code converts time via a workbook event
from say 13.40 to 13:40.
Can any of you help me out with a macro incorporating this code that
converts data over a specific range repeated in numerous sheets?
I have tried but without success.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Thanks in advance.

Angelo