new to vba macros , im trying to create a dropmenu that does a few different things, depending on the choice it'll convert the color of the severity then itll tally all the dropemnus and itll update a table thats attached to a graph. so far I have the colors working with the code below
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
With ContentControl.Range
If ContentControl.Title = "Severity" Then
Select Case .Text
Case "Critical"
.Cells(1).Shading.BackgroundPatternColor = wdColorDarkRed
.Font.Color = wdColorWhite
.Font.Bold = True
Case "High"
.Cells(1).Shading.BackgroundPatternColor = wdColorRed
.Font.Color = wdColorBlack
.Font.Bold = True
Case "Medium"
.Cells(1).Shading.BackgroundPatternColor = wdColorOrange
.Font.Color = wdColorBlack
.Font.Bold = True
Case "Low"
.Cells(1).Shading.BackgroundPatternColor = wdColorYellow
.Font.Color = wdColorBlack
.Font.Bold = True
Case "Informational"
.Cells(1).Shading.BackgroundPatternColor = wdColorLightBlue
.Font.Color = wdColorBlack
.Font.Bold = True
Case Else
.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End Select
End If
End With
End Sub
The chart and table look like this
if i edit the graph data it'll look like this
i think the code to update the table should look like something like this
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim Total As Long, i As Long
Total = 0
With Selection.Rows(1)
For i = 1 To 6
Select Case .Cells(i).Range.ContentControls(1).Range.Text
Case "Critical"
Critical = Critical + 1
Total = Total + 1
Case "High"
High = High + 1
Total = Total + 1
Case "Medium"
Medium = Medium + 1
Total = Total + 1
Case "Low"
Low = Low + 1
Total = Total + 1
Case "Informational"
Informational = Informational + 1
Total = Total + 1
End Select
Next i
.Cells(8).Range.Text = Total
End With
End Sub
the issue I have is, how do i reference that table in the code? second, how do i add both the color changing code and the table update code together so when someone picks an item it updates the table and changes the color, and third, if someone picks and item from the drop down, then decides they want a different choice, how can it update the table to remove the first choice and add the second choice. or should I make this a separate macro that just strictly adds the menu choice at the end , then updates the table and graph