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

Question

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

Can you not just create a new Worksheet and drop your list there, then reference it? — Roland
– Roland, Commented Feb 28, 2016 at 12:23
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
– civam, Commented Feb 28, 2016 at 13:56
See here, especially Siddharth's answer using chr(130) - which looks like a comma, but is not one. — vacip
– vacip, Commented Feb 28, 2016 at 14:24
Siddharth's answer is little helpful but can I change that Chr(130) to comma through any method — civam
– civam, Commented Feb 28, 2016 at 16:46
If Chr(130) looks identical to a comma why would you need to change it? — snoopen
– snoopen, Commented Feb 28, 2016 at 21:35

Gary's Student · Accepted Answer · 2017-06-29 12:06:38Z

3

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

edited Jun 29, 2017 at 12:06

answered Feb 28, 2016 at 12:49

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

civam Over a year ago

Thanks for your answer but Its not working. Its only change selected list item's dummy character, not the whole items.

snoopen Over a year ago

I've suggested an edit to this where Chr(130) is used as the dummy character. This will meet all @civam requirements of looking like a comma on the list and actually exporting as a comma after selection.

Gary's Student Over a year ago

@snoopen Thanks for the improvement!

snoopen · Accepted Answer · 2016-02-28 12:22:39Z

0

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.

answered Feb 28, 2016 at 12:22

snoopen

2251 silver badge9 bronze badges

4 Comments

civam Over a year ago

Thanks for your suggestion, but that is not my requirement.

snoopen Over a year ago

I appreciate that it's not your requirement however this would be the only way to have a true comma in your list. I'd recommend Chr(130) as @vacip suggested if you don't want to create the separate worksheet.

civam Over a year ago

I have created a dummy workbook and every time code runs it saves that with new file name.

snoopen Over a year ago

I'm curious if you tried the updated solution proposed by @Gary'sStudent with Chr(130)? I would have thought it would have been the ideal solution as it eliminates the need for an additonal worksheet.

civam · Accepted Answer · 2016-03-05 15:46:00Z

0

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.

answered Mar 5, 2016 at 15:46

civam

1163 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related