Include superscripts and Special character in Data Validations

vilas desai
3StarLounger
Posts: 290
Joined: 16 Mar 2011, 09:33

Include superscripts and Special character in Data Validations

Post by 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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Include superscripts and Special character in Data Validations

Post by HansV »

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

SpecialCharsInDV.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

vilas desai
3StarLounger
Posts: 290
Joined: 16 Mar 2011, 09:33

Re: Include superscripts and Special character in Data Validations

Post by vilas desai »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Include superscripts and Special character in Data Validations

Post by 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.
Regards,
Hans

vilas desai
3StarLounger
Posts: 290
Joined: 16 Mar 2011, 09:33

Re: Include superscripts and Special character in Data Validations

Post by 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?

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Include superscripts and Special character in Data Validations

Post by 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.
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

vilas desai
3StarLounger
Posts: 290
Joined: 16 Mar 2011, 09:33

Re: Include superscripts and Special character in Data Validations

Post by vilas desai »

Perfect. You are amazing. Thanks a lot