Cdbl textBox showing as Text Error in Worksheet

[Denny57]

I have an issue which has arisen on a number of files during the past few days. I am now getting error messages next to each worksheet cell advising that the entry of the cell is as text, even when the code sends the content as cDbl or currency. This has only happened since a recent Windows Update which I think may have reset a default value.
Consequently, I have needed to revert to long-hand code to get around this. This is ok when I only have a few textboxes, but I have some where there are over 200 textboxes x 4 conditions.

If there is not a generic setting, then please could someone please advise how I might change the following codes to ensure the cells are populated in the correct format.

(Maybe one day MS might introduce a control dedicated to sending numeric values to the ws instead if needing code to achieve this, especially as Excel is used for calculations)

Code 1 - The ws cells this code refers to still show the error despite the inclusion of cDbl.

Private Sub cmdAddRecord_Click()
'Used to add new transation records to the database

    Dim r As Long, sCredit As String, sDebit As String
    
    sDebit = Me.txtTransactionAmountDebit
    sCredit = Me.txtTransactionAmountCredit
    
    With Sheets("Spending Account")
        r = 1 + .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(r, "A").Value = DTPicker1
        .Cells(r, "B").Value = cboVendorDetails
        .Cells(r, "C").Value = cboTransactionType
        .Cells(r, "F").Value = cboTransactionStatus
     
        ' credit or debit
        If Len(sDebit) > 0 Then
            If Len(sCredit) > 0 Then
                MsgBox "Warning - Both Credit and Debit", vbExclamation
            Else
                .Cells(r, "D").Value = CDbl(sDebit)
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(r, "E").Value = CDbl(sCredit)
        End If
          
        If r > 21 Then
            Application.Goto Reference:=.Cells(r - 20, "A"), Scroll:=True
        End If
    End With

    'Unload Me
    'frmRegularTransactions.Show
    
    Call UserForm_Initialize
    
End Sub

Code 2 -Extract of the full code only

Private Sub cmdUpdateEuromillionsResultRecords_Click()

    Set EuroToColsDict = CreateObject("Scripting.Dictionary")
    
        With EuroToColsDict
            Add "All", Array("B", "C", "D", "E", "F", "G", "H")
        End With

         With Me
                 vCols = EuroToColsDict(.cboLotteryAll.Value)
        Set ws = Sheets("Draw Information")
            tbCounter = 1
                For lngRowLoop = 19 To 22
                    For Each vCol In vCols
                        ws.Cells(lngRowLoop, vCol).Value = .Controls("txtEuromillionsResult" & tbCounter).Text
                        tbCounter = tbCounter + 1
                    Next
                Next
    End With
     MsgBox "Euromillions Records have been updated", 0, "Records Updated"
End Sub

In both instances I need the TextBox Values to be treated as either curreny or numeric values in the worksheet.

Many thanks

 

[Legacy 143009]

Unfortunately, I ran into a similar issue like that. The only solution that I came up with was to set the number format after each statement. Like:

           Else
                .Cells(r, "D").Value = CDbl(sDebit)
                .Cells(r, "D").NumberFormat = "0"
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(r, "E").Value = CDbl(sCredit)
           .Cells(r, "E").NumberFormat = "0"

I hope it works for you, too.

 

[Denny57]

Unfortunately, I ran into a similar issue like that. The only solution that I came up with was to set the number format after each statement. Like:

           Else
                .Cells(r, "D").Value = CDbl(sDebit)
                .Cells(r, "D").NumberFormat = "0"
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(r, "E").Value = CDbl(sCredit)
           .Cells(r, "E").NumberFormat = "0"

I hope it works for you, too.

Thank you. This is easy to apply to the second example, however, I am unsure how I woudl apply this to the first code which uses arrays and not an individual column "D" or "E"

 

[Legacy 143009]

I think you should apply like this:

ws.Cells(lngRowLoop, vCol).Value = .Controls("txtEuromillionsResult" & tbCounter).Text
ws.Cells(lngRowLoop, vCol).NumberFormat = "0"

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Why do Worksheet cells show "Text" error even when the code includes cDbl

Please can someone explain why cells in my worksheet are suddenly showing "Text Value" error messages when my code indicates that a value from the corresponding textbox should be CDbl. Th...

stackoverflow.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.