Superscript Button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sloth - 13 Jan 2006 15:10 GMT

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

Reply to this Message

Dave Peterson - 13 Jan 2006 17:15 GMT

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.

> How do I assign a font change to selected charecters within a cell.
> Example:
[quoted text clipped - 17 lines]
> End With
> End Sub

Signature

Dave Peterson

Reply to this Message

GB - 13 Jan 2006 20:43 GMT

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.

> How do I assign a font change to selected charecters within a cell.
> Example:
[quoted text clipped - 17 lines]
> End With
> End Sub

Reply to this Message

Dave Peterson - 13 Jan 2006 23:02 GMT

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.

> 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,
[quoted text clipped - 22 lines]
> > End With
> > End Sub

Signature

Dave Peterson

Reply to this Message

Sloth - 13 Jan 2006 23:18 GMT

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

Reply to this Message

Dave Peterson - 13 Jan 2006 23:35 GMT

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

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

Signature

Dave Peterson

Reply to this Message

Sloth - 20 Jan 2006 21:12 GMT

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

> How about the last character of each cell in the selection. (If you only select
> one cell, it'll only do that one cell.)
[quoted text clipped - 30 lines]
> > How would I change macro1 to change the last character of a selected cell?
> > It currently changes the second and third characters.

Reply to this Message

Dave Peterson - 20 Jan 2006 22:39 GMT

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.

> 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
[quoted text clipped - 62 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message