1

Is it possible to print a padlock symbol (open & closed) in a cell with Chr(), ChrW() or something similar through VBA?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
AsUsual
  • 524
  • 2
  • 8

2 Answers2

4

It seems that Excel stores special chars as UTF-16. As long as you have a code that fits within 16bit, it's easy: Just convert the code using function ChrW. For example, the code for an alarm clock is U+23F0, so you can write ActiveCell.Value = ChrW(&H23F0) and you get your alarm clock (the prefix &H defines a hexadecimal number).

However, when you have characters that doesn't fit into these 16bit, you have to figure out the UTF-16 codes and concatenate them. The locks I found are U+1F512 and U+1F513, and their UTF-16 representatives are 0xD83D 0xDD12 resp. 0xD83D 0xDD13. So you write something like 

ActiveCell.Value = ChrW(&HD83D) & ChrW(&HDD12)

Of course, you need to set a font to the cell that supports these characters.

I found http://www.fileformat.info/info/unicode/char/search.htm helpfull to find matching characters. If you have already a character and you can paste it into a cell, you can use the code I've written in this answer: https://stackoverflow.com/a/55418901/7599798

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thanks! This worked for me: Locked Padlock = `ChrW(-10179) & ChrW(-8942)` and Unlocked Padlock = `ChrW(-10179) & ChrW(-8941)` – AsUsual May 06 '19 at 14:33
  • Sorry, made a mistake when C&P the codes, corrected it. Your values are the same, just as signed decimal values.It's a matter of taste if your prefer those or the hex values. – FunThomas May 06 '19 at 14:38
2

You need to add a unicode symbol. The following code will add a lock or unlock symbol in cell 1,1.

Sub SetUnlock()
    Sheet1.Cells.Font.Name = "Segoe UI Symbol"
    Sheet1.Cells(1, 1).Value = ChrW(&HE1F7)
End Sub

Sub SetLock()
    Sheet1.Cells.Font.Name = "Segoe UI Symbol"
    Sheet1.Cells(1, 1).Value = ChrW(&HE1F6)
End Sub

You can use the "Add Symbol" button in the "Insert" ribbon to look for other symbols in different fonts.

NickSlash
  • 4,758
  • 3
  • 21
  • 38