Excel VBA: how to add a data validation list that contains a comma, including a value without using the Range link

Question

I need to add a data validation list to a cell whose small values ​​contain a comma. I have the same problem as Validating data to include a comma

But I can’t refer to the range because I create a new book and pass it a cell with a list of data validation. Therefore, the link does not work for me, and since some values ​​contain a comma, so I can not set Range to String and use it after Formula1

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

Another idea that I found is that I can replace the comma with another character (dummy character) and after filling in the cell, replace this dummy character with a comma, but the problem is, how to replace this dummy character with a comma?

Another way is to redefine Formula 1 so that I can use the dummy symbol as a separator, but I do not know how to do it.

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

Answer 1

Use the dummy symbol method for cell B9 . This installs 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 fictitious . Important .ShowError .

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 

Answer 2

As the other answer says, the comma cannot be escaped. The only solution, as far as I see, is to add an additional sheet to the new book, add the values ​​for your list there, the name of this range, hide this sheet and indicate the named range. These are fair steps, but in fact the only way.

Answer 3

As @snoopen suggests, I created a Dummy Workbook that contains a data validation list, and each time it starts, it saves the desired new file name as a Dummy Workbook . After that, he fills this sheet with the necessary data.