Superscript Button

[Guest]

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]

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.

 

[Guest]

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.

 

[Dave Peterson]

I think that this breaks the "yes there is"

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.

 

[Guest]

How would I change macro1 to change the last character of a selected cell?
It currently changes the second and third characters.

 

[Dave Peterson]

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

 

[Guest]

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]

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.