Inserting a symbol in a cell using VBA

[seasmith]

So I have a pop up form and there is a check box that person filling in the information has to check or leave unchecked if the pressure vessel is active or not. And if it is active I want it to insert the symbol below
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21>■</TD></TR></TBODY></TABLE>
and if it is inactive I want this symbol <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21>□</TD></TR></TBODY></TABLE>both of these symbols can be found under the times new roman font when you go into symbols

I kind of have an idea, but this is as far as I got

If chbactive.Value Then
'CheckBox1 is ticked
.Cells(nextEmptyRow, "AA").Font.Name = "Times New Roman"
.Cells(nextEmptyRow, "AA").Value = ? 'solid black box in cell'
Else
'CheckBox1 is not ticked
.Cells(nextEmptyRow, "AA").Font.Name = "Times New Roman"
.Cells(nextEmptyRow, "AA").Value = ? 'Empty box in cell'
End If

I tried just copying and pasting the symbol into the code where the question mark is but it didn't work. Any help would be great

 

[mathchick]

I ran into this issue quite recently, it appears that some symbols just don't work (when I outputted the value of the cell with VBA it resonded with ???). Try looking through the symbol fonts. Wingdings 3 ended up being what I want and then you'll have to do
range("A1") = "h"
range("A1").font.name = "Wingdings 3"

 

[pkrishna]

How about placing the two symbols in two cells, say Z1 and Z2, and referring to these two cells as $Z$1 and $Z$2 in your code?

A website (tinyurl.com/3dewndg) gives codes for these two as


<TABLE class=MsoNormalTable style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184" cellPadding=0 border=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
■ <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o></PRE></TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
filled square <CODE>■</CODE><o></o></PRE></TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> <o></o>
</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
□ <o></o></PRE></TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d4d0c8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
hollow square <CODE>□</CODE><o></o></PRE></TD></TR></TBODY></TABLE></P><o> </o>
They work in a word document if you hold ALT down, and enter 9632 or 9633 from the number pad. But not when you enter the ALT code in an Excel cell. There may be a different method.<o></o>
However, you can cut & paste them into two cells, and refer to those cells.

 

[pkrishna]

Oops, the codes in the above reply got changed into the cgaracters they represent.
The codes are &#9632 and &#9633 respectively.

 

[pgc01]

Hi

When you are in the Insert->Symbol Dialog and you select the symbol you want to insert, you'll see at the bottom of the Dialog the hexadecimal character code of the symbol.

For the symbol ■ you see 25A0
For the symbol □ you see 25A1

You can use the codes to write the characters in strings or directly in cells.

This is an exampe writing strings with the symbols in cells A1 and A2:

Sub TestSquares()
Dim s As String
 
Range("A1").Value = ChrW(&H25A0)
 
s = "This is the empty square: " & ChrW(&H25A1)
Range("A2").Value = s
 
End Sub

 

[HalfAce]

Another option:

If chbactive.Value Then
'CheckBox1 is ticked
.Cells(nextEmptyRow, "AA").Font.Name = "Wingdings"
.Cells(nextEmptyRow, "AA").Value = "n"
Else
'CheckBox1 is not ticked
.Cells(nextEmptyRow, "AA").Font.Name = "Wingdings"
.Cells(nextEmptyRow, "AA").Value = "o"
End If

I believe this is what mathchick was referring to
Hope it helps.

 

[jolivanes]

Can you use Char(??) instead of Value?

Font Terminal - Char(8)
Font Times New Roman - Char(144) and/or Char(149)
Font Symbol - Char(168)

Or you can put a square in the cell and through your code fill it black or clear the fill.

 

[Scott Huish]

CHAR is an Excel function, not VBA. The VBA equivalent is Chr for standard ASCII. You will need ChrW if you want to use Unicode characters.

 

[seasmith]

I ran into this issue quite recently, it appears that some symbols just don't work (when I outputted the value of the cell with VBA it resonded with ???). Try looking through the symbol fonts. Wingdings 3 ended up being what I want and then you'll have to do
range("A1") = "h"
range("A1").font.name = "Wingdings 3"

I did this before but in all of the active cells I have a data validation so the user would be able to switch it back and froth from active and non active symbol but when you use windings with data validation it doesn't show the symbol in the drop down list it shows the charachter and then once you select the charachter it changes to the symbol

 

[seasmith]

Hi

When you are in the Insert->Symbol Dialog and you select the symbol you want to insert, you'll see at the bottom of the Dialog the hexadecimal character code of the symbol.

For the symbol ■ you see 25A0
For the symbol □ you see 25A1

You can use the codes to write the characters in strings or directly in cells.

This is an exampe writing strings with the symbols in cells A1 and A2:

Sub TestSquares()
Dim s As String
 
Range("A1").Value = ChrW(&H25A0)
 
s = "This is the empty square: " & ChrW(&H25A1)
Range("A2").Value = s
 
End Sub

This worked great! Thanks so much!!