PDA

View Full Version : Solved: Worksheet Change Event



austenr
07-25-2010, 12:22 PM
This should place a value in cell E25 but it returns nothing. If I put the formula directly into E25 it works fine. What gives?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Input + Wksheet").Select
If Range("B10").Value = "1" Then
Sheets("CP1").Select
Range("D12").Value = "X"
Range("E25").Formula = "=IF(TRIM(MEDCVG)=""1"",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End If
Sample WB attached.

GTO
07-25-2010, 01:27 PM
Hi Austen,

If I am reading correctly, you are trying to check B1 of 'Input + Wksheet'. The trouble is that even though you selected the sheet, the unqualified Range does not belong to the active sheet as one might expect. This is because the Range is in an object module, in this case, sheet cp1, and thus, the Range belongs to the sheet implicitly. To refer to the other sheet, you need to qualify it, regardless of what sheet is active.

Aggravatin' ain't it? I went along for the longest w/o that tidbit, until Pascal pointed it out to me one day.

Try this with a break at the top of the sub. YOu can see it happen...


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Input + Wksheet").Select

MsgBox Range("B10").Parent.Name

If Range("B10").Value = "1" Then
Sheets("CP1").Select
Range("D12").Value = "X"
Range("E25").Formula = _
"=IF(MEDCVG)=""1"",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End If
End Sub

I would note that qualifiying with the codename (If Sheet3.Range("B10").Value = 1 Then) it erred at the .Formula line. Not sure what is wrong there.

Mark

mdmackillop
07-25-2010, 01:36 PM
Hi Austen,
Changing selection in the code triggers the code so you get an endless loop. Either set EnableEvents=False or modify your code to avoid selection. I do get an error with your formula though.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Input + Wksheet").Range("B10").Value = "1" Then
With Sheets("CP1")
.Range("D12").Value = "X"
.Range("E25").Formula = "IF(MEDCVG)=""1"",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End With
End If
End Sub

austenr
07-25-2010, 01:47 PM
Malcomb,

Where does Enable events go?

GTO
07-25-2010, 02:11 PM
Hi Malcom :-)

I certainly agree that killing events whilst in an event procedure can save headaches/prevent recurse. I do not see anything in the code that would recurse though.

Hi Austen,

Generally, kill events at the start of the event procedure and turn them back on at the end.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
Sheets("Input + Wksheet").Select

If Sheet3.Range("B10").Value = 1 Then
Sheets("CP1").Select
Range("D12").Value = "X"
'Range("E25").Formula = _
"=IF(MEDCVG)=""1"",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End If
Application.EnableEvents = True
End Sub

When using this technique, I read through my code to make sure I don't have any Exit Sub's or any possible early exits where events do not get turned back on.

Mark

mdmackillop
07-25-2010, 02:13 PM
You use it as follows. You need the error handling as it does not rerset to True automatically


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False

Sheets("Input + Wksheet").Select
If Range("B10").Value = "1" Then
Sheets("CP1").Select
Range("D12").Value = "X"
Range("E25").Formula = "=IF(TRIM(MEDCVG)=""1"",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End If
Exits:
Application.EnableEvents = True
End Sub

austenr
07-25-2010, 02:28 PM
Would that solve this particular problem? Thanks guys.

austenr
07-25-2010, 02:35 PM
hmmm....tried your code Malcomb but the cell E25 doesn't get populated still. Would I be better off to put this in a standard module? Not sur what is wrong with the formula either?

mdmackillop
07-25-2010, 03:08 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Input + Wksheet").Range("B10").Value = "1" Then
With Sheets("CP1")
.Range("D12").Value = "X"
.Range("E25").Formula = "=IF(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End With
End If
End Sub

austenr
07-25-2010, 06:00 PM
Thanks Malcomb. Had to put it in a standard module to get it to work. That's fine though, because the user can click a button to run this.

austenr
07-26-2010, 12:12 PM
Malcomb,

Got this to work just fine.

Sub change()
' EE Only under 65
If Sheets("Input + Wksheet").Range("B10").Value = "1" Then
With Sheets("CP1")
.Range("D12").Value = "X"
.Range("E25").Formula = "=IF(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F25").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)"
.Range("E33").Formula = "=IF(MEDCVG=1,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f33").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)"
.Range("E41").Formula = "=IF(MEDCVG=1,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F41").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)"

End With
End If


End Sub
However, if the value is 2 in B10 of the first line of the IF statement, then I need to do an IF statement just like the one above and the formulas contained in the code above. So I would have a bigger IF statement including the formulas from above but not checking for MEDAVG=1.

I thought the easiest way was to just not check for the MEDAVG =1 but cant quite figure out which back end ) to eliminate. I have 4 conditions to check and if the selection is 3 then include formulas for 1 2 and 3. If it is 4 then everything. Hope I am clear.

gcomyn
07-26-2010, 02:34 PM
having more than 2 or 3 conditions, I would create a function to return the amounts needed for each cell. that way, you can use select case or nested ifs to do your conditions.

GComyn
:sleuth:

austenr
07-26-2010, 02:45 PM
Can you please give an example? Thanks

gcomyn
07-26-2010, 03:33 PM
Ok... here is something quick that I put together... I looked at your input sheet, and tried to see what you were doing there with the medcvg variable.... and here ya go:


