How to apply NumberFormat to control on a Userform?

[rick]

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

 

[Bernie Deitrick]

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]

Thank you Bernie. I put in the following code:
Private Sub UserForm_Initialize()
Me("ProjectNumber") =
Format(Worksheets("ProjectInformation").Range("Project_Number").Value,
"000000")
Me("ActivityNumber") =
Format(Worksheets("ProjectInformation").Range("Project_Activity_Number").Value,
"00000000")
Me("ComponentNumber") =
Format(Worksheets("ProjectInformation").Range("Project_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]

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