0

I'm reading in values from an Excel sheet and have the user checking those numbers/text and hitting save again if they are correct. It works well for any text and numbers, except for my discount being of double type.

Private Sub UserForm_Initialize()

' filling form
TextHN.Text = Range("D3")
'...
' original: TextDP.Text = Range("D7") - but wouldn't read in 12.5
' therefore tried below as well
 Dim new_value As Double
    If IsNumeric(Range("D7").Value) Then
        new_value = CDbl(Range("D7").Value)
        MsgBox new_value # displays 12.5
        Else
        new_value = 1
    End If
    TextDP.Text = CStr(CDbl(new_value))
    MsgBox TextDP.Text # displays 12

I read several articles (Convert TextBox.Value to Double into VBA (Excel 2013) & https://www.mrexcel.com/board/threads/textbox-value-to-double.1066876/), but nothing helped. How do I need to change my code to display my discount in TextDP? (I can read it out as double into Excel file fine)

Update with screenshots:

Excel table UserForm

I'm surprised that it displays it for hospital name and abbreviation correctly (and saves it accordingly), but not for discount percentage and expiry length...

Improve this question
22
  • 1
    You convert the input into a double with new_value = CDbl(Range("D7").Value). It displays the proper content. Why are you then trying to make it a double again and then convert it to a string with TextDP.Text = CStr(CDbl(new_value)) when it's already a double? You don't need the second call to CDbl before you use CStr - just use CStr(new_value).
    – Ken White
    Commented Nov 4 at 2:34
  • 1
    Read this: oaltd.co.uk/ExcelProgRef/Ch22/default.htm it will help you understand how Excel and VBA interface with regards to international settings such as decimal separators.
    – jkpieterse
    Commented Nov 4 at 10:14
  • 1
    What is 12.50 in D7? Is it a number? Test it in another cell =ISNUMBER(D7). Why does it have two digits after the dot? What is the number format of D7? What is the value of the MaxLength property of TextDP?
    – rotabor
    Commented Nov 4 at 23:46
  • 1
    I guess SpinButtons affect that TextBoxes. Disable the code related to SpinButtons. I suspected something like this.
    – rotabor
    Commented Nov 5 at 7:40
  • 1
    SpinButton works with integer. How do you plan to work with a fractional number?
    – rotabor
    Commented Nov 5 at 7:44

1 Answer 1

Reset to default
1

Evidently, SpinButton's or else code affects TextBox's content.

Highly likely, the TextDP_Change event is handled to setup related SpinButton. Then SpinButton_Change sets TextDP back.

Improve this answer
1
  • By deleting the SpinButton, the problem solved and I can simply reflect value from cell by TextDP.Text = Range("D7").
    – Chris Peh
    Commented Nov 6 at 2:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.