Excel: difficulty getting macros to insert symbols

  Tony Cooper 14:47 13 Jan 06
Locked

I am using Ecxel as a database for bird records. Using 'm' for male or 'f' for female sometimes autocompletes to previously used words such as 'many' or 'few'. The obvious answer is to use symbols for male and female but these cannot be found in normal text fonts. It is therefore necessary to change to a symbol font and then back again, sometimes twice in the one cell, in order to type in normal text. When I tried to record macros for the male and female symbols in the form - change to symbol font > Alt+nnnn > change back to text font, at the end 'Stop Recording' is greyed out. Even so, the macro will insert the required symbol but all other entries in the same cell are then in the symbol font. Does anybody know how to write a macro to do the same job that does not suffer from this problem? Alternatively, does anybody know of a text font that contains the symbols for male and female in place of or in addition to the many symbols that I never use? This would avoid having to use macros.

  VoG II 15:03 13 Jan 06

Why not just turn off AutoComplete?

Tools|Options, Edit tab. Untick 'Enable AutoComplete for cell values'.

  Tony Cooper 16:53 13 Jan 06

Thanks for the suggestion, but the autocomplete is very useful in some of the fields, e.g. long place names. Tony Cooper

  VoG II 16:55 13 Jan 06

Can you post the macro that you have recorded. It will be in the Visual Basic Editor - ALT+F11 to open the VBE.

  VoG II 22:22 13 Jan 06

If it helps this is a macro that converts all numbers in the selected range to subscript. I think that this is a similar problem - it may be possible to adapt:

Sub CharFmt()
'********************************************************************************
' Converts all numbers in selected range of cells to subscript *
' Use to correctly format chemical formulas
'********************************************************************************
Dim tRow As Integer, bRow As Integer, fCol As Integer, lCol As Integer
Dim irow As Integer, icol As Integer, iChr As Integer, Title As String
If Selection.Areas.Count > 1 Then
MsgBox ("Only one area may be selected")
Exit Sub
End If
Application.ScreenUpdating = False
tRow = Selection.Row
bRow = tRow + Selection.Rows.Count - 1
fCol = Selection.Column
lCol = fCol + Selection.Columns.Count - 1
For icol = fCol To lCol
For irow = tRow To bRow
Title = ActiveSheet.Cells(irow, icol).Value
For iChr = 1 To Len(Title)
If Val(Mid(Title, iChr, 1)) <> 0 Then _
ActiveSheet.Cells(irow, icol).Characters(start:=iChr, Length:=1).Font.Subscript = True
Next iChr
Next irow
Next icol
Application.ScreenUpdating = True
End Sub

  Tony Cooper 10:28 14 Jan 06

My apologies, but I have never done any programming in Visual Basic, so do not understand your last posting.

An example of the kind of information I need to insert into one of the fields in my database is: 2m 3f 5juv, which is just 10 key strokes

To manually replace the 'm'& 'f' with symbols for male and female would require: 2 > change to symbol font (currently Arial Special G2) > Alt+0088 > change back to text font (currently Arial) > space > 3 > change to symbol font > Alt+0067 > change back to text font > space > 5juv, which is a total of 18 key strokes and a minimum of 8 mouse clicks. With several thousand records a year to be entered I do not bother doing this - hence the problem with autocomplete, which I do need to keep turned on for other fields in the database.

If I could successfully record macros for the symbols, the sequence would be 2 > Ctrl+m > space > 3 Ctrl+f > space > 5juv, which is just 12 key strokes.

The sequence I used when trying to record the macro was: Tools > Macro > Record New Macro > Macro name = male > Shortcut key = m > OK > click on Arial Special G2 > Alt+0088 > click on Arial > Tools > Macro - and that is as far as I can get because 'Stop Recording' is greyed out. The same sequence but with name 'f' & Alt+0067 for the female symbol.

Ctrl+m/f does insert the male/female symbol but anything else I type in the cell is also in symbols not text.

