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/

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/

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/

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

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