Problem with Format function
 

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

I tried that and it still gives me null strings.

"Mike" wrote:

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

Post what you have

"TimK" wrote:

I tried that and it still gives me null strings.

"Mike" wrote:

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

Here is an edited excerpt of my code:

Public gate As Boolean
Public formtype As Boolean
Public r1, c1, col, row As Integer
Const sTblPrefix = "=[ODCmgt.xla]DataFields!"
Sub newpar()
'
' this is called when the NEW PAR button is clicked
'
Dim z As Long
Dim rng As Range
x1Down = -4121
Dim mydate As Date

r1 = ActiveCell.Cells.row 'save where cursor is in spreadsheet
c1 = ActiveCell.Cells.Column
..
..
..
If Cells(2, 1).Value = Empty Then ' if this is the first par to
enter
ActiveSheet.Cells(2, 1).Select ' set focus
Cells(2, 1).Value = 1 ' set par #
GoTo skp ' and get on with it
End If
Range("A1").End(x1Down).Select ' jump to last format(row,0,0,0) in
spreadsheet
z = ActiveCell.Cells.row + 1 ' calc next empty line's column
ActiveCell.Offset(1, 0).Select 'jump the cursor there
skp: col = 1 ' save location of new line's
coordinates
row = ActiveCell.Cells.row
Load NewPARForm ' load the form first
'Init the controlsource properties for the new spreadsheet line
With NewPARForm
.TextBox1.ControlSource = address(col, row)
If ActiveCell.Value < 1 Then
z = Cells(row - 1, col).Value + 1
.TextBox1.Value = z 'init w/ next sequential par#
End If
ActiveCell.Offset(0, 1).Select 'Move cursor to the product field
.ComboBox1.SetFocus ' need to move cursor to cell
.ComboBox1.ControlSource = address(col + 1, row)
.TextBox3.ControlSource = address(col + 2, row)
.ComboBox2.ControlSource = address(col + 3, row)
.TextBox5.ControlSource = address(col + 4, row)
.TextBox5.Value = Format(Date, "[$-409]mmddyy;@") ' initialize the
date entered field <<<------------------------------
.ComboBox3.ControlSource = address(col + 5, row)
.ComboBox4.ControlSource = address(col + 6, row)

The indicated line is where I tried your suggestion. Whey I ran this with a
breakpoint at the following line, the .Textbox5.value field showed "" as its
content.

"Mike" wrote:

Post what you have

"TimK" wrote:

I tried that and it still gives me null strings.

"Mike" wrote:

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

Not sure try this
.TextBox5.Value = Format(Address(col + 4, row), "[$-409]mmddyy;@")

"TimK" wrote:

Here is an edited excerpt of my code:

Public gate As Boolean
Public formtype As Boolean
Public r1, c1, col, row As Integer
Const sTblPrefix = "=[ODCmgt.xla]DataFields!"
Sub newpar()
'
' this is called when the NEW PAR button is clicked
'
Dim z As Long
Dim rng As Range
x1Down = -4121
Dim mydate As Date

r1 = ActiveCell.Cells.row 'save where cursor is in spreadsheet
c1 = ActiveCell.Cells.Column
.
.
.
If Cells(2, 1).Value = Empty Then ' if this is the first par to
enter
ActiveSheet.Cells(2, 1).Select ' set focus
Cells(2, 1).Value = 1 ' set par #
GoTo skp ' and get on with it
End If
Range("A1").End(x1Down).Select ' jump to last format(row,0,0,0) in
spreadsheet
z = ActiveCell.Cells.row + 1 ' calc next empty line's column
ActiveCell.Offset(1, 0).Select 'jump the cursor there
skp: col = 1 ' save location of new line's
coordinates
row = ActiveCell.Cells.row
Load NewPARForm ' load the form first
'Init the controlsource properties for the new spreadsheet line
With NewPARForm
.TextBox1.ControlSource = address(col, row)
If ActiveCell.Value < 1 Then
z = Cells(row - 1, col).Value + 1
.TextBox1.Value = z 'init w/ next sequential par#
End If
ActiveCell.Offset(0, 1).Select 'Move cursor to the product field
.ComboBox1.SetFocus ' need to move cursor to cell
.ComboBox1.ControlSource = address(col + 1, row)
.TextBox3.ControlSource = address(col + 2, row)
.ComboBox2.ControlSource = address(col + 3, row)
.TextBox5.ControlSource = address(col + 4, row)
.TextBox5.Value = Format(Date, "[$-409]mmddyy;@") ' initialize the
date entered field <<<------------------------------
.ComboBox3.ControlSource = address(col + 5, row)
.ComboBox4.ControlSource = address(col + 6, row)

