Add Data Validation to Excel cell via VBA script

Question

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:

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

What have you tried? What has/hasn't worked? Do you get errors? If so, what error at what line? — BruceWayne, Jan 24 '19 at 18:00
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... — Mathieu Guindon, Jan 24 '19 at 18:07
@BruceWayne I have added the code that I tried and the error I see afterwards. — Dr.YSG, Jan 24 '19 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. — BruceWayne, Jan 24 '19 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?) — Dr.YSG, Jan 24 '19 at 18:30
After the delete of the old Dropliss sheets, We get a #REF error on the data validation, is is possible that it cannot be cleared with the .delete in this code? — Dr.YSG, Jan 24 '19 at 18:31
@MathieuGuindon I have even tried moving the delete of the old validation before I delete the DropList sheet. It still complains about multiple validations. Does this really rate a -1 rating for the question? — Dr.YSG, Jan 24 '19 at 18:56
When you delete the DropList sheet, you "break" the named range. It no longer points to anything. You'll need to recreate it. — Ron Rosenfeld, Jan 24 '19 at 18:59
@RonRosenfeld that is indeed what I am doing, I am adding it back it. But then I get the message about duplicate validation. As you can see from the code, I also add back in the named range — Dr.YSG, Jan 24 '19 at 19:00
This delete of the worksheet, and adding back in the named range and validation is all being done in one pass. Is it possible that the workbook has to be saved and reloaded, for the validation to be removed and added back in? — Dr.YSG, Jan 24 '19 at 19:03
If you are going to add back the original named range, you need to delete it first. Or else you could just change the `RefersTo` parameter — Ron Rosenfeld, Jan 24 '19 at 19:08
@RonRosenfeld read what I write in the comments, and look at the code snippet. It deletes the validation. However, there was no old Range Name for that validation. We are now adding that. — Dr.YSG, Jan 24 '19 at 19:14
I wasn't commenting about the validation, rather the range name (but I think that is a red herring). However, I just tried your code. First I deleted and recreated a droplists worksheet, then ran your code and all worked OK! Several times. Perhaps there is something about how you are "copying in" the new Droplists worksheet. — Ron Rosenfeld, Jan 24 '19 at 19:20
@RonRosenfeld the copying is a thought. But I have another named range on that sheet that is used for validation - and that works. In that case, I only needed to delete the NamedRange and recreate it. Since the validation already used a NameRange. That is not the case between the new and old WinPercent, which is what I suspect gives the "The selection contains more than one type of validation." That there is a namedRange trying to replace a =DropLists!$B$1:$B$5, Did you try that? — Dr.YSG, Jan 24 '19 at 19:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/187283/discussion-between-ron-rosenfeld-and-dr-ysg). — Ron Rosenfeld, Jan 24 '19 at 19:25
I found the source of the issue. Hidden cells that made the validation a range bigger than I thought. Should I delete this question? It really does not seem useful in retrospect. — Dr.YSG, Jan 29 '19 at 17:04

Add Data Validation to Excel cell via VBA script

0 Answers0