Unable to format a control in a Userform

[rick]

I have a Userform which has the .controlsources set to named ranges in one
of the sheets. There are 3 ranges on the sheet that are Custom formatted
with leading zeros such as "000". If a 1 is entered it is displayed on the
sheet as 001.

However, in the form the number shows as 1. I have tried Text(<name>, "000")
but that doesn't take.

How do I display the numbers with a custom format?

Thanks.

Rick

 

[Eduardo]

Hi,
try

Private Sub TxtApril_Change()
Me.TxtApril.Value = Format(Me.TxtApril.Value, "000")
End Sub

Change TxtApril to your box name

 

[Patrick Molloy]

formatting doesn't affect the cell content, just how it appears. So when this
is dropped into say a listbox, its the cell content thats loaded.
You'll need to either (1) load from another range where the TEXT of the cell
matches the format or (2) load when the form initilaises using a for/next
noop and this type of adjustment:
ListBox1.AddItem Format$(Cells(index, 3), "0000")


I'd go for (1) since all you need to do is enter =TEXT(A1,"0000") into an
adjoining column