Impact Effort Matrix

[Brew]

Hello all

I need help with my Impact Effort Matrix. The records results in column C and D starting at row 5 is my Impact and Effort results. see the sample chart below.

I would like to do 2 things

How do I create a formula for my chart starting F5 where it will address all of the following conditions and give me the appropriate results and add a color for condition and its matching results in column F:
If C= 4 or 5 and D=4 or 5, then HighImpact-Hard
If C= 1 or 2 and D=4 or 5, then LowImpact-Hard
If C= 4 or 5 and D=1 or 2, then HighImpact-Easy
If C= 1 or 2 and D=1 or 2, then LowImpact-Easy
If C= 3 and D=1 or 2, then NeutralImpact-Easy
If C= 3 and D=4 or 5, then NeutralImpact-hard
If C= 4 or 5 and D=3, then HighImpact-NeutralEffort
If C= 1 or 2 and D=3, then LowImpact-NeutralEffort

Also, to the right of my table B5 to E94 is there a way to create a 4 quadrant plot, where the top left is the highImpact-hard, the bottom left= LowImpact-hard, the top right=highimpact-Easy, the bottom right= lowimpact-Easy and place the cell number in column B that matches the records condition in column C&D into the appropriate quadrant. row 5 is the first record. row 4 is the heading row.

Ideas Impact Effort Total
1) Buy Local and Direct from Producers 5 3 8
2) Share your Knowledge & Enthusiasm for Local Food 3 3 6
3) Join a Community Supported Agriculture (CSA) Program 1 5 6
4) Network Home Gardeners 5 2 7
5) Add Value to Your Own Agricultural Products 4 3 7
6) Identify Opportunities for Local Distribution 5 4 9
7) Find Ways to Pool Resources & Costs with Others 5 3 8
8) Network Community-Based Farmers 5 3 8
9) Create Incentives for Consumers to Shop Locally 5 2 7

 

[dreid1011]

Well if there is a way for me to select record in column b and it auto-selects the matching plot result, then i can manually move that plot result to where it it is visible within the appropriate quadrant. Can you do setup that condition.

I can give that a shot.

Is the list in column B ever longer or shorter than B96?

 

[Brew]

we may add additional records but it will not exceed B100 and yes can definitely be shorter than B96.

 

[dreid1011]

Ok, so I have it working to highlight the area on the plot when you select an item in column B. Can you tell me the exact cell range the plot should occupy? (Currently I have it in K5:AD44) That way I can set the code specific to your sheet. And what is the sheet name the plot and data is in?

And before I forget, where would you prefer the movable items to be created? Next to column B, in A perhaps? Or Column E?

Also, I have the code set up to work with a couple of ActiveX Control buttons to create the movable items for column B and then to delete them if you have the desire to start over/make a new list or edit the existing list. Would you like me to keep it working this way?

 

[Brew]

Ok, so I have it working to highlight the area on the plot when you select an item in column B. Can you tell me the exact cell range the plot should occupy? (Currently I have it in K5:AD44) That way I can set the code specific to your sheet. (that range is fine) And what is the sheet name the plot and data is in? "Impact Matrix"

And before I forget, where would you prefer the movable items to be created? Next to column B, in A perhaps? (A) Or Column E?

Also, I have the code set up to work with a couple of ActiveX Control buttons to create the movable items for column B and then to delete them if you have the desire to start over/make a new list or edit the existing list. Would you like me to keep it working this way ? (I assume that will be ok)

See response in the quote

 

[dreid1011]

Ok, here is what I have for you. You will need to save the workbook as .xlsm after inserting the code below.

On sheet "Impact Matrix", insert two Active X Command Buttons (from the Developer tab). If you don't have the Developer tab active, look here:https://www.techonthenet.com/excel/questions/developer_tab2013.php

Right click on each button, click Properties.

Name one of the buttons "PlotButton" and change the caption to whatever you would like.

Name the other button "ClearButton" and change the caption to whatever you would like.

Copy and paste each of the three codes below into the Sheet module for sheet "Impact Matrix".

Code for PlotButton:

Private Sub PlotButton_Click()

Dim tB1 As Shape
Dim ws As Worksheet
Dim i As Long, j As Long

'Set the sheet name
Set ws = Worksheets("Impact Matrix")

'Identify last row of data in column B
j = ws.Range("C" & Rows.Count).End(xlUp).Row

'Loop through B5 to last row of data in column B
For i = 5 To j
'Create shapes starting in A5 until the last row of data in column B
Set tB1 = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, Range("A5") + 10, Range("A5").Top + (15 * (i - 5)), 30, 15)

'Add the cell address to the shape, horizontal/vertical align text (centered), change font size to 8
With tB1.TextFrame
    .Characters.Text = ws.Range("B" & i).Address(0, 0)
    .HorizontalAlignment = xlHAlignCenter
    .VerticalAlignment = xlVAlignCenter
    .Characters.Font.Size = 8
End With

'Change shape fill color to gray, change shape border color to black
tB1.Fill.ForeColor.RGB = RGB(204, 204, 204)
tB1.Line.ForeColor.RGB = RGB(0, 0, 0)

Next i

End Sub

Code for ClearButton:

Private Sub ClearButton_Click()

Dim shp As Shape

'Delete all shapes on the active sheet
For Each shp In ActiveSheet.Shapes
    If shp.Type = msoAutoShape Or shp.Type = msoTextBox Then shp.Delete
Next shp

End Sub

Code to highlight sections of the chart:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet

Dim i As Long
Dim xVal As Variant, yVal As Variant

'Set the sheet name
Set ws = Worksheets("Impact Matrix")

'Identify last row of data in column B
i = ws.Range("B" & Rows.Count).End(xlUp).Row

'Define the significant coordinates of the chart (ignore first entries of "0", they are place holders)
xVal = Split("0,37,29,21,13,5", ",")
yVal = Split("0,K,O,S,W,AA", ",")

'Checks to see if you selected any cell within the used range in column B, then identifies the coordinates based on the values in columns C and D
If Not Intersect(Target, ws.Range("B5:B" & i)) Is Nothing Then
    ws.Range("K5:AD44").Interior.ColorIndex = xlNone
    ws.Range(yVal(Target.Offset(, 2).Value) & xVal(Target.Offset(, 1).Value)).Resize(8, 4).Interior.ColorIndex = 6
    ws.Range(yVal(Target.Offset(, 2).Value) & xVal(Target.Offset(, 1).Value)).Resize(8, 4).Interior.Pattern = xlSolid
Else
    ws.Range("K5:AD44").Interior.ColorIndex = xlNone
End If
    
End Sub

 

[Brew]

Thanks, I added the 3 programs into the worksheet VB module and saved as xlsm and enabled the macro, but i dont see how to execute the programs.

 

[dreid1011]

If you inserted the buttons and named them appropriately, when you click on them, the code should run.

You did insert Active X Command Buttons yes? Not form controls?

 

[Brew]

I only added the code. I just created the 2 Active X Command buttons and change the buttons names as instructed and the caption. However, when i click on them the buttons are only highlighted they are not active.

 

[dreid1011]

Ok, so on the ribbon you may still have "design mode" active. Click on it again so it's not selected, then you should be able to use the buttons.

 

[Brew]

Thanks that works, the plot button generates results in column A and the clear button clears the column A results, however it doesnt create plot diagram results starting in column K