Format Individual Characters in a Cell
 

I have a range named Conditions: Cells AB37:AD46.
The AB:AD columns are merged, so AB37:Ad37 appears to be one cell,
AB38:AD38 appears to be another cell, etc.

I have a macro that is supposed to format the first and last character in
Wingdings Font, and the 2nd to 5th characters in "Wingdings 2" font. But
nothing's happening.
The correct range is being selected, the macro is being triggered and
steeped through, but the cell format doesn't change at all.

It seems to be something to do with a UDF I have in the sheet.
When the UDF is volatile, the format macro doesn't work. When it's not
volatile, it does.

I've tried using a global variable to set the volatile True or False as
needed, but I must be doing it wrong.
Can anyone help?
Thanks in advance,
Darren

It's Excel XP by the way.
The macros:

In the sheet's Worksheet_Change event I have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("Conditions"), Target) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Call FormatConditions(Target)
End If
End Sub

And in Module1, the FormatConditions macro looks like this:

Sub FormatConditions(myRange As Range)

'Application.ScreenUpdating = False
'(commented out so I can see what's happening)
Dim myaddress As String
myaddress = myRange.Address
' so I can see the correct range is selected: it is.
myaddress = myRange.MergeArea.Address
With myRange
.Font.Name = "Wingdings 2"
.Font.Bold = False
.Font.ColorIndex = vbRed
If .Characters.Count 0 Then _
.Characters(Start:=1, Length:=1).Font.Name = "Wingdings"
.Characters(Start:=1, Length:=1).Font.Color = vbBlack
If .Characters.Count 5 Then _
.Characters(Start:=6, Length:=1).Font.Name = "Wingdings"
End With
Application.ScreenUpdating = True
End Sub

The troublesume UDF (which does it's job fine):

Function CountStunts(Optional AssignedOrNot As Boolean = True)
Application.Volatile
Application.EnableEvents = False
Dim mySheet As Worksheet
'Set mySheet = ActiveSheet
Set mySheet = Application.Caller.Parent
Dim CountAssigned As Integer, CountUnassigned As Integer, tempStunts As
Integer
Dim mycelladdress As String
Dim myCell As Range, StuntRange As Range
CountStunts = 0: CountAssigned = 0: CountUnassigned = 0

Set StuntRange =
mySheet.Range("$H$20:$H$23,$H$25:$H$27,$H$29:$H$31 ,$H$33:$H$35,$H$37:$H$39,$H$41:$H$43,$H$45:$H$46,$ H$48:$H$49,$H$51:$H$52,$H$54:$H$55,$H$57:$H$58,$H$ 60:$H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73 ,$H$75")
Set StuntRange = Application.Union(StuntRange,
mySheet.Range("$P$20:$P$23,$P$25:$P$27,$P$29:$P$31 ,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P$45:$P$46,$ P$48:$P$49,$P$51:$P$52,$P$54:$P$55,$P$57:$P$58,$P$ 60:$P$61,$P$63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73 ,$P$75"))

For Each myCell In StuntRange
mycelladdress = myCell.Address
If myCell < "" Then
tempStunts = Len(myCell.Text)
If myCell.Offset(0, -5).Value < "" Then
CountAssigned = CountAssigned + tempStunts
Else
CountUnassigned = CountUnassigned + tempStunts
End If
End If

Next myCell
CountStunts = CountUnassigned
If AssignedOrNot Then CountStunts = CountAssigned
Application.EnableEvents = True
End Function

Format Individual Characters in a Cell
 

Darren,

Haven't checked out your code but if you take the cell references out of the
UDF and pass them as an argument instead, you could remove the volatile
clause.

--
HTH

Bob Phillips

"Darren Hill" wrote in message
news:opsqhzx9zved89cl@omega...
I have a range named Conditions: Cells AB37:AD46.
The AB:AD columns are merged, so AB37:Ad37 appears to be one cell,
AB38:AD38 appears to be another cell, etc.

I have a macro that is supposed to format the first and last character in
Wingdings Font, and the 2nd to 5th characters in "Wingdings 2" font. But
nothing's happening.
The correct range is being selected, the macro is being triggered and
steeped through, but the cell format doesn't change at all.

It seems to be something to do with a UDF I have in the sheet.
When the UDF is volatile, the format macro doesn't work. When it's not
volatile, it does.

I've tried using a global variable to set the volatile True or False as
needed, but I must be doing it wrong.
Can anyone help?
Thanks in advance,
Darren

It's Excel XP by the way.
The macros:

In the sheet's Worksheet_Change event I have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("Conditions"), Target) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Call FormatConditions(Target)
End If
End Sub

And in Module1, the FormatConditions macro looks like this:

Sub FormatConditions(myRange As Range)

'Application.ScreenUpdating = False
'(commented out so I can see what's happening)
Dim myaddress As String
myaddress = myRange.Address
' so I can see the correct range is selected: it is.
myaddress = myRange.MergeArea.Address
With myRange
.Font.Name = "Wingdings 2"
.Font.Bold = False
.Font.ColorIndex = vbRed
If .Characters.Count 0 Then _
.Characters(Start:=1, Length:=1).Font.Name = "Wingdings"
.Characters(Start:=1, Length:=1).Font.Color = vbBlack
If .Characters.Count 5 Then _
.Characters(Start:=6, Length:=1).Font.Name = "Wingdings"
End With
Application.ScreenUpdating = True
End Sub

