Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I correct ##### error in a cell formated as text? | Excel Discussion (Misc queries) | |||
Text formated cells with numbers | New Users to Excel | |||
Use custom [mm] formated value that is displayed in a calculation | Excel Worksheet Functions | |||
Cells formated as text do not always display properly | Excel Discussion (Misc queries) | |||
Cells formated as numbers are calculating like text | Excel Discussion (Misc queries) |