Cells formated as custom numerics to text box error.

G

Guest

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.
 
M

Mike Fogleman

Try:

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

Mike F
 
G

Guest

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
 
M

Mike Fogleman

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

Mike F
Francis Brown said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Guest

Your Code works Fine.

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

Now using your code.

Francis.

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

Mike F
Francis Brown said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Text to Textbox value by looping through a data range. 5
Reg:Macro needed to pick up only distinct values but it picks up allthe values 4
Search and Replace a Spacific Character with Conditional Formating 17
Dim IndxNum As Long & Dim c As Variant 7
List unique items 4
Code condition 3
Copy code failing 2
Error 1004 - again 3

Top