The troublesume UDF (which does it's job fine):

Function CountStunts(Optional AssignedOrNot As Boolean = True)
Application.Volatile
Application.EnableEvents = False
Dim mySheet As Worksheet
'Set mySheet = ActiveSheet
Set mySheet = Application.Caller.Parent
Dim CountAssigned As Integer, CountUnassigned As Integer, tempStunts As
Integer
Dim mycelladdress As String
Dim myCell As Range, StuntRange As Range
CountStunts = 0: CountAssigned = 0: CountUnassigned = 0

Set StuntRange =

mySheet.Range("$H$20:$H$23,$H$25:$H$27,$H$29:$H$31 ,$H$33:$H$35,$H$37:$H$39,$
H$41:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$ 54:$H$55,$H$57:$H$58,$H$60
:$H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$ H$75")
Set StuntRange = Application.Union(StuntRange,

mySheet.Range("$P$20:$P$23,$P$25:$P$27,$P$29:$P$31 ,$P$33:$P$35,$P$37:$P$39,$
P$41:$P$43,$P$45:$P$46,$P$48:$P$49,$P$51:$P$52,$P$ 54:$P$55,$P$57:$P$58,$P$60
:$P$61,$P$63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73,$ P$75"))

For Each myCell In StuntRange
mycelladdress = myCell.Address
If myCell < "" Then
tempStunts = Len(myCell.Text)
If myCell.Offset(0, -5).Value < "" Then
CountAssigned = CountAssigned + tempStunts
Else
CountUnassigned = CountUnassigned + tempStunts
End If
End If

Next myCell
CountStunts = CountUnassigned
If AssignedOrNot Then CountStunts = CountAssigned
Application.EnableEvents = True
End Function

Format Individual Characters in a Cell
 

On Mon, 9 May 2005 09:29:50 +0100, Bob Phillips
wrote:

Darren,

Haven't checked out your code but if you take the cell references out of
the
UDF and pass them as an argument instead, you could remove the volatile
clause.

The UDF is triggered: =CountStunts() - nothing in the brackets.
There are 36 seperate, non-contiguous ranges, so I hardwired them in the
UDF rather than typing them in as parameters. If that's the only way to do
it, though...

Darren

Format Individual Characters in a Cell
 

Create a workbook name for that range, then you can just use

=CountStunts(myRange)

BTW that UDF name is a bit close to the mark, bit like the old Metallica
album :-)

--
HTH

Bob Phillips

"Darren Hill" wrote in message
news:opsqh24ffxed89cl@omega...
On Mon, 9 May 2005 09:29:50 +0100, Bob Phillips
wrote:

Darren,

Haven't checked out your code but if you take the cell references out of
the
UDF and pass them as an argument instead, you could remove the volatile
clause.

The UDF is triggered: =CountStunts() - nothing in the brackets.
There are 36 seperate, non-contiguous ranges, so I hardwired them in the
UDF rather than typing them in as parameters. If that's the only way to do
it, though...

Darren

Format Individual Characters in a Cell
 

Brilliant idea - and yes, I had to doublecheck my typing, too :)

Darren

On Mon, 9 May 2005 11:46:03 +0100, Bob Phillips
wrote:

Create a workbook name for that range, then you can just use

=CountStunts(myRange)

BTW that UDF name is a bit close to the mark, bit like the old Metallica
album :-)




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Format Individual Characters in a Cell
 

By the way, what's the best way to create local named ranges?
I can loop through the sheets I need to, but I don't know how to add the
worksheet range to each of 12 sheets.
I've tried to create local (single sheet) named ranges via code before,
but can rarely get it to work.

Thanks
Darren

On Mon, 9 May 2005 11:46:03 +0100, Bob Phillips
wrote:

Create a workbook name for that range, then you can just use

=CountStunts(myRange)

BTW that UDF name is a bit close to the mark, bit like the old Metallica
album :-)




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Format Individual Characters in a Cell
 

