Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change format for an individual word in a cell | Excel Discussion (Misc queries) | |||
Format individual coloumn cell with corresponding coloum cell resu | Excel Worksheet Functions | |||
Format row color based on individual cell | Excel Discussion (Misc queries) | |||
hiding individual characters in Excel | Excel Discussion (Misc queries) | |||
Can Excel access substrings and individual characters? | Excel Discussion (Misc queries) |