How to apply NumberFormat to control on a Userform?
 

I am having a problem with the display of data in a userform control.

The control is a textbox. I have set it's controlsource with:
Me("ProjectNumber").ControlSource =
..Range("Project_Number").Address(external:=True)
which refers to a sheet cell formatted with a Custom "000000". On the sheet
the value shows correctly (000123)
but in the Userform it displays as (123).

Is there a way to get the Userform control's numberformat set? I have tried:
Me("ProjectNumber").NumberFormat = "000000" but this results in a runtime
error "438 - Object does not support this property or method."

Thanks for your wisdom.

.... rick

How to apply NumberFormat to control on a Userform?
 

Rick,

In the initialize event of the userform, use

Userform1.Textbox1.Text = Format(Worksheets("Name").Range("Control Source Address").Value, "000000")

like

UserForm1.TextBox1.Text = Format(Worksheets("Sheet1").Range("A1").Value, "000000")


HTH,
Bernie
MS Excel MVP


"rick" wrote in message ...
I am having a problem with the display of data in a userform control.

The control is a textbox. I have set it's controlsource with:
Me("ProjectNumber").ControlSource = .Range("Project_Number").Address(external:=True)
which refers to a sheet cell formatted with a Custom "000000". On the sheet the value shows
correctly (000123)
but in the Userform it displays as (123).

Is there a way to get the Userform control's numberformat set? I have tried:
Me("ProjectNumber").NumberFormat = "000000" but this results in a runtime error "438 - Object
does not support this property or method."

Thanks for your wisdom.

... rick

How to apply NumberFormat to control on a Userform? (Bernie Deitrick)
 

Thank you Bernie. I put in the following code:
Private Sub UserForm_Initialize()
Me("ProjectNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Number").Value,
"000000")
Me("ActivityNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Activity_Number").Value,
"00000000")
Me("ComponentNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Component_Number").Value,
"000")
End Sub

I compiles and runs OK but doesn't give me the required formats in the
Userform. I then tried putting it in the Activate event, after the
assignment of controlsource(s). Still no joy.

Any other suggestions?

Thanks,

Rick

I believe the Initialize event runs before the Activate event (which is
where I set the control source to all controls).
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

In the initialize event of the userform, use

Userform1.Textbox1.Text = Format(Worksheets("Name").Range("Control Source
Address").Value, "000000")

like

UserForm1.TextBox1.Text = Format(Worksheets("Sheet1").Range("A1").Value,
"000000")


HTH,
Bernie
MS Excel MVP


"rick" wrote in message
...
I am having a problem with the display of data in a userform control.

The control is a textbox. I have set it's controlsource with:
Me("ProjectNumber").ControlSource =
.Range("Project_Number").Address(external:=True)
which refers to a sheet cell formatted with a Custom "000000". On the
sheet the value shows correctly (000123)
but in the Userform it displays as (123).

Is there a way to get the Userform control's numberformat set? I have
tried:
Me("ProjectNumber").NumberFormat = "000000" but this results in a
runtime error "438 - Object does not support this property or method."

Thanks for your wisdom.

... rick

How to apply NumberFormat to control on a Userform? (Bernie Deitrick)
 

Rick,

You cannot have the textboxes linked to a control source and set their value
in code - it is one or the other. Remove the controlsource and it should
work.

HTH,
Bernie
MS Excel MVP


"rick" wrote in message
...
Thank you Bernie. I put in the following code:
Private Sub UserForm_Initialize()
Me("ProjectNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Number").Value,
"000000")
Me("ActivityNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Activity_Number").Value,
"00000000")
Me("ComponentNumber") =
Format(Worksheets("ProjectInformation").Range("Pro ject_Component_Number").Value,
"000")
End Sub

I compiles and runs OK but doesn't give me the required formats in the
Userform. I then tried putting it in the Activate event, after the
assignment of controlsource(s). Still no joy.

Any other suggestions?

Thanks,

Rick

I believe the Initialize event runs before the Activate event (which is
where I set the control source to all controls).
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

In the initialize event of the userform, use

Userform1.Textbox1.Text = Format(Worksheets("Name").Range("Control Source
Address").Value, "000000")

like

UserForm1.TextBox1.Text = Format(Worksheets("Sheet1").Range("A1").Value,
"000000")


HTH,
Bernie
MS Excel MVP


"rick" wrote in message
...
I am having a problem with the display of data in a userform control.

The control is a textbox. I have set it's controlsource with:
Me("ProjectNumber").ControlSource =
.Range("Project_Number").Address(external:=True)
which refers to a sheet cell formatted with a Custom "000000". On the
sheet the value shows correctly (000123)
but in the Userform it displays as (123).

Is there a way to get the Userform control's numberformat set? I have
tried:
Me("ProjectNumber").NumberFormat = "000000" but this results in a
runtime error "438 - Object does not support this property or method."

Thanks for your wisdom.

... rick