Hoi again,
hab Modul 5 mal angepasst (ModifyFilter).
Option Explicit
Private Const C_RGB_STATE1 As Long = &H8A5D38 ' ? "&H" & Hex$(RGB(56, 93, 138))
Private Const C_RGB_STATE2 As Long = &H50B000 ' ? "&H" & Hex$(RGB(0, 176, 80))
Public Sub RoundedRectangle_Click()
'On click filter listed categories in "Risk Category Checklist" by the text in the rounded rectangles
Dim ws As Excel.Worksheet
Dim rngAutoFilter As Excel.Range
Dim shp As Excel.Shape
Dim strShapeText As String
Set ws = Worksheets("Risk Category Checklist")
Set rngAutoFilter = ws.Range("$A$5:$W$500")
Set shp = ActiveSheet.Shapes(Application.Caller)
strShapeText = shp.TextFrame2.TextRange.Text
Call ToggleShapeColor
Application.ScreenUpdating = False
If shp.Fill.ForeColor.RGB = C_RGB_STATE2 Then
'Select relevant column for filtering according to the shape's text
Select Case strShapeText
Case "Internal", "External", "Combination"
Call ModifyFilter(rngAutoFilter, 4, strShapeText)
Case "Financial", "Infrastructure", "Reputational", "Market"
Call ModifyFilter(rngAutoFilter, 6, strShapeText)
Case "Strategic", "Project-related", "Operational"
Call ModifyFilter(rngAutoFilter, 11, strShapeText)
Case Else
MsgBox ("Please pick a specific risk cause driver, a risk event or the effect level!")
End Select
Else 'Unfilter
Select Case strShapeText
Case "Internal", "External", "Combination"
Call ModifyFilter(rngAutoFilter, 4)
Case "Financial", "Infrastructure", "Reputational", "Market"
Call ModifyFilter(rngAutoFilter, 6)
Case "Strategic", "Project-related", "Operational"
Call ModifyFilter(rngAutoFilter, 11)
Case Else
MsgBox ("Please pick a specific risk cause driver, a risk event or the effect level!")
End Select
End If
Application.ScreenUpdating = True
End Sub
Private Sub ToggleShapeColor()
'Change shape color on click in sheet "Checklist Structure"
Dim shp As Shape
Set shp = ActiveSheet.Shapes(Application.Caller)
With shp.Fill
If .ForeColor.RGB = C_RGB_STATE1 Then
.ForeColor.RGB = C_RGB_STATE2
Else
.ForeColor.RGB = C_RGB_STATE1
End If
End With
End Sub
Gruß, Trägheit - so nenn ich mich an "trippwütigen" Tagen. ;)
|