Excel VBA userform type mismatch

Question

First time asking a question here, my apologies if my question has already been answered (If it was, I didn't understand it because I am an utter novice). My Excel userform that I use to update quantities of stock supplies used on a particular job is generating a

type mismatch error

It is supposed to add the quantity from the useform to the entry in the appropriate cell on the sheet. I assume that this has something to do with a variable not being declared correctly.

Private Sub SubmitFormButton_Click()

Dim Data_Start As Range

Dim i As Integer

Set Data_Start = ActiveSheet.Cells(6, 6)

For i = 1 To 31

    Data_Start.Offset(i, 0) = Data_Start.Offset(i, 0) + AddToform.Controls("TextBox" & i).Value

Next i

Unload AddToform

End Sub

If this code runs from within the form then change AddToform to Me so that it references the current instance of the form. — Andy G
– Andy G, Commented Dec 14, 2017 at 15:32
You should probably be using + Val(AddToform.Controls("TextBox" & i).Value) to convert the textbox values to true numbers. Technically CDbl would be more appropriate if you might not be dealing with US settings but Val works better with empty textboxes. — Rory
– Rory, Commented Dec 14, 2017 at 15:33
@sktneer I'd generally agree but if the cell contained text already (eg wrong cell, or bad data entry) then not using Val would cause the + to operate like & and simply append the data. Using Val/CDbl would cause an error, which would probably be a good thing. — Rory
– Rory, Commented Dec 14, 2017 at 15:52

Mathieu Guindon · Accepted Answer · 2017-12-14 15:51:30Z

AddToform.Controls("TextBox" & i).Value is making a number of assumptions:

AddToForm was shown with AddToForm.Show. If you're doing this:
```
With New AddToForm
    .Show
    '...
End With
```
...then the code is not referring to the instance that's being displayed, and the .Value of the textbox is very likely not what you expect it to be, since the textbox you're reading from isn't the textbox that the user entered a value in.
There's a control named "TextBox" & i on the form. This means if you ever rename your textboxes to more meaningful names, e.g. FirstNameBox, LastNameBox (or whatever makes sense), then the code breaks. Using control names to hold metadata (e.g. some worksheet row number offset) can work, but it's probably better to iterate the controls on the form (whatever their names are), test if the current control is a TextBox (e.g. If TypeOf ctrl Is MSForms.TextBox Then), and then pull the metadata from the control's Tag property. That way your controls can have meaningful names and renaming them won't break any of the logic.
User input is valid. That's always a bad assumption to make, regardless of the language or technology being used: always protect your code from invalid input. If the user enters "ABC", that loop breaks. One way to do this, is to introduce a local variable, to separate getting the user input from consuming the user input - and validate it on the way:
```
If IsNumeric(Controls("TextBox" & i).Value) Then
    Dim validInput As Double
    validInput = CDbl(Controls("TextBox" & i).Value)
    Data_Start.Offset(i, 0) = Data_Start.Offset(i, 0) + validInput
End If
```
And that should fix your bug.

Thanks, I ready the article, and I aspire to comprehend it someday!

Collectives™ on Stack Overflow

Excel VBA userform type mismatch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related