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

 

[JLGWhiz]

You can adapt this to your needs.

Code:
Sub Checkbox1_Click()
If Me.CheckBox1.Value = True Then
With Range("A1")
.Font.Name = "WingDings"
.Value = "n"
End With
Else
With Range("B1")
.Font.Name = "WingDings"
.Value = "p"
End With
End If
End Sub
Code:

The WingDings lower case n and p are the same symbols that you want to use.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[jolivanes]

@HOTPEPPER

I should have explained better.

Sub GetCharacters()
    Dim c As Range
    Dim i As Long
    Application.ScreenUpdating = False
    Range("A1:A255").Select
    Application.Dialogs(xlDialogFormatFont).Show
    Range("A1").Select
    Application.ScreenUpdating = True
    i = 1
    For Each c In Range("A1:A255")
        c.Formula = "=Char" & "(" & i & ")"
        'c.Value = c.Value    '<------If you want to
        i = i + 1
    Next c
End Sub

 

[pgc01]

Hi Jolivanes

That would be correct some years ago,when characters in Windows were 8bits by default. Since Windows NT-2000, Windows supports Unicode characters (that could be made availabe in earlier versions like Windows 95, if you knew how to do it) and so most of the fonts we use also support Unicode.

The worksheet function Char() is old and, strangely, there is still not an equivalent worksheet function for Unicode characters. Char() will only work for 8 bits characters and so will not be useful for a case like this one, with characters with codes 9632 and 9633.

You can do it in VBA. VBA added to the old Chr() and Asc() the new functions ChrW() and AscW() that work with Unicode characters.

 

[kelvin p]

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

I'm after something similar to this. I want to create a command button if clicked it will add whatever character I enter into above code. Cell will be populated with either text or number so character to be added at the end.

 

[molinax]

Hi

Great post!
That helped me a lot. You can use it for generating buttons wih vba to.

Hi
You can use the codes to write the characters in strings or directly in cells.
[/CODE]

Kind regards
molinax