Darren,

When creating the name (InsertNameDefine...), make sure the correct sheet
is active, and precede the name with the sheet name

'sheet name'!myRange

In code, use

ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:="=$A$1:$H$10"


--
HTH

Bob Phillips

"Darren Hill" wrote in message
news:opsqh8nsn6ed89cl@omega...

By the way, what's the best way to create local named ranges?
I can loop through the sheets I need to, but I don't know how to add the
worksheet range to each of 12 sheets.
I've tried to create local (single sheet) named ranges via code before,
but can rarely get it to work.

Thanks
Darren

On Mon, 9 May 2005 11:46:03 +0100, Bob Phillips
wrote:

Create a workbook name for that range, then you can just use

=CountStunts(myRange)

BTW that UDF name is a bit close to the mark, bit like the old Metallica
album :-)




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Format Individual Characters in a Cell
 

On Mon, 9 May 2005 12:27:06 +0100, Bob Phillips
wrote:

ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:="=$A$1:$H$10"

When I try this I get an application or object-defined error.

I've tried using:
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:=StuntRangeAddress
with StuntRange a string variable containing the address.

I've also tried (wait for it...):
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:="=$H$20:$H$23,$H$25:$H$27,$H$29:$H$31,$H $33:$H$35,$H$37:$H$39,$H$41:$H$43,$H$45:$H$46,$H$4 8:$H$49,$H$51:$H$52,$H$54:$H$55,$H$57:$H$58,$H$60: $H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$H $75,$P$20:$P$23,$P$25:$P$27,$P$29:$P$31,$P$33:$P$3 5,$P$37:$P$39,$P$41:$P$43,$P$45:$P$46,$P$48:$P$49, $P$51:$P$52,$P$54:$P$55,$P$57:$P$58,$P$60:$P$61,$P $63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73,$P$75"

Is the range address too long?
Is there a way to create a small range, than add extra ranges to it?

Darren

Format Individual Characters in a Cell
 

I figured out a solution, Bob.
I used:
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!StuntRange",
_
RefersTo:="=$H$20:$H$75,$P$20:$P$75" 'StuntRangeAddress
Then rewrote the CountStunts function to exclude the ranges it should
ignore.

Now it all works fine. Thanks for your help - couldn't have done it
without you (and the Metallica reference was fun, too). :)

Darren

Format Individual Characters in a Cell
 

"Darren Hill" wrote in message
news:opsqiaim06ed89cl@omega...

When I try this I get an application or object-defined error.

I've tried using:
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:=StuntRangeAddress
with StuntRange a string variable containing the address.

You have to include = so

ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:= "=" & StuntRangeAddress


I've also tried (wait for it...):
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _

RefersTo:="=$H$20:$H$23,$H$25:$H$27,$H$29:$H$31,$H $33:$H$35,$H$37:$H$39,$H$4
1:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$54: $H$55,$H$57:$H$58,$H$60:$H
$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$H$7 5,$P$20:$P$23,$P$25:$P$27,
$P$29:$P$31,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P $45:$P$46,$P$48:$P$49,$P$5
1:$P$52,$P$54:$P$55,$P$57:$P$58,$P$60:$P$61,$P$63: $P$64,$P$66:$P$67,$P$69,$P
$71,$P$73,$P$75"

Is the range address too long?
Is there a way to create a small range, than add extra ranges to it?

That worked okay for me.

Format Individual Characters in a Cell
 

Hope you don't mind the inline response

On Mon, 9 May 2005 12:55:19 +0100, Bob Phillips
wrote:


"Darren Hill" wrote in message
news:opsqiaim06ed89cl@omega...

When I try this I get an application or object-defined error.

