VBA Userform to stop displaying 0 instead of blank in control text field

[alandry3310]

I use Excel Office 365. I created a userform to maintain information about employees. The userform contains many fields that are populated from an underneath Excel row at initialisation time.. Some fields have values and other are blanks (empty). My problem is that the blank fields are showing as a zero (0) instead of being empty. How to stop this bizarre behaviour?

 

[jkpieterse]

How are you populating the fields exactly?

 

[alandry3310]

The fields are in an Excel Table.
For simplicity I exclude how the table row and column are found. Below are parts of 2 Private Sub invoked with one of the userform field:
The field has TEXT format and is empty. However, Len(field) = 1.

Private Sub FrmMàJAdresse_AfterUpdate() 'turns the needs upate CommandButton
Me.CmdMàjSortir.BackColor = vbYellow
End Sub

Private Sub UserForm_Initialize()
With MaTable
...
Me.FrmMàJAdresse.Value = .DataBodyRange.Cells(MaRangéeTable, .ListColumns("Adresse").Index)
...
End With
End Sub

 

[jkpieterse]

Normally if you get the value of an empty cell and put it into a textbox, the textbox remains empty. But if your cell isn't empty but contains a zero, the zero gets entered. Now you can tell Excel (per worksheet) not to display zeroes. The zeroes are still there as values, you simply don't see them. Perhaps that is the case? You can also format cells not to display zero values. Same issue.
Try if using the Text property of the cell works. Mind you! That will get the values as they are displayed, which might not be what you need in all cases (think of rounding)

 

[alandry3310]

I had tried the TEXT property with no success. I found the issue. In a recent conversion effort, I ended up Copy-Values the cells from a worksheet to another workbook-Worsheet. The source cells for the Copy-Values are formulas (used to re-arrange an old worksheet). For an unknow reason to me, the Copy-Values of what was blank cells resulted in object cells containing a 0. The zeros never appeared in the object worksheets because I had set its property to not display 0 values.

I have changed the formulas (used to re-arrange an old worksheet) to test the source cell for blank: =If(Source="","",Source). If you see a better way to achieve this, I'm open to suggestions.

Thank you for your comments, they helped me to figure out the issue. Problem solved.