Thread
:
Worksheet_Change problem
View Single Post
#
5
Posted to microsoft.public.excel.programming
RGA
external usenet poster
Posts: 4
Worksheet_Change problem
I have a Data Validation dropdown in the cell. The Worksheet_Change
event fires when the value is changed via the dropdown. But the code
below does not work. If I type in a valid value the code works!!! Is
there another event to run the dropdown change?
TIA
R.
(RGA) wrote in message . com...
Hi again. Update: It works on other sheets in the workbook but not the
one I need it in. Other subs that are called from the Worksheet_Change
event are working and some of them are selecting other cells. One of
then if calling an Oracle database, bringing data into a range and
naming it then creating Data Validation dropdowns.
Very confused.
"Kevin Beckham" wrote in message ...
You need to avoid changing your selection whilst
processing a cell Change
Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant
varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value
Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone
If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If
With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing
End sub
-----Original Message-----
Hello,
I'm having a problem with the code below. When I make a
change to the
worksheet it is calling 'RetailZonesFormat' and returning
values from
the variables but it won't select the cells or colour the
interior.
Thanks for any help you can provide.
-Ron
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim varRtl As Variant
If Target.Column = 16 And Target.Row 15 Then
On Error GoTo NoRtl
varRtl = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID").Find(Target, , xlValues,
xlWhole).Offset(0, -1).Value
Call RetailZonesFormat
Range("P14").Select
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Target.AddComment.Text varRtl
Call EnEv
Application.CalculateFull
Exit Sub
NoRtl:
Call DisEv
Target = ""
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Call EnEv
Application.CalculateFull
End
End If
End Sub
Sub RetailZonesFormat()
Dim varRtlZne As Variant
Dim varRtlVal As Variant
Dim dblRow As Double
dblRow = ActiveCell.Row
varRtlZne = Cells(dblRow, 16)
varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,BS15").
_
Interior.ColorIndex = xlNone
Select Case varRtlVal
Case 1
Range("Q15").Select
Case 2
Range("Q15,R15").Select
Case 3
Range("Q15,R15,S15").Select
Case 4
Range("Q15,R15,S15,T15").Select
Case 5
Range("Q15,R15,S15,T15,U15").Select
Case 6
Range("Q15,R15,S15,T15,U15,BJ15").Select
Case 7
Range("Q15,R15,S15,T15,U15,BJ15,BK15").Select
Case 8
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15").Select
Case 9
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Cells(dblRow, 16).Select
End Sub
.
Reply With Quote
RGA
View Public Profile
Find all posts by RGA