Solved: Worksheet Change Event

[austenr]

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?

[vba]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),F ALSE),0)/12"
End If[/vba]
Sample WB attached.

[GTO]

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]

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.

[VBA]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[/VBA]

[austenr]

Malcomb,

Where does Enable events go?

[GTO]

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]

You use it as follows. You need the error handling as it does not rerset to True automatically

[VBA]
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),F ALSE),0)/12"
End If
Exits:
Application.EnableEvents = True
End Sub

[/VBA]

[austenr]

Would that solve this particular problem? Thanks guys.

[austenr]

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]

[VBA]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[/VBA]

[austenr]

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]

Malcomb,

Got this to work just fine.

[vba]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[/vba]
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]

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

[austenr]

Can you please give an example? Thanks

[gcomyn]

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:

[vba]
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
[/vba]

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