Inserting a symbol in a cell using VBA

seasmith

New Member
Joined
Jul 6, 2011
Messages
44
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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"
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p></o:p></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:p></o:p></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:p></o:p>
</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:p></o:p></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:p></o:p></PRE></TD></TR></TBODY></TABLE></P><o:p> </o:p>
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:p></o:p>
However, you can cut & paste them into two cells, and refer to those cells.
 
Upvote 0
Oops, the codes in the above reply got changed into the cgaracters they represent.
The codes are &#9632 and &#9633 respectively.
 
Upvote 0
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:

Code:
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
 
Upvote 0
Another option:
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top