0

What parameters do I need in the VBA call to get DataValidation that uses a List, that is referenced via a Named RANGE?

Our range is called WinPercent. It is already defined via vba.

The default Stop alert is fine, but I want to get something that works like what is shown below:

enter image description here

Right now, the VBA executes without an error, but when go to examine the current DataValidation, I get a pop-up:

"The selection contains more than one type of validation. Erase Current settings and continue"

The VBA code is:

Sub addWinPercent(wbk As Workbook)
    Dim target As range
    wbk.Names.Add Name:="WinPercent", RefersTo:=wbk.Sheets("DropLists").range("B1:B5")
    Set target = wbk.Worksheets("StaffRequest").range("J3")
    With target.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=WinPercent"
    End With
End Sub

I have looked at the SO article:

How to add data validation to a cell using VBA

UPDATE

Here is an odd observation. Before I delete the DropList sheet (where the validation was pointing), I put a breakpoint right before the delete, and after the removal of the old validation. I get a pop-up on the when checking the validation on the J3 cell (with the GUI). It says that some cells don't have validation, do I want to extend it. If I say no, there is no validation there. But if I say YES, then it goes back to the old validation. There should be no validation on that worksheet at this point in time no matter what I say.

here is how I remove the validation and then the old sheet

    Set dest = wbk.Sheets("DropLists")
    wbk.Names("YesNoList").Delete
    Set target = wbk.Worksheets("StaffRequest").range("J3")
    target.Validation.Delete 'delete previous validation
    Application.DisplayAlerts = False
        dest.Delete
    Application.DisplayAlerts = True
16
  • 1
    What have you tried? What has/hasn't worked? Do you get errors? If so, what error at what line? Commented Jan 24, 2019 at 18:00
  • 1
    The question you're linking to looks like it has everything you need to know... which specific part is unclear? Formula1 needs to be your =WinPercent named range, is all... Commented Jan 24, 2019 at 18:07
  • @BruceWayne I have added the code that I tried and the error I see afterwards. Commented Jan 24, 2019 at 18:18
  • Hmm, your code works fine for me. Double check the sheets you're using are the ones you're expecting. How exactly are you calling it - I tried with Sub t() // addWinPercent ActiveWorkbook // End Sub and it worked. Commented Jan 24, 2019 at 18:25
  • Everything points to the correct cells. The issue seems to be the delete of the old formula (which was =DropLists!$B$1:$B$5, but then deleted the DropLists sheet, copied in a new one, which is why we have to re-add the name and the validation. Did you try that? (and is it really a -1 question?) Commented Jan 24, 2019 at 18:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.