Drop Down Menu to Change Several Cells - Infinite Loop Crash

r2yneth

New Member
Joined
Jul 10, 2014
Messages
13
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,946
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
Code:
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?
 
Last edited:

r2yneth

New Member
Joined
Jul 10, 2014
Messages
13
Try:
Code:
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

New Member
Joined
Jul 10, 2014
Messages
13
Try:
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,946
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
Code:
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.
 
Last edited:

r2yneth

New Member
Joined
Jul 10, 2014
Messages
13
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.
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,946
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
My pleasure. :)
 

r2yneth

New Member
Joined
Jul 10, 2014
Messages
13
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
 

Forum statistics

Threads
1,170,954
Messages
5,872,935
Members
432,952
Latest member
Il Prete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top