VBA Type Mismatch on Worksheet Change event

[Life_is_Good]

Hello All,

Here's what i'm trying to do:

I have a userform which prompts the user to populate numbers when a drop-down value on column D is "Yes". And based on the entered number, current row will be replicated.

However, I am running into Type Mismatch error 13 after the row is replicated. I believe it's because of the If Target.Column = currentCol And Target.Row = currentRow And Target.Value = "Yes" Then UserForm1.Show piece.

Could someone please solve this problem? I am not too familiar with error handling. Thanks a bunch in advance!



Worksheet change

Sub Worksheet_Change(ByVal Target As Range)
    Dim currentCol As Variant
    Dim currentRow As Variant
    currentCol = 4
    currentRow = ActiveCell.Row
    
    If Target.Column = currentCol And Target.Row = currentRow And Target.Value = "Yes" Then UserForm1.Show
    

End Sub

Duplicates the current row based on the entered numbers.

Private Sub OK_Btn_Click()
 
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = ActiveCell.Row
    Application.ScreenUpdating = False

        
        
    If Not IsNumeric(TextBox1.Value) Then
        MsgBox "only numbers allowed"
        Cancel = True
    End If
    
        VInSertNum = CInt(TextBox1.Value)
        
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           
           Range(Cells(xRow, "B"), Cells(xRow, "BB")).Copy
           
           Range(Cells(xRow + 1, "B"), Cells(xRow + VInSertNum - 1, "BB")).Select
           Selection.Insert Shift:=xlDown
           Cells(xRow, "C").Value = Cells(xRow, "C").Value & VInSertNum - 1
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Application.ScreenUpdating = False
UserForm1.Hide
 
End Sub

 

[iliace]

It's possible that UserForm.Show triggers the error. What I would recommend is put a break point on that line (click in the margin, or press F9), and run your code. Once you get to the breakpoint, press F8 to go line-by-line. This should take you to the UserForm code module (something like UserForm_Initialize), and keep pressing F8 to continue line-by-line (slowly) until you get the error.

Post back with what you discover.

 

[My Aswer Is This]

Would you please explain what your ultimate goal is.
If you just want to duplicate the row where Yes is entered in column 4
Why do we need a userform to open and then you enter some value.

There would be easier ways to do this.

So when Yes is entered into column 4 what do you want to happen?
Do not say open userform.
Tell me what the ultimate goal is.
I believe you want the current row to be duplicated.
If that's true I can write you a script which does not require a Userform form.

 

[iliace]

I believe you want the current row to be duplicated.
If that's true I can write you a script which does not require a Userform form.

Very true... if all you need is a single TextBox, might as well use an InputBox and forego the form altogether

 

[Life_is_Good]

Would you please explain what your ultimate goal is.
If you just want to duplicate the row where Yes is entered in column 4
Why do we need a userform to open and then you enter some value.

There would be easier ways to do this.

So when Yes is entered into column 4 what do you want to happen?
Do not say open userform.
Tell me what the ultimate goal is.
I believe you want the current row to be duplicated.
If that's true I can write you a script which does not require a Userform form.

Ultimate goal is to duplicate the current row based on the user's entered numeric value. Not all rows need duplication thus, the 'Yes' or 'No' option

Basically, I'd like to have the ability create "line items" for the current row. For example, if I need 2 additional line items for row 1, I'd expect a place for me to enter the value "3" and without having to run a code, row 1.1, 1.2, 1.3 are created.

 

[Life_is_Good]

Like I said the issue is around If Target.Column = currentCol And Target.Row = currentRow And Target.Value = "Yes" Then UserForm1.Show. This is where the debugger stops. Thanks.

 

[My Aswer Is This]

You said:

I'd expect a place for me to enter the value "3" and without having to run a code, row 1.1, 1.2, 1.3 are created.​

So if you enter 3 into textbox you want to make 3 copies of row is that true?

Do you know what a InputBox is?

Why not have a Inputbox popup.
Enter 3 or 5 or 2 and make that many copies of the row.

 

[Life_is_Good]

You said:

I'd expect a place for me to enter the value "3" and without having to run a code, row 1.1, 1.2, 1.3 are created.​

So if you enter 3 into textbox you want to make 3 copies of row is that true?

Do you know what a InputBox is?

Why not have a Inputbox popup.
Enter 3 or 5 or 2 and make that many copies of the row.

So if you enter 3 into textbox you want to make 3 copies of row is that true? Yes, that's right

Do you know what a InputBox is? No

Not all rows need duplication and we don't want to prompt the users to enter a value when there is no need for duplication. That's why we need a trigger for the userform, hence, 'yes' or 'no' dropdown. Yes, it could be a inputbox or whatever - if you think there's a better or simpler way to do this please do let me know.

Thanks for trying to understand my objective.

 

[My Aswer Is This]

Try this:
No UserForm needed

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-19-18 11:22 PM EDT
If Target.Column = 4 Then If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Application.EnableEvents = False
Dim r As Long
Dim ans As Long
r = Target.Row
If Target.Value = "Yes" Then
ans = InputBox("How many copies of this row do you want?")
Rows(r).Offset(1).Resize(ans).Insert xlShiftDown
Rows(r).Offset(1).Resize(ans).Value = Rows(r).Value
End If
Application.EnableEvents = True
Exit Sub
M:
MsgBox "We had some sort of problem. Try again  "
Application.EnableEvents = True
End Sub

 

[Fazza]

the input box looks good

a suggested slight variation on that, if it suits - with the idea of quicker/easier data entry

instead of having a yes input and then a value input, code it so that one single input is required. 0 for no rows

ans = InputBox(Prompt:="Enter number of extra rows. (Zero for none.)", Title:="How many copies of this row do you want?", Default:=0)