Event Macro that X's cells that go together based upon the same value in a helper column

mohrx123

New Member
Joined
Jan 6, 2012
Messages
9
Hello,

I have a list with multiple rows of data. When I place an "x" in the cell in Column A, that indicates I selected that row and a sumproduct ensues. Most of the rows are independent of each other. However, some rows are add alternates while others are mutually exclusive.

Add alternate means if I select the "add 1" row then I have to select the the "base" row (however, the reverse of selecting the "base" row does not require selection of "add 1"). In some cases there would be "add 1" and "add 2". Selection of "add 2" means "add 1" and "base" need to be selected.

Mutually exclusive means if I select "row 1" then I cannot select "row 5" or "row 13".

An event macro that knows where there is an add alternate (with precedence) or a mutually exclusive case based upon values in a hidden helper column, say Column B, would be fantastic. It ensures no base rows are missed and also ensures there are no duplications for the sumproducts that follow.

Anyone that can post VBA on the event macro I've conceptualized, thank you in advance :)

Jared
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,350
Is the relationship between rows (eg. row 1 is the base row for row2, row 3 and row 4 are exclusive) fixed forever and ever. Or are you going to want the user to be able to set up those relationships.

Also, is "exclusivity" a symmetric relationship.
i.e. is it possible that
if the user selects row 3 they are barred from then selecting row4
but if the user selects row4 first they can then also select row3

Finally, is "Base row" transitive.
Is it possible that
Selecting row2 automaticaly selects Row1 (Row1 is the base row for row2)
Selecting row3 automaticaly selects Row2 but not Row1 (Row2 is a base row for Row3, but Row1 is not.)

One more scenario.
If Row1 and Row2 are exclusive
And Row1 is the base row for Row4

If Row2 is selected
Is the user barred from selecting Row1 or does selecting Row1 de-select Row2

also
is the user barred from selecting Row4 (thus causing Row2 to be selected)
Or does selecting Row4 automaticaly select Row1 and Row2 is de-selected.

How much like option buttons do exclusive rows behave?
 
Last edited:
Upvote 0

mohrx123

New Member
Joined
Jan 6, 2012
Messages
9
1) Would like the user to be able to set up the relationships using the helper column. In most cases, the add alternates will be consecutive after the base row, and the mutually exclusive items will be distanced from each other.

2) Exclusivity shall be if the user selects row 3 then row 4 should be unselected. The user still has the option to select row 4; however, if they do, then row 3 automatically unselects. The user should have the ability to select either of the unchecked boxes, but one box being unchecked does not mean the other box needs to be checked.

3) Base row not transitive (by that definition). If the user sets row 2 as the first add to the base, which would be row 1, then if the user selects just row 1, nothing else happens. If they selected row 2, then row 1 should be selected (if it wasn't). If the user sets row 3 as the second add to the base, then if they select row 3, row 2 and row 1 should both be selected; however, if only row 2 were selected, then only row 1 needs to be selected.

Thanks for the fast reply!
Jared
 
Upvote 0

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,350
What you describe is that BaseRow is transitive.

Final question, might a row have two base rows, e.g. selecting Row3 automatialy selects both Row1 and Row2.
 
Upvote 0

mohrx123

New Member
Joined
Jan 6, 2012
Messages
9
ADVERTISEMENT
Excellent question, I have not encountered selecting one row having two bases; however, if you can permit that with creative code, please do so
 
Upvote 0

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,350
That is more difficult. At the moment, I'm thinking that Column B would hold the "base" information. A cell would be either blank or hold the row number of the base row for that row.
Column C would hold the "exclusive" information, being either blank or holding a comma delimited string of rows exclusive to that row.

I need to do some yard work, but will workout an event macro later.
 
Upvote 0

mohrx123

New Member
Joined
Jan 6, 2012
Messages
9
One slight modification request to enhance visuals...would it be possible to add a color format (e.g. red color for mutually exclusive "x" and blue color for any add alternate "x")? The only trick is can this be done with an event macro as opposed to a conditional format and not impede performance when clicking through cells? The reason I cannot use conditional formats is because I've modified an event macro by others to add a yellow colored crosshair that uses and clears conditional formats. That code is below in case it is helpful to others or can be improved.

Private Sub Worksheet_Activate() 'toggles crosshairs and removes conditional formats in this sheet only!
Application.OnKey "^h", "toggle" 'this has to be in a module
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Deactivate()
Application.OnKey "^h"
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' Use this sheet macro in conjunction with the Module crosshair toggle macro.
If tog = 1 Then
If Target.Cells.Count > 1 Then Exit Sub
Dim icolor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!
'// On error resume in case
'// user selects a range of cells
On Error Resume Next
icolor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors
If icolor < 0 Then
icolor = 19 '15 = grey 4 = green 3=red 35 = light green 24 = lightpurple 19 = yellow

Else
'iColor = iColor + 1 ' makes it a different color than the existing
icolor = 19 ' keeps color as original hightlight
End If
'// Need this test incase Font color is the same
If icolor = Target.Font.ColorIndex Then icolor = icolor + 1
Cells.FormatConditions.Delete
'// Horizontal color banding
With Rows(Target.Row) 'Range("A" & Target.Row, Target.Address)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = icolor
End With
'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
'Column(Target.Column) 'Range("A" & Target.Row, Target.Address)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = icolor
End With
'// Vertical color banding
With Range(Target.Offset(1, 0).Address & ":" & Target.Offset(200, 0).Address)
'Column(Target.Column) 'Range("A" & Target.Row, Target.Address)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = icolor
End With

'With Rows(Target.Column) 'Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
' .FormatConditions.Add Type:=2, Formula1:="TRUE"
' .FormatConditions(1).Interior.ColorIndex = iColor
'End With
Else
Application.StatusBar = "Crosshair turned off, ctrl+h to toggle" 'by default this is turned off
Cells.FormatConditions.Delete
End If
Application.OnKey "^h", "toggle"
Application.EnableEvents = True
'coverage a thru r in column F...colors text the same if any cell starts with a letter a thru q
'only works when entering down
Dim icolor2 As Integer
Dim icolor3 As Integer
bg1 = 1 'black
Application.ScreenUpdating = False
End Sub




And a module code is as follows:

Public tog As Long ' this keeps the variable after End Sub and recognizable by all modules in this workbook
Sub toggle()
'used with the crosshair formatting
'crosshairs only work when the Sheet has the private sub code that runs the macro upon any change in selection in that sheet
If tog = 1 Then 'if crosshairs are on, turn them off
Application.StatusBar = "Crosshair turned off"
Cells.FormatConditions.Delete
'Application.EnableEvents = False
tog = 0
'MsgBox "toggle off"
Else
Application.StatusBar = "Crosshair turned on"
Application.EnableEvents = True
tog = 1
'MsgBox "toggle on"
End If
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,195,831
Messages
6,011,843
Members
441,650
Latest member
ceyoung75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top