Problem with Format function

[Guest]

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?

 

[Guest]

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

 

[Guest]

I tried that and it still gives me null strings.

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

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?

 

[Guest]

Post what you have

I tried that and it still gives me null strings.

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

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?

 

[Guest]

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.

Post what you have

I tried that and it still gives me null strings.

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

:

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?

 

[Guest]

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

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.

Post what you have

I tried that and it still gives me null strings.

:

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

:

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?

 

[Guest]

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.

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

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.

Post what you have

:

I tried that and it still gives me null strings.

:

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

:

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?