I've tried using:
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:=StuntRangeAddress
with StuntRange a string variable containing the address.

You have to include = so

ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:= "=" & StuntRangeAddress


My stuntrangeaddress did include an "=" in the variable.
I've never been able to get it to accept variables. I must be doing
something wrong, but I can't see waht. Even when I copy directly from
other people's examples, it doesn't work.


I've also tried (wait for it...):
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _

RefersTo:="=$H$20:$H$23,$H$25:$H$27,$H$29:$H$31,$H $33:$H$35,$H$37:$H$39,$H$4
1:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$54: $H$55,$H$57:$H$58,$H$60:$H
$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$H$7 5,$P$20:$P$23,$P$25:$P$27,
$P$29:$P$31,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P $45:$P$46,$P$48:$P$49,$P$5
1:$P$52,$P$54:$P$55,$P$57:$P$58,$P$60:$P$61,$P$63: $P$64,$P$66:$P$67,$P$69,$P
$71,$P$73,$P$75"

Is the range address too long?
Is there a way to create a small range, than add extra ranges to it?

That worked okay for me.

Hmm, it failed for me. Maybe there's something else in my code that is
causing it to fail at this point?
Anyway, I found a way to work around it, so I'm happy - thanks again. :)

Darren

Format Individual Characters in a Cell
 

Hi Darren,

It's not possible to define a range name using a string of more than 255.
However the limit for a multiarea name is not limited by its address length
but by the number of areas, and your address produces considerably less than
that. There are various ways of defining such a name but for your purposes
break the string into "meaningful" address's of less than 255 (without the
"=") and try something like:

ActiveSheet.Names.Add "MyName", Union(Range(sAddr1), Range(sAddr1))

Note that later you won't be able to edit this manually but in all other
respects it should work like a normal name.

Regards,
Peter T


"Darren Hill" wrote in message
news:opsqibpzlked89cl@omega...
Hope you don't mind the inline response

On Mon, 9 May 2005 12:55:19 +0100, Bob Phillips
wrote:


"Darren Hill" wrote in message
news:opsqiaim06ed89cl@omega...

When I try this I get an application or object-defined error.

I've tried using:
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:=StuntRangeAddress
with StuntRange a string variable containing the address.

You have to include = so

ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _
RefersTo:= "=" & StuntRangeAddress


My stuntrangeaddress did include an "=" in the variable.
I've never been able to get it to accept variables. I must be doing
something wrong, but I can't see waht. Even when I copy directly from
other people's examples, it doesn't work.


I've also tried (wait for it...):
ActiveSheet.Names.Add Name:="'" & ActiveSheet.Name & "'!myRange", _


RefersTo:="=$H$20:$H$23,$H$25:$H$27,$H$29:$H$31,$H $33:$H$35,$H$37:$H$39,$H$4

1:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$54: $H$55,$H$57:$H$58,$H$60:$H

$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$H$7 5,$P$20:$P$23,$P$25:$P$27,

$P$29:$P$31,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P $45:$P$46,$P$48:$P$49,$P$5

1:$P$52,$P$54:$P$55,$P$57:$P$58,$P$60:$P$61,$P$63: $P$64,$P$66:$P$67,$P$69,$P
$71,$P$73,$P$75"

Is the range address too long?
Is there a way to create a small range, than add extra ranges to it?

That worked okay for me.

Hmm, it failed for me. Maybe there's something else in my code that is
causing it to fail at this point?
Anyway, I found a way to work around it, so I'm happy - thanks again. :)

Darren

Format Individual Characters in a Cell
 

Typo:

ActiveSheet.Names.Add "MyName", Union(Range(sAddr1), Range(sAddr1))

should read

ActiveSheet.Names.Add "MyName", Union(Range(sAddr1), Range(sAddr2))

Peter T

Format Individual Characters in a Cell
 

It took me a good 30 seconds studying to notice the difference there :)
Thanks for the explanation of what was going on.
So there is a way of bundling several ranges at ocne - thanks, that'll
help a lot.

Darren

On Mon, 9 May 2005 16:18:42 +0100, Peter T <peter_t@discussions wrote:

Typo:

ActiveSheet.Names.Add "MyName", Union(Range(sAddr1), Range(sAddr1))

should read

ActiveSheet.Names.Add "MyName", Union(Range(sAddr1), Range(sAddr2))

Peter T