Percentage formats in User Form text boxes

[scoha]

I have a user form which includes some text boxes (txtRUL.text for example) where I want to enter a number as a percentage. I divide the number entered by 100 to avoid Excel automatically multiplying by 100 and the result is displayed in the user form as the correct percentage.

Private Sub txtRUL_AfterUpdate()
EnableSave
txtRUL.Value = Format(txtRUL.Value / 100, "0.0%")

(I'm sorry I cant figure out how to format this code as code in this post)

This works well, trouble is when I save and it writes the results to the worksheet, the numbers in the user form revert back to plain unformatted non percentage numbers (ie 5% goes to 0.05)

Any ideas?

 

[jindon]

try

txtRUL.Value = Cells(r, 27).Text

 

[scoha]

jindon - that was what was needed! Works just as I need it - thanks very much for persisting with this matter.

 

[pstraton]

I know this is an old thread but I've recently run into this problem and HaHoBe's answer provided a clue that helped me resolve it. But the issue has several dimensions and the lack of final resolution to this thread suggests that it's not well articulated.

In this scoha's case, the problem may be that the array of worksheet cells being referenced may be formatted as "General" formatting (the default), which will automatically convert percentages to decimals when writing to them and reading form them.

In my case, I'm using the ControlSource property of a Textbox contained in an ActiveX Frame control. Here's the general case, and resolution for these kinds of situations:

If the Textbox has its LinkedCell (or ControlSource) property set to refer to a worksheet cell, or if VBA code explicitly copies data to and from a worksheet cell, then the problem may be that Excel’s cell formatting system is overriding the Textbox’s value format. The fix for this problem is to ensure that the targeted LinkedCell (or ControlSource) cell, or the explicitly written/read cell, is explicitly formatted to Text format (“@”) or Percentage format (“0.00%”, “#%”, etc.).
​