Private Static Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cellrow As Integer
Dim cellcol As Integer
Dim KeyCells As Range
Dim duescells As Range
Dim adjustcell As Range
Dim captionname As String
Dim rangename As String
Dim formatname As String
Application.ScreenUpdating = False
'On Error Resume Next
' KeyCells is set for include or exclude cells
Set KeyCells = Range("adjustcell, incl_incr1, incl_incr2, incl_incr3, special_assess_incl_or_excl")
' Check if an Include or Exclude cell value needs changing
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case "Include"
ActiveCell = "Exclude"
Range("hoaname").Select
Case "Exclude"
ActiveCell = "Include"
Range("hoaname").Select
Case "Manual Adjust"
ActiveCell = "Auto Adjust"
Range("hoaname").Select
Case "Auto Adjust"
ActiveCell = "Manual Adjust"
Range("hoaname").Select
End Select
Else
End If
' Check if an Include or Exclude for other annual incomes cells value needs changing
Set KeyCells = Range("one_time_income_incl_or_excl")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case "Include"
ActiveCell = "Exclude"
ActiveCell.Offset(0, -6).Select
Case "Exclude"
If ActiveCell.Offset(0, -6).Value = "" Or ActiveCell.Offset(0, -5).Value = "" Or ActiveCell.Offset(0, -4).Value = "" Or ActiveCell.Offset(0, -3).Value = "" Or ActiveCell.Offset(0, -2).Value = "" Then
MsgBox "You cannont include an expense unless Start Year, End Year, Amount, % Annual Increase, and Description data are entered. One or more of these data are missing.", vbOKOnly + vbCritical, "Missing Data"
ActiveCell.Offset(0, -6).Select
Else
ActiveCell = "Include"
ActiveCell.Offset(0, -6).Select
End If
End Select
Else
End If
'Set keycells testing for the contingency fund include/exclude
Set KeyCells = Range("contin_incl_or_exclude")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case "Include"
ActiveCell = "Exclude"
ActiveCell.Offset(0, -1).Select
Case "Exclude"
If ActiveCell.Offset(0, -1).Value = "" Or ActiveCell.Offset(1, -1).Value = "" Or ActiveCell.Offset(2, -1).Value = "" Or ActiveCell.Offset(3, -1).Value = "" Or ActiveCell.Offset(4, -1).Value = "" Or ActiveCell.Offset(5, -1).Value = "" Or ActiveCell.Offset(6 - 2).Value = "" Then
MsgBox "The Contingency Fund can only be included if all the field values have an entry. You cannot leave a field blank. Click the info icon for more information.", vbOKOnly + vbCritical, "Missing Data"
ActiveCell.Offset(0, -1).Select
Else
ActiveCell = "Include"
ActiveCell.Offset(0, -1).Select
End If
End Select
Else
End If
' Check if autoadjust is turned on
If Sheet19.Range("autoadjust").Value = True Then
Range("hoa_dues_start2").Value = Range("incr2_earlystart").Value
Range("hoa_dues_start3").Value = Range("incr3_earlystart").Value
Else
End If
' Lastly, update the control panel fields ...
Application.Run ("update_controlpanel")
Sheet19.Activate ' put in to prevent activation of Sheet16 when updating
On Error Resume Next ' resume next if user had only unprotected a single sheet. Not very likely, but possible
If Sheet2.Range("maxpctfunding") < 1 Then
Sheet19.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScale = 1
Sheet14.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScale = 1
Sheet1.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScale = 1
Else
Sheet19.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScaleIsAuto = True ' set maxscale back to auto if 1 or more
Sheet14.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScaleIsAuto = True ' set maxscale back to auto if 1 or more
Sheet1.ChartObjects("pctfund_chart").Chart.Axes(xlValue).MaximumScaleIsAuto = True ' set maxscale back to auto if 1 or more
End If
Application.ScreenUpdating = True
Calculate
End Sub