Impossible?

[raskinsm]

Hello,

I have posted this on 3 other forums and no one has responded. It seems like a doable request. Maybe a not-so-advanced user has an advanced scenario? It doesn't seem outlandish to insert a row once data is input.


I have a macro written on a specific sheet (not a module). What it does is when a selection is made in the dropdown in C2, it populates, in the adjacent cell, D2, another dropdown depending on the selection in C2.



This works perfectly.



However, this configuration will not allow me to insert a row, which pushes the data in Row 2 to Row 3.



Here is the code on the sheet.

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target
Case "Home Expenses": CAT_HM
Case "Daily Living": CAT_DLE
Case "Children": CAT_CHLD
Case "Savings": CAT_SAV
Case "Obligations": CAT_OBLIG
Case "Entertainment": CAT_ENT
End Select
End If
End Sub

When I insert a row, I get MSVB error: Run-Time error '13': Type Mismatch





When I click Debug, it sends me here:











Any help would be epic, I have been struggling with this. I have also tried copying and pasting, cutting, deleting and pasting. Come on, do me a solid on this Friday.



Thanks

Marc

 

[Fluff]

I have posted this on 3 other forums and no one has responded.

Thanks for letting us know, but can you please post links to those sites. Thanks

 

[raskinsm]

Thanks for letting us know, but can you please post links to those sites. Thanks

There is nothing there. I copied and pasted exactly what I pasted here in other Excel Macro Help Forums. If I post the links, you will see exactly what you see here but in a different forum. This question doesn't make sense. The other sites are exactly like this one. Why would you want to navigate to other forums to see the exact question in verbatim?

 

[raskinsm]

Thanks for letting us know, but can you please post links to those sites. Thanks

Here, let me rephrase:

Hello,

I have a macro written on a specific sheet (not a module). What it does is when a selection is made in the dropdown in C2, it populates, in the adjacent cell, D2, another dropdown depending on the selection in C2.



This works perfectly.



However, this configuration will not allow me to insert a row, which pushes the data in Row 2 to Row 3.



Here is the code on the sheet.




VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target
Case "Home Expenses": CAT_HM
Case "Daily Living": CAT_DLE
Case "Children": CAT_CHLD
Case "Savings": CAT_SAV
Case "Obligations": CAT_OBLIG
Case "Entertainment": CAT_ENT
End Select
End If
End Sub

When I insert a row, I get MSVB error: Run-Time error '13': Type Mismatch





When I click Debug, it sends me here:











Any help would be epic, I have been struggling with this. I have also tried copying and pasting, cutting, deleting and pasting. Come on, do me a solid on this Friday.



Thanks

Marc

 

[Fluff]

The fact that they have not been answered is not the point.

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. Most sites have exactly the same requirement.

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

Please provide links to all sites where you have asked this question.

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

 

[Flaiban]

Try...
First , insert a Module :

Sub raskinsM()
Dim ws As Worksheet: Set ws = Worksheets("Plan1")
'declare and set the worksheet you are using, amend as required
LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
'get the last row with data on Column B

For i = 2 To LastRow + 1 'loop from Row 2 to Last
    If ws.Cells(i, "C").Value = "" Then
        With ws.Cells(i, "C").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="CAT_HM,CAT_DLE,CAT_CHLD,CAT_SAV,CAT_OBLIG,CAT_ENT"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        With ws.Cells(i, "D").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Home Expenses,Daily Living,Children,Savings,Obligations,Entertainment"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

    End If
Next i
End Sub

After ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Call raskinsM
End Sub

 

[raskinsm]

Try...
First , insert a Module :

Sub raskinsM()
Dim ws As Worksheet: Set ws = Worksheets("Plan1")
'declare and set the worksheet you are using, amend as required
LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
'get the last row with data on Column B

For i = 2 To LastRow + 1 'loop from Row 2 to Last
    If ws.Cells(i, "C").Value = "" Then
        With ws.Cells(i, "C").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="CAT_HM,CAT_DLE,CAT_CHLD,CAT_SAV,CAT_OBLIG,CAT_ENT"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        With ws.Cells(i, "D").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Home Expenses,Daily Living,Children,Savings,Obligations,Entertainment"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

    End If
Next i
End Sub

After ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Call raskinsM
End Sub

Oooh This looks like something I can work with. Thanks! I'll let you know.

Thanks again and have a great weekend.

Marc

 

[Flaiban]

Oooh This looks like something I can work with. Thanks! I'll let you know.

Thanks again and have a great weekend.

Marc

Great weekend for you , too.
I hope it helps you

 

[Fluff]

@raskinsm please do not ignore instructions from moderators.
Please post the links to the other sites where you have asked this question.

 

[raskinsm]

@raskinsm please do not ignore instructions from moderators.
Please post the links to the other sites where you have asked this question.

I don't remember and I am not searching my history, I have too much going on. I will delete the post and never ever ever say that again.