Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
How do I assign a font change to selected charecters within a cell.
Example: A1: 623154 (as text) I highlight 231 and run the macro and get this result 6²³¹54 I've been experimenting with macros lately and I wanted to make a superscript and subscript button. Here is some code I recorded (minus the junk). It is extremely simple, but I wanted to know if there was a way to apply the font change to only selected charecters in a cell. Sub Macro1() With ActiveCell.Characters(Start:=2, Length:=2).Font .Superscript = True End With End Sub Sub Macro3() With Selection.Font .Subscript = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
If this is a learning exercise, then this may be too much help:
From John Walkenbach: http://j-walk.com/ss/excel/files/supersub.htm == The bad thing is that there isn't any macro that does anything while you're editing the cell. Sloth wrote: How do I assign a font change to selected charecters within a cell. Example: A1: 623154 (as text) I highlight 231 and run the macro and get this result 6²³¹54 I've been experimenting with macros lately and I wanted to make a superscript and subscript button. Here is some code I recorded (minus the junk). It is extremely simple, but I wanted to know if there was a way to apply the font change to only selected charecters in a cell. Sub Macro1() With ActiveCell.Characters(Start:=2, Length:=2).Font .Superscript = True End With End Sub Sub Macro3() With Selection.Font .Subscript = True End With End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
Yes there is a way. I believe you should be able to capture the selection
range of a given cell, and then you could piece the text together using Left, Mid, and Right and applying the appropriate font changes to each section of that grouping. "Sloth" wrote: How do I assign a font change to selected charecters within a cell. Example: A1: 623154 (as text) I highlight 231 and run the macro and get this result 6²³¹54 I've been experimenting with macros lately and I wanted to make a superscript and subscript button. Here is some code I recorded (minus the junk). It is extremely simple, but I wanted to know if there was a way to apply the font change to only selected charecters in a cell. Sub Macro1() With ActiveCell.Characters(Start:=2, Length:=2).Font .Superscript = True End With End Sub Sub Macro3() With Selection.Font .Subscript = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
I think that this breaks the "yes there is"
but I wanted to know if there was a way to apply the font change to only selected charecters in a cell. GB wrote: Yes there is a way. I believe you should be able to capture the selection range of a given cell, and then you could piece the text together using Left, Mid, and Right and applying the appropriate font changes to each section of that grouping. "Sloth" wrote: How do I assign a font change to selected charecters within a cell. Example: A1: 623154 (as text) I highlight 231 and run the macro and get this result 6²³¹54 I've been experimenting with macros lately and I wanted to make a superscript and subscript button. Here is some code I recorded (minus the junk). It is extremely simple, but I wanted to know if there was a way to apply the font change to only selected charecters in a cell. Sub Macro1() With ActiveCell.Characters(Start:=2, Length:=2).Font .Superscript = True End With End Sub Sub Macro3() With Selection.Font .Subscript = True End With End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
How would I change macro1 to change the last character of a selected cell?
It currently changes the second and third characters. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
How about the last character of each cell in the selection. (If you only select
one cell, it'll only do that one cell.) Option Explicit Sub Macro1A() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No non-numeric constants in selection" Exit Sub End If For Each myCell In myRng.Cells With myCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Next myCell End Sub But if you really just want the activecell: With ActiveCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Sloth wrote: How would I change macro1 to change the last character of a selected cell? It currently changes the second and third characters. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
I don't know if you will read this but I wanted to thank you for your help.
Here is the code I ended up with (If you are interested). I edited it according to my personal likings (I prefer not to have any error msgs, as I am the only one who uses it, and I will know when and why I can't :) ). The only problem I had was the Len(.value) did not work, so I replaced it with LEN(myCell). Is that because I removed the With...End With? Also, what is the Option Explicit command for? Sub Superscript() On Error Resume Next Dim myCell As Range For Each myCell In Selection.Cells myCell.Characters(Start:=Len(myCell), Length:=1).Font.Superscript = True Next myCell End Sub Sub Subscript() On Error Resume Next Dim myCell As Range For Each myCell In Selection.Cells myCell.Characters(Start:=Len(myCell), Length:=1).Font.Subscript = True Next myCell End Sub Sub PlainText() On Error Resume Next Selection.Font.Subscript = False Selection.Font.Superscript = False End Sub "Dave Peterson" wrote: How about the last character of each cell in the selection. (If you only select one cell, it'll only do that one cell.) Option Explicit Sub Macro1A() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No non-numeric constants in selection" Exit Sub End If For Each myCell In myRng.Cells With myCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Next myCell End Sub But if you really just want the activecell: With ActiveCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Sloth wrote: How would I change macro1 to change the last character of a selected cell? It currently changes the second and third characters. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Superscript Button
Option Explicit forces you to declare your variables. So if excel finds
something that looks like a variable and it's not declared, your code won't compile and you'll be shown an error. It may seem like more work, but if you've ever spent time trying to determine why: ctr1 = ctrll + 1 didn't work the way you hoped, you'll see the savings. (one of those is ctr-one and the other is ctr-ELL). And it's the removal of the with/end with that caused the problem. the things with leading dots belong to the object in the previous With. Sloth wrote: I don't know if you will read this but I wanted to thank you for your help. Here is the code I ended up with (If you are interested). I edited it according to my personal likings (I prefer not to have any error msgs, as I am the only one who uses it, and I will know when and why I can't :) ). The only problem I had was the Len(.value) did not work, so I replaced it with LEN(myCell). Is that because I removed the With...End With? Also, what is the Option Explicit command for? Sub Superscript() On Error Resume Next Dim myCell As Range For Each myCell In Selection.Cells myCell.Characters(Start:=Len(myCell), Length:=1).Font.Superscript = True Next myCell End Sub Sub Subscript() On Error Resume Next Dim myCell As Range For Each myCell In Selection.Cells myCell.Characters(Start:=Len(myCell), Length:=1).Font.Subscript = True Next myCell End Sub Sub PlainText() On Error Resume Next Selection.Font.Subscript = False Selection.Font.Superscript = False End Sub "Dave Peterson" wrote: How about the last character of each cell in the selection. (If you only select one cell, it'll only do that one cell.) Option Explicit Sub Macro1A() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No non-numeric constants in selection" Exit Sub End If For Each myCell In myRng.Cells With myCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Next myCell End Sub But if you really just want the activecell: With ActiveCell .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True End With Sloth wrote: How would I change macro1 to change the last character of a selected cell? It currently changes the second and third characters. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toolbar button for superscript | Excel Discussion (Misc queries) | |||
Superscript Button and Subscript Button | Excel Discussion (Misc queries) | |||
Button for subscript and superscript in Excel | Excel Discussion (Misc queries) | |||
Subscript & Superscript | Excel Discussion (Misc queries) |