CODEWITHSUNDEEP
vbamismatch
Thomas Mattinzioli
Thomas Mattinzioli

Reputation: 3

VBA Mismatch Error for outputting textbox entry with calculation to Excel sheet?

quite new to VBA and have what must be a rather simple question, but data types keep on giving me problems. I'm developing a MultiPage UserForm in VBA, and would like to output user input to Excel, to run calcs in the sheet and then provide an answer on a subsequent page in the UserForm. I've tried looking online for solutions, but nothing is clicking. Basically trying this:

Sheets("Calcs").Range("DataStart").Offset(0, 11).Value = CRBinQnt / 100 * Sheets("Calcs").Range("DataStart").Offset(0, 6).Value

So a user would input a number into a text box CRBinQnt, and I want to assign that number to Sheets("Calcs").Range("DataStart").Offset(0, 11).Value in Excel Worksheet. Before inputting that value, I would like it to be divided 100 (to be a percentage) and multiplied by a fixed value already in the worksheet Sheets("Calcs").Range("DataStart").Offset(0, 6).Value.

I've tried Dim CRBinQnt as long, short etc and CDbl(CRBinQnt) to no avail. Not sure why I keep getting mismatch error. Any help would be most appreciated. New to this, so if any further clarifications are required please let me know.

Upvotes: 0

Views: 173

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

What I would do is using the following code for validating your TextBox CRBinQnt. The event CRBinQnt_Exit runs when the TextBox looses focus, so we validate its content.

Private Sub CRBinQnt_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.CRBinQnt.Value = vbNullString Then Exit Sub  ' this allows the TextBox to be left empty (if you don't want that remove it). If you don't allow it the user will be trapped in the TextBox until he enters a valid number. He even cannot abort.
    
    If Not IsNumeric(Me.CRBinQnt.Value) Then
        ' if the value is not numeric error and do not allow to leave the TextBox
        MsgBox "CRBinQnt needs to be an integer number!", vbExclamation
        Cancel = True
    ElseIf CStr(CLng(CRBinQnt.Value)) <> CRBinQnt.Value Then
        ' if the value is numeric check if it is an integer number, if not do not allow to leave the TextBox
        MsgBox "CRBinQnt needs to be an integer number!", vbExclamation
        Cancel = True
    End If
End Sub

Then you should be able to use this without getting an error.

If Not CRBinQnt.Value = vbNullString Then
    Sheets("Calcs").Range("DataStart").Offset(0, 11).Value = CLng(CRBinQnt.Value) / 100 * Sheets("Calcs").Range("DataStart").Offset(0, 6).Value
Else
    ' user did not input anything so abort …
End If

Upvotes: 1

Related Questions