VBA: How to create a multi-level nested IF statement for dynamic data validation?
I'm sure I'm missing something obvious here, but I've been banging my head against this for hours..... I'm trying to implement a dynamic data validation rule in Excel using VBA that checks multiple criteria across various cells. My goal is to create a multi-level nested IF statement that will set the validation based on the value of a specific cell. For instance, if cell A1 contains 'Option1', I want the validation in cell B1 to allow only values between 1 and 100. If A1 is 'Option2', B1 should allow values between 101 and 200. I attempted to use the following code but encountered a 'Run-time behavior '1004': Application-defined or object-defined behavior'. This is my current approach: ```vba Sub SetDynamicValidation() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws.Range("B1") .Validation.Delete If ws.Range("A1").Value = "Option1" Then .Validation.Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=1, Formula2:=100 ElseIf ws.Range("A1").Value = "Option2" Then .Validation.Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=101, Formula2:=200 Else MsgBox "Invalid option selected in A1." End If End With End Sub ``` I ran the macro after changing the value in A1, but it seems the validation isn't applying correctly, and the behavior pops up when I try to run it again after changing A1. Any insights on how to properly implement this or troubleshoot the scenario? I'm using Excel 2016 and would appreciate any best practices to avoid such errors in the future. How would you solve this? This is part of a larger application I'm building. Any ideas what could be causing this?