Public Sub Checking_MedCoverag()
Dim intLookUp As Integer
Dim strAgeLookup As String

Call Clear_CP1

Select Case Range("age")
Case Is <= 65
strAgeLookup = "_Below_65"
Case Is > 65
strAgeLookup = "_Above_65"
End Select

Select Case Range("medcvg").Value
Case "1"
intLookUp = IIf(Range("age") <= 65, 7, 14)
With Sheets("CP1")
.Range("D12").Value = "X"
.Range("E25").Formula = "=VLOOKUP(YearsOfService,Indemnity," & intLookUp & ",FALSE)/12"
.Range("F25").Formula = "=Indemnity_RO" & strAgeLookup & "/12"
.Range("E33").Formula = "=VLOOKUP(YearsOfService,PPO," & intLookUp & ",FALSE)/12"
.Range("f33").Formula = "=PPO_RO" & strAgeLookup & "/12"
.Range("E41").Formula = "=VLOOKUP(YearsOfService,EPO," & intLookUp & ",FALSE)/12"
.Range("F41").Formula = "=EPO_RO" & strAgeLookup & "/12"
End With
Case "2"
intLookUp = IIf(Range("age") <= 65, 9, 0)
If intLookUp <> 0 Then
With Sheets("CP1")
.Range("I12").Value = "X"
.Range("j25").Formula = "=VLOOKUP(YearsOfService,Indemnity," & intLookUp & ",FALSE)/12"
.Range("k25").Formula = "=Indemnity_RC" & strAgeLookup & "/12"
.Range("j33").Formula = "=VLOOKUP(YearsOfService,PPO," & intLookUp & ",FALSE)/12"
.Range("k33").Formula = "=PPO_RC" & strAgeLookup & "/12"
.Range("j41").Formula = "=VLOOKUP(YearsOfService,EPO," & intLookUp & ",FALSE)/12"
.Range("k41").Formula = "=EPO_RC" & strAgeLookup & "/12"
End With
End If
Case "3"
intLookUp = IIf(Range("age") <= 65, 8, 15)
If intLookUp <> 0 Then
With Sheets("CP1")
.Range("N12").Value = "X"
.Range("O25").Formula = "=VLOOKUP(YearsOfService,Indemnity," & intLookUp & ",FALSE)/12"
.Range("P25").Formula = "=Indemnity_RS" & strAgeLookup & "/12"
.Range("O33").Formula = "=VLOOKUP(YearsOfService,PPO," & intLookUp & ",FALSE)/12"
.Range("P33").Formula = "=PPO_RS" & strAgeLookup & "/12"
.Range("O41").Formula = "=VLOOKUP(YearsOfService,EPO," & intLookUp & ",FALSE)/12"
.Range("P41").Formula = "=EPO_RS" & strAgeLookup & "/12"
End With
End If
Case "4"
intLookUp = IIf(Range("age") <= 65, 10, 0)
If intLookUp <> 0 Then
With Sheets("CP1")
.Range("S12").Value = "X"
.Range("T25").Formula = "=VLOOKUP(YearsOfService,Indemnity," & intLookUp & ",FALSE)/12"
.Range("U25").Formula = "=Indemnity_RF" & strAgeLookup & "/12"
.Range("T33").Formula = "=VLOOKUP(YearsOfService,PPO," & intLookUp & ",FALSE)/12"
.Range("U33").Formula = "=PPO_RF" & strAgeLookup & "/12"
.Range("T41").Formula = "=VLOOKUP(YearsOfService,EPO," & intLookUp & ",FALSE)/12"
.Range("U41").Formula = "=EPO_RF" & strAgeLookup & "/12"
End With
End If
End Select
End Sub
Public Sub Clear_CP1()

With Sheets("CP1")
.Range("D12").Value = vbNullString
.Range("E25").Value = vbNullString
.Range("F25").Value = vbNullString
.Range("E33").Value = vbNullString
.Range("f33").Value = vbNullString
.Range("E41").Value = vbNullString
.Range("F41").Value = vbNullString
.Range("I12").Value = vbNullString
.Range("j25").Value = vbNullString
.Range("k25").Value = vbNullString
.Range("j33").Value = vbNullString
.Range("k33").Value = vbNullString
.Range("j41").Value = vbNullString
.Range("k41").Value = vbNullString
.Range("N12").Value = vbNullString
.Range("O25").Value = vbNullString
.Range("P25").Value = vbNullString
.Range("O33").Value = vbNullString
.Range("P33").Value = vbNullString
.Range("O41").Value = vbNullString
.Range("P41").Value = vbNullString
.Range("S12").Value = vbNullString
.Range("T25").Value = vbNullString
.Range("U25").Value = vbNullString
.Range("T33").Value = vbNullString
.Range("U33").Value = vbNullString
.Range("T41").Value = vbNullString
.Range("U41").Value = vbNullString
End With
End Sub


There are 2 functions there.. one to clear the sheet, the other to put the numbers in the appropriate place... it goes on a seperate module, and you can call it from a button.

I also put some more names in the Indemnity, PPO and EPO sheets.... for the totals I used 'Indemity_RO_Below_65' and Indemnity_RO_Above_65, and you can speculate what the others are from there.

GComyn
:sleuth: