Drop Down Menu to Change Several Cells - Infinite Loop Crash

[r2yneth]

Hello!

I am running into a brick wall with an infinite loop that keeps occurring (I have no idea why) when I choose either "Rectangle" or "Circle" on my drop down menu.

Below is what I have in VBA - I copied it from a Excel video where it only had 1 parameter, and I need to have 2-3 parameters (Changing 2 different cells instead of only 1)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Range("C2")​

Case "Rectangle"​

Range("A5") = "Orifice Length"​

Range("A6") = "Orifice Width"​

Case "Circle"​

Range("A5") = "Orifice Diameter"​

Range("A6") = ""​

Range("C6") = ""
​

End Select​

End Sub


Any help would be greatly appreciated!

 

[mumps]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Rectangle"
            Range("A5") = "Orifice Length"
            Range("A6") = "Orifice Width"
        Case "Circle"
            Range("A5") = "Orifice Diameter"
            Range("A6") = ""
            Range("C6") = ""
    End Select
End Sub

This macro should be placed in the code module for ThisWorkbook. It will also apply to all the sheets in your workbook. Is this what you want or do you want it to apply to only one specific sheet?

 

[r2yneth]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Rectangle"
            Range("A5") = "Orifice Length"
            Range("A6") = "Orifice Width"
        Case "Circle"
            Range("A5") = "Orifice Diameter"
            Range("A6") = ""
            Range("C6") = ""
    End Select
End Sub

This macro should be placed in the code module for ThisWorkbook. It will also apply to all the sheets in your workbook. Is this what you want or do you want it to apply to only one specific sheet?

Hi, thanks for helping me!

I have saved it as ThisWorkbook -> [Workbook] & [SheetChange]

It would be ideal to only have this on my first sheet

I have tried the code you have suggested and it does not appear to do anything, perhaps because I have other C2 cells on other sheets? Just copied and paste:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Rectangle"
Range("A5") = "Orifice Length"
Range("A6") = "Orifice Width"
Case "Circle"
Range("A5") = "Orifice Diameter"
Range("A6") = ""
Range("C6") = ""
End Select
End Sub

 

[r2yneth]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Rectangle"
            Range("A5") = "Orifice Length"
            Range("A6") = "Orifice Width"
        Case "Circle"
            Range("A5") = "Orifice Diameter"
            Range("A6") = ""
            Range("C6") = ""
    End Select
End Sub

This macro should be placed in the code module for ThisWorkbook. It will also apply to all the sheets in your workbook. Is this what you want or do you want it to apply to only one specific sheet?

It worked! Thank you!!! It would be ideal to only have it for the selected worksheet.

Thank you!!

 

[mumps]

You are very welcome. If you want it for a specific sheet, copy and paste this revised macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Rectangle"
            Range("A5") = "Orifice Length"
            Range("A6") = "Orifice Width"
        Case "Circle"
            Range("A5") = "Orifice Diameter"
            Range("A6") = ""
            Range("C6") = ""
    End Select
End Sub

Delete the macro from the code module for ThisWorkbook if it still there.

 

[r2yneth]

You are very welcome. If you want it for a specific sheet, copy and paste this revised macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Rectangle"
            Range("A5") = "Orifice Length"
            Range("A6") = "Orifice Width"
        Case "Circle"
            Range("A5") = "Orifice Diameter"
            Range("A6") = ""
            Range("C6") = ""
    End Select
End Sub

Delete the macro from the code module for ThisWorkbook if it still there.

Thank you thank you! You honestly saved my day. I spent over 2 hours watching YouTube videos trying to find a solution. Have a great day!

 

[mumps]

My pleasure.

 

[r2yneth]

My pleasure.

One last question! What would I have to change if I had another drop down menu for "C3"? I tried duplicating the code but it will only run the code for when "C2" changes. Very weird

 

[r2yneth]

My pleasure.

Nevermind my last question, I added the value in the initial range set and it all works!