Excel VBA: how to add data validation list which contain comma included value without using reference of Range

5
votes

I need to add a data validation list to Cell whose few values contains comma. I have the same problem like this Data Validation to Include Comma Character

But I can't reference a range, because I am creating a new workbook and feed its cell with the data validation list. So reference is not working for me and since some values contain comma so I can't set the Range into String and use that after Formula1

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=myStr

Another idea I found that I can replace comma with other character(dummy character) and after populating the cell replace that dummy character with comma but problem is that how to replace that dummy character with comma ?

Another way is that override the Formula1 so that I can use dummy character as delimiter but I don't know how to achieve this.

Please suggest any solution, my final goal is to create a new workbook and populate it with data validation list through Excel VBA

3
vbaexcel
Can you not just create a new Worksheet and drop your list there, then reference it? – Roland
I can, but problem is comma containing values. If I store whole item in myStr then list validation automatically split them too into new items. Which I don't need. – civam
See here, especially Siddharth's answer using chr(130) - which looks like a comma, but is not one. – vacip
Siddharth's answer is little helpful but can I change that Chr(130) to comma through any method – civam
If Chr(130) looks identical to a comma why would you need to change it? – snoopen

3 Answers

3
votes

Let's use the dummy character method for cell B9. This sets up the DV:

Sub InternalString2()
    Range("B9").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Replace("alpha%ralpha,beta%waiter,gamma%hammer,delta%faucet","%",Chr(130))
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

where Chr(130) is the dummy. The .ShowError is important.

Now to replace the dummy, we use the Event Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
        Range("B9").Replace What:=Chr(130), Replacement:=","
    Application.EnableEvents = True
End Sub
0
votes

As the other answer states, the comma cannot be escaped. The only solution, as far as I can see, is to add an extra worksheet to your new workbook, add the values there for your list, name that range, hide that sheet, and reference the named range. It's a fair few steps but really the only way.

0
votes

As the @snoopen suggest, I have create a Dummy Workbook which contains the data validation list and every time code runs it save as the Dummy Workbook to desired new file name. After that it populates that sheet with required data.