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

 

[My Aswer Is This]

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)

If the user leave it at zero a error will occur. That's why I have a error catching code. And why would the default want to be zero.

The whole ideal from my understanding is to copy rows when Yes is entered.

 

[Fazza]

for sure adjust the entire code to suit!

I wasn't posting a full solution, just the critical step to demonstrate the idea of having one input box instead of a two step process. Users entering the data might appreciate this - especially if there are many to do.

regards

 

[My Aswer Is This]

for sure adjust the entire code to suit!

I wasn't posting a full solution, just the critical step to demonstrate the idea of having one input box instead of a two step process. Users entering the data might appreciate this - especially if there are many to do.

regards

So your saying by entering a default value the user does not have to enter a value. Is that the step your eliminating?

I'm just curios unless I'm not understanding

 

[Fazza]

More likely my misunderstanding.

I was thinking instead of entering "Yes" and then a second entry of a value being a value > 1,
have a single entry. 0 for when no extra rows are entered, or the number of extra rows.

Just so that there is simpler data entry for users. It may not suit: in which case it can be ignored. But if it does suit, it is offered for consideration.

HTH

 

[Life_is_Good]

@ My Answer Is This

Thank you for your "answer"!

One issue, I noted was that every time when I manually delete or insert a row, I get the "We had some sort of problem. Try again" message. How can I handle this issue?

Lastly, one more additional functionality would help me a lot during the duplication is to give append sub-IDs to the original ID for the duplicated rows. For example, in the column 3 ("c") I have a row ID (e.g. "A-1"). And if we duplicated this row 3 times, I would like the row IDs to be something like A-1.1, A-1.2, A-1.3.

Thanks so much!

-LIG

 

[My Aswer Is This]

This is a problem you run into when you use sheet change event scripts.
The script sees a change to the sheet and reacts.
When you delete a row the script sees a change to column and reacts.
Not sure how to solve this issue.
That's why some times a double click script is easier to work with.
double click a cell and something happens.

And so maybe someone else may need to help you.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[NoSparks]

I'd just use an entry in column 4 to initiate things.
Entering a 0 or 1 will do nothing, any higher number will add additional rows and increment the column C values.
See if this works for you, I don't see any issues manually adding or deleting rows.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dupes As Long, i As Integer, j As Integer

If Target.Count > 1 Then Exit Sub                       'limit to monitor for single cell only
If Target.Column <> 4 Then Exit Sub                     'limit to monitor for column 4 only

On Error GoTo ErrorHandler                              'make sure events get re-enabled

If IsNumeric(Target.Value) And Target.Value > 1 Then    'number of duplicate lines
    Application.EnableEvents = False
    dupes = Target.Value - 1
    Rows(Target.Row).Copy
    Rows(Target.Row + 1).Resize(dupes).Insert
    Application.CutCopyMode = False
Else
    Exit Sub
End If

j = 1
For i = Target.Row To Target.Row + dupes
    Cells(i, 3).Value = Cells(i, 3).Value & "." & j
    Cells(i, 4).Value = ""
    j = j + 1
Next i

Application.EnableEvents = True
Exit Sub

ErrorHandler:
MsgBox "Error #  " & Err.Number, 0, "Error"
Application.EnableEvents = True

End Sub