Cells formated as custom numerics to text box error.
 

In the following code:

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
boxlength = Len(name) + Len(datee) + Len(start) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & length & " at " & start & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c
End Sub

The cells copied to the variable called start and length are formated on the
worksheet as : Custom hh:mm:ss
by the time they are in the text box the excel equivilent numerical value is
displayed.

I would like them added to the text box in the same format as they are
viewed on the work sheet so as the report being constructed makes sence.

Can anyone help me do this.

It is interesting to note there is no such trouble with the "datee" formated
as date 04-Mar-97 on the worksheet and it retains an appropriate format when
carried to the text box.

Again thanks for any help in advance and thanks to the members of this forum
who have helped me so far.

Francis.

Cells formated as custom numerics to text box error.
 

Try:

start = Format(c.Offset(, 4).Value, "hh:mm:ss"
length = Format(c.Offset(, 6).Value, "hh:mm:ss"

Mike F
"Francis Brown" wrote in message
...
In the following code:

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
boxlength = Len(name) + Len(datee) + Len(start) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & length & " at " & start & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c
End Sub

The cells copied to the variable called start and length are formated on
the
worksheet as : Custom hh:mm:ss
by the time they are in the text box the excel equivilent numerical value
is
displayed.

I would like them added to the text box in the same format as they are
viewed on the work sheet so as the report being constructed makes sence.

Can anyone help me do this.

It is interesting to note there is no such trouble with the "datee"
formated
as date 04-Mar-97 on the worksheet and it retains an appropriate format
when
carried to the text box.

Again thanks for any help in advance and thanks to the members of this
forum
who have helped me so far.

Francis.

Cells formated as custom numerics to text box error.
 

Came up with this solution for my problem.

Is there a more elegent way.

The Hour, Minute and second function cant seem to be called in vba.

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C9").Resize(finalrow - 8, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
c.Offset(, 8).Formula = "=hour(RC[-4])"
starth = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-4])"
startm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-4])"
starts = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=hour(RC[-2])"
lengthh = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-2])"
lengthm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-2])"
lengths = c.Offset(, 8).Value
boxlength = Len(name) + Len(datee) + Len(starth) + Len(startm) +
Len(starts) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & lengthh & ":" & lengthm & ":" & lengths & " at " & starth &
":" & startm & ":" & starts & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c


End Sub

"Francis Brown" wrote:

In the following code:

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
boxlength = Len(name) + Len(datee) + Len(start) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & length & " at " & start & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c
End Sub

The cells copied to the variable called start and length are formated on the
worksheet as : Custom hh:mm:ss
by the time they are in the text box the excel equivilent numerical value is
displayed.

I would like them added to the text box in the same format as they are
viewed on the work sheet so as the report being constructed makes sence.

Can anyone help me do this.

It is interesting to note there is no such trouble with the "datee" formated
as date 04-Mar-97 on the worksheet and it retains an appropriate format when
carried to the text box.

Again thanks for any help in advance and thanks to the members of this forum
who have helped me so far.

Francis.

Cells formated as custom numerics to text box error.
 

Did my Format solution not work for you? It works for me with a valid
date/time serial number.

Mike F
"Francis Brown" wrote in message
...
Came up with this solution for my problem.

Is there a more elegent way.

The Hour, Minute and second function cant seem to be called in vba.

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C9").Resize(finalrow - 8, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
c.Offset(, 8).Formula = "=hour(RC[-4])"
starth = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-4])"
startm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-4])"
starts = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=hour(RC[-2])"
lengthh = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-2])"
lengthm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-2])"
lengths = c.Offset(, 8).Value
boxlength = Len(name) + Len(datee) + Len(starth) + Len(startm) +
Len(starts) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & lengthh & ":" & lengthm & ":" & lengths & " at " & starth &
":" & startm & ":" & starts & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c


End Sub

"Francis Brown" wrote:

In the following code:

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
boxlength = Len(name) + Len(datee) + Len(start) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & length & " at " & start & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c
End Sub

The cells copied to the variable called start and length are formated on
the
worksheet as : Custom hh:mm:ss
by the time they are in the text box the excel equivilent numerical value
is
displayed.

I would like them added to the text box in the same format as they are
viewed on the work sheet so as the report being constructed makes sence.

Can anyone help me do this.

It is interesting to note there is no such trouble with the "datee"
formated
as date 04-Mar-97 on the worksheet and it retains an appropriate format
when
carried to the text box.

Again thanks for any help in advance and thanks to the members of this
forum
who have helped me so far.

Francis.

Cells formated as custom numerics to text box error.
 

Your Code works Fine.

I Just hadnt read the post at the time I wrote my own.

Now using your code.

Francis.

"Mike Fogleman" wrote:

Did my Format solution not work for you? It works for me with a valid
date/time serial number.

Mike F
"Francis Brown" wrote in message
...
Came up with this solution for my problem.

Is there a more elegent way.

The Hour, Minute and second function cant seem to be called in vba.

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C9").Resize(finalrow - 8, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
c.Offset(, 8).Formula = "=hour(RC[-4])"
starth = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-4])"
startm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-4])"
starts = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=hour(RC[-2])"
lengthh = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=minute(RC[-2])"
lengthm = c.Offset(, 8).Value
c.Offset(, 8).Formula = "=second(RC[-2])"
lengths = c.Offset(, 8).Value
boxlength = Len(name) + Len(datee) + Len(starth) + Len(startm) +
Len(starts) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & lengthh & ":" & lengthm & ":" & lengths & " at " & starth &
":" & startm & ":" & starts & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c


End Sub

"Francis Brown" wrote:

In the following code:

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim boxlength As Single
ActiveWorkbook.save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text
startPos = 19
For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
boxlength = Len(name) + Len(datee) + Len(start) + Len(length) + 50
tb.Characters(start:=startPos, _
length:=boxlength).Text = Chr(10) & "" & name & " was in " &
c.Value & _
" for " & length & " at " & start & " on " & datee & "."

startPos = startPos + boxlength + 1
Next c
End Sub

The cells copied to the variable called start and length are formated on
the
worksheet as : Custom hh:mm:ss
by the time they are in the text box the excel equivilent numerical value
is
displayed.

I would like them added to the text box in the same format as they are
viewed on the work sheet so as the report being constructed makes sence.

Can anyone help me do this.

It is interesting to note there is no such trouble with the "datee"
formated
as date 04-Mar-97 on the worksheet and it retains an appropriate format
when
carried to the text box.

Again thanks for any help in advance and thanks to the members of this
forum
who have helped me so far.

Francis.