D
Darren Hill
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
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