The indicated line is where I tried your suggestion. Whey I ran this with a
breakpoint at the following line, the .Textbox5.value field showed "" as its
content.

"Mike" wrote:

Post what you have

"TimK" wrote:

I tried that and it still gives me null strings.

"Mike" wrote:

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?

Problem with Format function
 

I got it..... I used the following format.

..TextBox5.Value = Format(Date, "mm/dd/yy")

That returned non-null strings 3 out of 3 tries. Thanks for your help.

"Mike" wrote:

Not sure try this
.TextBox5.Value = Format(Address(col + 4, row), "[$-409]mmddyy;@")

"TimK" wrote:

Here is an edited excerpt of my code:

Public gate As Boolean
Public formtype As Boolean
Public r1, c1, col, row As Integer
Const sTblPrefix = "=[ODCmgt.xla]DataFields!"
Sub newpar()
'
' this is called when the NEW PAR button is clicked
'
Dim z As Long
Dim rng As Range
x1Down = -4121
Dim mydate As Date

r1 = ActiveCell.Cells.row 'save where cursor is in spreadsheet
c1 = ActiveCell.Cells.Column
.
.
.
If Cells(2, 1).Value = Empty Then ' if this is the first par to
enter
ActiveSheet.Cells(2, 1).Select ' set focus
Cells(2, 1).Value = 1 ' set par #
GoTo skp ' and get on with it
End If
Range("A1").End(x1Down).Select ' jump to last format(row,0,0,0) in
spreadsheet
z = ActiveCell.Cells.row + 1 ' calc next empty line's column
ActiveCell.Offset(1, 0).Select 'jump the cursor there
skp: col = 1 ' save location of new line's
coordinates
row = ActiveCell.Cells.row
Load NewPARForm ' load the form first
'Init the controlsource properties for the new spreadsheet line
With NewPARForm
.TextBox1.ControlSource = address(col, row)
If ActiveCell.Value < 1 Then
z = Cells(row - 1, col).Value + 1
.TextBox1.Value = z 'init w/ next sequential par#
End If
ActiveCell.Offset(0, 1).Select 'Move cursor to the product field
.ComboBox1.SetFocus ' need to move cursor to cell
.ComboBox1.ControlSource = address(col + 1, row)
.TextBox3.ControlSource = address(col + 2, row)
.ComboBox2.ControlSource = address(col + 3, row)
.TextBox5.ControlSource = address(col + 4, row)
.TextBox5.Value = Format(Date, "[$-409]mmddyy;@") ' initialize the
date entered field <<<------------------------------
.ComboBox3.ControlSource = address(col + 5, row)
.ComboBox4.ControlSource = address(col + 6, row)

The indicated line is where I tried your suggestion. Whey I ran this with a
breakpoint at the following line, the .Textbox5.value field showed "" as its
content.

"Mike" wrote:

Post what you have

"TimK" wrote:

I tried that and it still gives me null strings.

"Mike" wrote:

Not sure what you wabt Date to look like but try this
format's date as 061407
Format(Date, "[$-409]mmddyy;@")

"TimK" wrote:

I have the following line of code in my add-in.

obj.textbox1.value = format(Date)

I find that the format function often produces inconsistent results, i.e.
sometimes it produces null strings. Anyone know why?