Excel VBA Worksheet Change function - more than 1 function (1 Viewer)

Ginny2222

Ginny
Joined
Oct 27, 2007
Messages
108
Hi all,
Thanks to Craig Ottley and Batman in the Ozgrid Fo
rum I have been able to set up functions in Worksheet_Change(). My problem is that each function will work correctly on their own, but when I try to put 2 functions together, I can't get them to work independently. Is this possible?

' First function
Dim wSht As Worksheet

Set wSht = Worksheets("Rosters")

MyFix

On Error GoTo errHandler
If WorksheetFunction.Count(wSht.Range("Multitask_atts_DWSs"), Target) > 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = wSht.Range("Y266") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, wSht.Range("DWS_List"), 0), 0)

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

' Second function for warning cells
If Cells(168, 23).Value = "Quota >Req'd" Then
Warning.Show
ActiveCell.Offset(0, 0).Range("A1").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Else
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End If

Each seems to be aimed at individual cell/sets of cells changes, but they are getting mixed up.

Any pointers would be greatly appreciated.

Thanks
Ginny
http://www.ozgrid.com/forum/member.php?u=35962&
 

Ginny2222

Ginny
Joined
Oct 27, 2007
Messages
108
Thanks Sunshine076. I'll have a read up.

I changed my second function to read:
n=Target.Column
If Cells(168, n).Value = "Quota >Req'd" Then ...

This has helped. It's not fully right yet, though. It's not producing the correct results all the time.

rgs
Ginny
 

Brianwarnock

Retired
Joined
Jun 2, 2003
Messages
12,701
will this

Code:
If WorksheetFunction.Count(wSht.Range("Multitask_atts _DWSs"), Target) > 1 Then GoTo exitHandler
not cause a problem?

should you not test which function you want as soon as entering the code?

Brian
 

Ginny2222

Ginny
Joined
Oct 27, 2007
Messages
108
Hi Brian,

I'm new to Worksheet_Change, I'm afraid. My understanding is that by putting the code there, it would be triggered each time a change is made in the worksheet. If I put the code into functions there, would they then need to be triggered?

Yes, that line of code is probably wrong, but with that function in the file on it's own, it was doing what I needed it to do. Also with the other function on it's own, it was also working. I am trying to isolate the cell references that will trigger each appropriately.

Am I going about this wrong?

Help appreciated.

rgs
Ginny
 

Brianwarnock

Retired
Joined
Jun 2, 2003
Messages
12,701
What I'm suggesting is that when you first enter the sub you test for which code you want to execute, maybe something like

If Target.column/row/address criteria then
Goto second_function
End If

first function code

Exit Sub
second_function:
second function dode

End Sub

Your test may be on Target.value or something else entirely

Brian
 

Ginny2222

Ginny
Joined
Oct 27, 2007
Messages
108
Oh, I see what you mean. I didn't think this would be necessary, when I have specified the cell addresses that each function refer to. I'll try this.

Thanks for the help.

rgs
Ginny
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom