Cell format in VBA

[lotily]

Good evening,

I have basic skills in VBA and I have made a VBA-based user form in my workbook. It is a simple member list for our small organization. It has name, mail-adress and phone number fields. With my form the user can add or remove members easily. (or thats my purpose anyway) By pressing a command button the form shows up and user inputs member data to fields. As you can already guess i ran into familiar problem with phone number formats. Here in Finland all phone numbers start with zero, so those cells should be formatted as a "text" for me to be able keep the preceding zero. I bet you all no my situation, but how could i do this using VBA and most of all, how can I make sure that those cells WONT show those annoying little green triangles on cell corners "Number fomatted as text" -error?
This is the line from my code which I currently use to input the phone number from the forms texbox to the list:

.........
ActiveCell.Offset(0, 1) = TextBoxPhonenumber.Value
.........'code continues...

I have tried to use TextBoxPhonenumber.Text but that left also those error-triangles in my sheet. What else I could try?


I am using excel 2003 but this code should work with older versions too

I would preciate some tips from you excel-gurus!

Thank you for your time!

Yours, Lotily

 

[mikerickson]

One way would be to Tools>Options>ErrorChecking and uncheck the Numbers formatted as Text box.

Another would be to format the cells with fixed number of digits. "00000000" (no quotes)
and use

With ActiveCell.Offset(0, 1) 
    .Value = Val(TextBoxPhonenumber.Value)
    .NumberFormat = "00000000"
End With

which would put a number in the cell, but formatted to show leading 0's