Hi Basil
Public Function TextStyle(Reference As Range)
TextStyle = Range(Reference).Font.FontStyle
End Function

Cordially
Pascal

"Basil" a écrit dans le message de
...
Hi,

I've been looking for a function that will return the style of text within
a cell, but I can't find one!

I am aware that the text within a single cell can actually have multiple
styles, but I am not concerned with this.

So, I tried to create my own function in VBA which nearly works - but I
need help.

The VBA code in my personal module is as follows:

Public Function TextStyle(Reference)
TextStyle = Range(Reference).Font.FontStyle
End Function

The problem as you may have guessed is that Reference will return the
contents of the cell rather than the address (A1 or whatever). A way I
found around this was to change the way I use the function on the worksheet
eg:
=PERSONAL.XLS!TextStyle(ADDRESS(ROW(A1),COLUMN(A1) ,4))

This works, but I would rather just have it so I can say:
=PERSONAL.XLS!TextStyle(A1)

This is the first time I've actually created my own function in Excel, so
any help tips would be really welcomed.

Many thanks,

Basil
PS The function as is would return 0 for cells holding multiple styles

Basil,

Here is something that you can modify to return what you want:

Use it like

=TextStyle(A1)

Changing the font doesn't trigger any event within Excel that you can
capture to force a recalc. The best you can do is force a recalc when the
selection changes...

Public Function TextStyle(Reference As Range) As String
With Reference.Font
If .Bold Then TextStyle = " Bold"
If .Italic Then TextStyle = TextStyle & " Italic"
If .Shadow Then TextStyle = TextStyle & " Shadow"
If .Underline = xlUnderlineStyleSingle Then TextStyle = TextStyle & "
Underline"

TextStyle = TextStyle & Chr(10) & "Name: " & .Name & Chr(10)
TextStyle = TextStyle & "ColorIndex: " & .ColorIndex & Chr(10)
TextStyle = TextStyle & "FontStyle: " & .FontStyle
End With
TextStyle = Trim(TextStyle)
End Function

HTH,
Bernie
MS Excel MVP

"Basil" wrote in message
...
I tried that already - it doesn't work (generate #VALUE error - I assume
because it expects Reference to be a range but it isn't - it's the cell
contents.)

Any other ideas?

Oh, also, do you know how I can get it to automatically update if I change
the target's font style?

Basil
PS when I say font style, I mean Bold/Italic etc

"papou" wrote:

Hi Basil
Public Function TextStyle(Reference As Range)
TextStyle = Range(Reference).Font.FontStyle
End Function

Cordially
Pascal

"Basil" a écrit dans le message de
...
Hi,

I've been looking for a function that will return the style of text
within
a cell, but I can't find one!

I am aware that the text within a single cell can actually have
multiple
styles, but I am not concerned with this.

So, I tried to create my own function in VBA which nearly works - but
I
need help.

The VBA code in my personal module is as follows:

Public Function TextStyle(Reference)
TextStyle = Range(Reference).Font.FontStyle
End Function

The problem as you may have guessed is that Reference will return the
contents of the cell rather than the address (A1 or whatever). A way I
found around this was to change the way I use the function on the
worksheet
eg:
=PERSONAL.XLS!TextStyle(ADDRESS(ROW(A1),COLUMN(A1) ,4))

This works, but I would rather just have it so I can say:
=PERSONAL.XLS!TextStyle(A1)

This is the first time I've actually created my own function in Excel,
so
any help tips would be really welcomed.

Many thanks,

Basil
PS The function as is would return 0 for cells holding multiple styles

Hi Pascal,

Change

TextStyle = Range(Reference).Font.FontStyle

to

TextStyle = ReferenceFont.FontStyle

You have already declared Reference as a range object.


---
Regards,
Norman



"papou" wrote in message
...
Hi Basil
Public Function TextStyle(Reference As Range)
TextStyle = Range(Reference).Font.FontStyle
End Function

Cordially
Pascal

"Basil" a écrit dans le message de
...
Hi,

I've been looking for a function that will return the style of text
within
a cell, but I can't find one!

I am aware that the text within a single cell can actually have multiple
styles, but I am not concerned with this.

So, I tried to create my own function in VBA which nearly works - but I
need help.

The VBA code in my personal module is as follows:

Public Function TextStyle(Reference)
TextStyle = Range(Reference).Font.FontStyle
End Function

The problem as you may have guessed is that Reference will return the
contents of the cell rather than the address (A1 or whatever). A way I
found around this was to change the way I use the function on the
worksheet
eg:
=PERSONAL.XLS!TextStyle(ADDRESS(ROW(A1),COLUMN(A1) ,4))

This works, but I would rather just have it so I can say:
=PERSONAL.XLS!TextStyle(A1)

This is the first time I've actually created my own function in Excel,
so
any help tips would be really welcomed.

Many thanks,

Basil
PS The function as is would return 0 for cells holding multiple styles

Thank you everyone. It works lovely now.

"Norman Jones" wrote:

Hi Pascal,

Change

TextStyle = Range(Reference).Font.FontStyle

to

TextStyle = ReferenceFont.FontStyle

You have already declared Reference as a range object.


---
Regards,
Norman



"papou" wrote in message
...
Hi Basil
Public Function TextStyle(Reference As Range)
TextStyle = Range(Reference).Font.FontStyle
End Function

Cordially
Pascal

"Basil" a écrit dans le message de
...
Hi,

I've been looking for a function that will return the style of text
within
a cell, but I can't find one!

I am aware that the text within a single cell can actually have multiple
styles, but I am not concerned with this.

So, I tried to create my own function in VBA which nearly works - but I
need help.

The VBA code in my personal module is as follows:

Public Function TextStyle(Reference)
TextStyle = Range(Reference).Font.FontStyle
End Function

The problem as you may have guessed is that Reference will return the
contents of the cell rather than the address (A1 or whatever). A way I
found around this was to change the way I use the function on the
worksheet
eg:
=PERSONAL.XLS!TextStyle(ADDRESS(ROW(A1),COLUMN(A1) ,4))

This works, but I would rather just have it so I can say:
=PERSONAL.XLS!TextStyle(A1)

This is the first time I've actually created my own function in Excel,
so
any help tips would be really welcomed.

Many thanks,

Basil
PS The function as is would return 0 for cells holding multiple styles