I use Excel 2000 with Windows 2000 pro. Is there a pre loaded shortcut for making superscripts?
Much like I can use control+i to change back and forth between regular font and italics within the same cell, I want to be able to do that with superscripts. I have been able to format the entire cell for superscripts, auto produce a superscript as the last character within a cell and perform individual resets within cell via the mouse (by shading, format, check/uncheck superscript box). But I want a shortcut key.
Macros have been fairly easy for me to set in the past, but I haven't been able to produce a macro for this. I don't know enough about editing macros to solve my dilemma, but I'm willing to learn.
I don't know if there is a shortcut or not. In Word you can highlight the character(s) and then format font, click on superscript. I suspect Excel would be similar. You can also use the format painter tool.
As for macros, click on the record button, perform your action, and stop recording. Go look at the macro and make appropriate changes as you see fit.
Here is what the macro should look like: (I'm using Office 2007 but it should be similar in 2000). I deleted all the extra junk. I put x2 in cell A1 (meant to be x square). You can assign a hot key to the macro as well. Good luck.
ActiveCell.FormulaR1C1 = "x2"
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Superscript = True
Thanks for the help. I'm learning a lot now about VBA code. Your code did exactly what it is supposed to do, as I read it. (the pic below is the result) But unfortunately, it's not what I want.
-- I need the macro to be available anywhere on the sheet, so we can do without the ranges.
-- I don't want the font to match that of the active cell. It's to be always Tahoma.
-- Both regular font characters and superscripted numbers are variable, in both number of characters and the characters themselves.
For example, I might want one cell to read:
and another: Lilium martagon³
So the best I've done so far is:
' Macro7 Macro
' Macro recorded 1/4/2010 by Rick
' Keyboard Shortcut: Ctrl+t
ActiveCell.FormulaR1C1 = " "
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Tahoma"
.superscript = True
But that still changes the entire cell to superscripting. Am I misreading your example? I want to be able to use the macro multiple times within one cell. If that means one macro to change to superscript and a second to change back to the cell's original font, that would be fine.
Yes, the highlighting, then format, font, superscript with the mouse does work (and back again too), but I'd like to not bother with the mouse. Is it possible?
You will have to modify the macro as needed. The code I provided was just for starters based on what limited information you gave in your original post.
The cell formula can be changed to the ActiveCell as you found out. Just eliminate the range("ColumnRow").select statement. Same for font name, font size, etc. but then you didn't ask for that in the original post.
The selection of characters to be formatted is in the Start:=xStart, Length:=xLength part which is fairly explanatory. The xStart is where to start in the string and the xLength is how many characters to select. I don't think you can do a multiple select on text though (maybe you can but I've never tried it). So you might have to do the same type of logic multiple times to do multiple superscripts in the same cell. The other thing you can do is eliminate the select in the code, let the user do the select, and then the hot key or run the macro (or put a command button on the sheet or a selection on the toolbar). If the location will always be the same then you can hard code the selects in your macro.
The other thing you can do is click in your cell and if all the numbers are to be superscripted then you would work across the string, selecting numbers only and applying the formatting. Or (and) if you want to superscript the last character then you would add in the logic to select that. You would just have to hit the hot key or a command button or tool on the toolbar. How's that for simplicity? Piece of cake?
Do you want to be able to make a superscript like this: ³ - within a cell?
Google "alt codes" and you should find lists of all sorts of 4-digit numbers that you can enter while holding down the alt key. The superscript ³ is alt plus 0179. I use this when I sign my signature here as tl³. Alt plus 0176 is the ° sign for °F or °C. There is also ® which is alt plus 0174 and ™ is alt plus 0153. There are many, many alt codes for making just about any special or numerical superscript symbol.
I'm not sure if this is exactly what you're looking for, but thought I would ask.
I am not sure you can insert special characters like that (using ALT plus a sequence) in Excel. You can insert symbols from a list of special characters though. For superscript it is a toss up on whether to go this route or just select, format, and choose superscript. Special characters have to use the insert symbol though (or copy and paste from somewhere else).
Here is some more code that only superscripts numbers and periods. You could also add checks for other characters such as negative signs, engineering notation, etc.
For j = 1 To Len(ActiveCell)
If IsNumeric(Mid(ActiveCell, j, 1)) Or Mid(ActiveCell, j, 1) = "." Then
ActiveCell.Characters(j, 1).Font.superscript = True
This is most strange. I edited this post to add white space because it looked like it had been stripped but it was still there when I edit. I guess DG removes white space at the beginning of lines for display? Pretty silly.
My problem had been that I could not apply a macro while typing in the cell. Thus the cell would be all superscript or all not, because I could only apply the macro in the beginning (or at the end by re activating the cell).
hcmcdole, your new suggested code works great! It saves me a whole wad of steps! Thank you!
I see that your if-then sentence is quite long. Yes, DG does remove spaces at the beginning of a line, and extra spaces in between characters in mid sentence.
Mike, alt codes do work in my Excel program, but there are only alt codes for superscript 1,2 and 3.
P.S. I see there are bits and pieces of VBA code references all over the internet. Does anyone have a preferred one that they use?
You could substitute a variable for part of the code to simplify matters such as:
x = Mid(ActiveCell, j, 1))
If IsNumeric(x) Or x = "." Then
ActiveCell.Characters(j, 1).Font.superscript = True
What do you mean by "Does anyone have a preferred one that they use?" VBA is Microsoft's standardization of macro code. In the past Word had its own macro language and Excel had its own, etc. So a programmer would have to learn all the different macro languages if they programmed for each application. Bill Gates said they needed one standard language and VB became the underlying language. The objects are different between each application but the concept is the same. It made life so much simpler going between Access, Excel, Word, etc. when doing macros.
From the little internet perusing I've done, I did get the idea that for word and for excel was different somehow. Thanks for clearing that up.
I was looking for a maybe better code list to refer to and learn from. I really don't know much about the whole language, but it bears a similarity to the (very) old Basic language. I thought maybe it's not that difficult. Actually, the built in help for macros in the excel program is fairly explanatory.
VB has come a long way from plain old Basic. It's great for doing forms and is fairly robust. If it isn't robust enough or something you can't do you can build a DLL in C++ and call it from VB (a bit awkward due to the way both languages use the stack but doable). It has been a while since I used to do this but the speed in algorithms came from 'C' and the forms all came from VB. The same can be applied to VBA but in this case you also use the power of Excel or any add in.
If you work in it for a while it becomes easy (easier at least). You know what you want to accomplish and you know the basic tools which is a good starting point. Then you add in controls or references to do the heavy lifting for you. Buy a book on using VBA for whatever app you are programming for at a local book store. It is a great help on getting started too.