Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Altering cell contents
Hi,
What I am trying to achieve is to be able to alter the contents of a particular cell. If I type 2300 into the cell I want the cell to automatically update with 23:00 The code I have so far is: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TargetRow As String Dim TargetColumn As String Dim IntValue As String Dim TargetHours As String Dim TargetMins As String TargetRow = Target.Row TargetColumn = Target.Column IntValue = Format(Target.Worksheet.Cells(Target.Row, Target.Column).Value, "0000") TargetHours = Left(IntValue, 2) TargetMins = Right(IntValue, 2) Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" & TargetMins End Sub the trouble is that the cell gets updated with 00:00 all the time. When I try to format the cell (after it has been formatted) it is always on a custom format of hh:mm which is OK but I am thinking the formatting may be at fault somewhere?? I am using office 2000. Any help is appreciated. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Altering cell contents
Melissa,
The basic problem is that you need to convert 2300 into time and format as such, but your code seems overly complex as well. Try this Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False On Error GoTo wb_exit Target.Value = Target.Value / 24 / 100 Target.NumberFormat = "hh:mm" wb_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "MelissaCreek " wrote in message ... Hi, What I am trying to achieve is to be able to alter the contents of a particular cell. If I type 2300 into the cell I want the cell to automatically update with 23:00 The code I have so far is: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TargetRow As String Dim TargetColumn As String Dim IntValue As String Dim TargetHours As String Dim TargetMins As String TargetRow = Target.Row TargetColumn = Target.Column IntValue = Format(Target.Worksheet.Cells(Target.Row, Target.Column).Value, "0000") TargetHours = Left(IntValue, 2) TargetMins = Right(IntValue, 2) Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" & TargetMins End Sub the trouble is that the cell gets updated with 00:00 all the time. When I try to format the cell (after it has been formatted) it is always on a custom format of hh:mm which is OK but I am thinking the formatting may be at fault somewhere?? I am using office 2000. Any help is appreciated. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Altering cell contents
Melissa,
My previous response will turn 2350 to 23:30, that is the last 2 digits wil be a treated as a fraction of an hour not as the number of minutes. If it should be the latter, use this code Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim nHours As Long Dim nMins As Long Application.EnableEvents = False On Error GoTo wb_exit nHours = Int(Target.Value / 100) nMins = Target.Value - nHours * 100 Target.Value = nHours / 24 + nMins / 1440 Target.NumberFormat = "hh:mm" wb_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Melissa, The basic problem is that you need to convert 2300 into time and format as such, but your code seems overly complex as well. Try this Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False On Error GoTo wb_exit Target.Value = Target.Value / 24 / 100 Target.NumberFormat = "hh:mm" wb_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "MelissaCreek " wrote in message ... Hi, What I am trying to achieve is to be able to alter the contents of a particular cell. If I type 2300 into the cell I want the cell to automatically update with 23:00 The code I have so far is: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TargetRow As String Dim TargetColumn As String Dim IntValue As String Dim TargetHours As String Dim TargetMins As String TargetRow = Target.Row TargetColumn = Target.Column IntValue = Format(Target.Worksheet.Cells(Target.Row, Target.Column).Value, "0000") TargetHours = Left(IntValue, 2) TargetMins = Right(IntValue, 2) Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" & TargetMins End Sub the trouble is that the cell gets updated with 00:00 all the time. When I try to format the cell (after it has been formatted) it is always on a custom format of hh:mm which is OK but I am thinking the formatting may be at fault somewhere?? I am using office 2000. Any help is appreciated. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Altering cell contents
The reason your values were going to 0 is that each time you wrote the value
back to the cell, the event was called. Bob stopped that with the "application.enableevents = false" line. He wrote to the cell, and then reenabled events for the next time. Bob's code will convert 2315 to 23:09:00 (hh:mm:ss). I thought that you would want to enter 2315 to get 23:15:00 (hh:mm:ss). And I had concerns why this is in a workbook_sheetchange event. In most cases I've seen, users want to limit this kind of change to a single worksheet (and maybe a single range). If that seems reasonable, this code would go in that worksheet's module. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub On Error GoTo errHandler: If IsNumeric(Target.Value) Then Select Case Target.Value Case Is < 1: 'do nothing Case Is 2359: 'do nothing Case Else Application.EnableEvents = False Target.Value = Format(Target.Value, "00:00") & ":00" Target.NumberFormat = "hh:mm" End Select End If errHandler: Application.EnableEvents = True End Sub (I limited my range to column A. Change that to what you want--along with the numberformat, too.) "MelissaCreek <" wrote: Hi, What I am trying to achieve is to be able to alter the contents of a particular cell. If I type 2300 into the cell I want the cell to automatically update with 23:00 The code I have so far is: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TargetRow As String Dim TargetColumn As String Dim IntValue As String Dim TargetHours As String Dim TargetMins As String TargetRow = Target.Row TargetColumn = Target.Column IntValue = Format(Target.Worksheet.Cells(Target.Row, Target.Column).Value, "0000") TargetHours = Left(IntValue, 2) TargetMins = Right(IntValue, 2) Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" & TargetMins End Sub the trouble is that the cell gets updated with 00:00 all the time. When I try to format the cell (after it has been formatted) it is always on a custom format of hh:mm which is OK but I am thinking the formatting may be at fault somewhere?? I am using office 2000. Any help is appreciated. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Altering cell contents
Can the code above be used in the worksheet change event so that an
cell's value can be changed within a certain range. I have tried but an error message comes up saying wit me.range("A:A").... is not valid. Thanks Rac -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) | |||
Altering UI Programatically | Excel Programming |