Am I correct in supposing that it is the attempt to change font that is causing the problem? Using a text font that includes symbols for male and female (if there is such a thing - I can't find one) would be the simplest solution.

Thanks again for all your trouble.

Tony Cooper

  VoG II 11:29 14 Jan 06

Sub CharFmt()
Dim tRow As Integer, bRow As Integer, fCol As Integer, lCol As Integer
Dim irow As Integer, icol As Integer, iChr As Integer, Title As String
If Selection.Areas.Count > 1 Then
MsgBox ("Only one area may be selected")
Exit Sub
End If
Application.ScreenUpdating = False
tRow = Selection.Row
bRow = tRow + Selection.Rows.Count - 1
fCol = Selection.Column
lCol = fCol + Selection.Columns.Count - 1
For icol = fCol To lCol
For irow = tRow To bRow
Title = ActiveSheet.Cells(irow, icol).Value
For iChr = 1 To Len(Title)
If Asc(Mid(Title, iChr, 1)) = 102 Then Cells(irow, icol).Replace what:=Chr(102), replacement:=Chr(67)
If Asc(Mid(Title, iChr, 1)) = 109 Then Cells(irow, icol).Replace what:=Chr(109), replacement:=Chr(88)
If Asc(Mid(Title, iChr, 1)) = 102 Or Asc(Mid(Title, iChr, 1)) = 109 Then _
ActiveSheet.Cells(irow, icol).Characters(Start:=iChr, Length:=1).Font.Name = "Arial Narrow Special G2"
Next iChr
Next irow
Next icol
Application.ScreenUpdating = True
End Sub


-----------------------------------------------

Open your workbook, ALT+F11 to open the VBE. Insert|Module. Select the above code and paste it into the module. Close the VBE.

Select the range of cells that you want to convert, Tools, Macro, Macros, click ChrFmt and click Run.

  VoG II 16:03 14 Jan 06

I should have said - backup your workbook before doing this!!

  Tony Cooper 16:44 14 Jan 06

Thanks, I had backed up. I did as you suggested and pasted the code in. I then selected the field (column) where I wish to use the male and female symbols and ran the macro. I immediately got an Error Message Run-time error '6' 'Overflow'. I clicked on 'Debug' and line 10 (which begins bRow = tRow was highlighted in yellow. Nothing had happened to any entries inthe highlighted column and when I tried Ctrl+m to see if I could get a male symbol, nothing happened.

When I then clicked on Tools > Macro to try and delete the macro, anything further was greyed out. Fortunately I had not 'saved' since adding the macro so was able to close without saving and thus get back to my original database.

I also notice that Line 20 refers to 'Arial Narrow Special G2' whereas the font I am using is just 'Arial Special G2'. I am assuming that I can edit that myself.

One thing does puzzle me and that is your reference to 'Select the range of cells that you want to convert'. What I really want to do is the ability to use male and female symbols in any new entries rather than to convert existing entries from previous years. Sometimes I may need to refer to males (or females) only and so was really looking for two separate macros, one for each symbol.

I am very appreciative of all the trouble you have gone to over this problem.

Thank you.

Tony Cooper

  VoG II 16:53 14 Jan 06

The reason that you got the error was that you selected an entire column. The macro is only expecting up to 255 rows to be selected. It would be possible to modify this but I think it would then take an age to run.

Yes, you can change to 'Arial Special G2'.

Can you then try it on a few cells containing m and f to see if it converts as you want. If it does then I can look at getting it to run when you enter something in a cell. Does a code like '2m 3f 5juv' only go in one column - if so which one?

  VoG II 17:12 14 Jan 06

Right click the sheet tab and select 'View Code'.

Delete anything that is already there and replace it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iChr As Integer
If Target.Column = 2 Then
Title = Target.Value
For iChr = 1 To Len(Title)
If Asc(Mid(Title, iChr, 1)) = 102 Then Target.Replace what:=Chr(102), replacement:=Chr(67)
If Asc(Mid(Title, iChr, 1)) = 109 Then Target.Replace what:=Chr(109), replacement:=Chr(88)
If Asc(Mid(Title, iChr, 1)) = 102 Or Asc(Mid(Title, iChr, 1)) = 109 Then _
Target.Characters(Start:=iChr, Length:=1).Font.Name = "Arial Special G2"
Next iChr
End If
End Sub

The line

If Target.Column = 2 Then

assumes that the column that contains your m/f codes is B (2). If it is actually, e.g. Z then change the 2 to 26.

Close the window. Now, typing the code into a cell in the 'code column' and pressing Enter will run the code.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…