Include superscripts and Special character in Data Validations

[vilas desai]

Hello Experts,

Please help me to edit special characters and superscripts while making a drop down list:

Data --> Data Validation --> List -->(Deg Centigrade, meter Cube)

Thanks and best regards
Vilas Desai

[HansV]

To do that, you must create the source list in a range of cells. See the attached example.

SpecialCharsInDV.xlsx

[vilas desai]

Thanks as always, Hans. I want to avoid using the source list Can the macros do this?

[HansV]

A source range is much more convenient, but of course you can also use something like

Code: Select all

Sub SetDV()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="°C,m²,cm³"
        .ErrorTitle = "Boo!"
        .ErrorMessage = "Wrong, wrong, wrong!"
    End With
End Sub

This may fail if you use Unicode characters, because the Visual Basic Editor does not fully support Unicode in the code itself.

[vilas desai]

Hans, how did you manage to edit the superscripts in the VBA editor? If I need to add another one like 2 raise to a or 3 power 4 how do i do it?

[HansV]

I used Character Map from Start > Windows Accessories to find the characters and copy them to the clipboard. Then I pasted them.
This will work for the degree symbol and for superscript 2 and 3 - they belong to the "standard" character set.

S0561.png

But it won't work for superscript 4, 5, etc. - those are Unicode characters that will be changed to ? in the Visual Basic Editor.
That's why it is better to use a cell range to store the list.

[vilas desai]

Perfect. You are amazing. Thanks a lot