Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

How to Automatically Insert Symbols and Special Characters in Excel

Insert symbols and special characters on a worksheet, you can use the Symbol dialog box to enter symbols and characters, If you want to repeatedly input the symbols and special characters, will make people upset when used in VBA can be quickly and easily achieve it.

If column C is to enter "is the best salesman", and if so, input a "^v^" , because the 1th, 1 header rows, starting from 2nd row, click the mouse, showing a "^v^".

Automatically Insert Symbols and Special Characters in Excel


Right mouse button click on the sheet tab, select the "View Code" and paste the following code at the cursor:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Application.EnableEvents = False

For Each Rng In Target
If Rng.Row > 1 And Rng.Column = 3 Then 'If the number rows is greater than 1 and the column number equals 3 (column c)
Rng = IIf(Rng = "^v^", "", "^v^") 'If the cell was "^v^" instead of NULL, otherwise, enter "^v^"
End If
Next

Application.EnableEvents = True
End Sub


Note:

If you select an entire row or column can trigger sheet suspended animation. If you want to avoid this from happening, you should be at the beginning of the program inserts a judgement statement: if you select an entire row or column, and exit the program.

Code is as follows:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If the selection is an entire row or column, do not run the following program, exit the program.
If Target.Rows.Count = Me.Cells.Rows.Count Or _
Target.Columns.Count = Me.Cells.Columns.Count Then Exit Sub


Dim Rng As Range
Application.EnableEvents = False

For Each Rng In Target
If Rng.Row > 1 And Rng.Column = 3 Then
Rng = IIf(Rng = "^v^", "", "^v^")
End If
Next

Application.EnableEvents = True
End Sub


Example: Satisfaction Survey

Example File: satisfaction survey.xlsm

A satisfaction survey form, Service Department has four. To calculate each of the four Office staffs on the number one point of satisfaction and satisfaction on all issues in every department number. To the effect that, click a button on the cell count of the total number.

Automatically Insert Symbols and Special Characters in Excel

Code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nL%


If Target.Count > 1 Then Exit Sub 'If you select multiple cells, exits the program

'If you select B3:U21 outside the cell, exits the program
If Intersect(Target, Range("b3:u21")) Is Nothing Then Exit Sub


'Area at the beginning of the column number
nL = IIf(Target.Column > 16, 17, IIf(Target.Column > 11, 12, IIf(Target.Column > 6, 7, 2)))
Application.EnableEvents = False


If Target = "" Then 'If the current cell is blank
Cells(Target.Row, nL).Resize(1, 5).ClearContents 'Removal of section original vote
Target = "X" 'vote
Else
Target.ClearContents 'If you click on a cell has to vote, clearing it
End If
Application.EnableEvents = True
End Sub

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved