How flexible is the "For Each" command?

[EdwardMartinIII]

I have a spreadsheet in Excel 2007.

Each row is a condition, but at the end of each condition is a button that reads "not applicable."

If I press that button, then I want every (specified cell value) to switch to 0, and every (specified control) to change its attributes a certain way.

These things are not, however, necessarily consecutive.

It doesn't matter what order they're executed.

So, a sort of pseudo-code would be:

set GroupOfCells = {d18, e18, g18}
set GroupOfControls = {Button108, Button109, Button110}
set TargetCell as range
set TargetControl as control (?)

{upon "Applicable" button being pressed...}

for each TargetCell in GroupOfCells
  TargetCell.value = 0
next

for each TargetControl in GroupOfControls
  TargetControl.caption = "banana"
  TargetControl.color = red
  TargetControl.enabled = false
next

The reason for doing this is to reduce fiddling with code for each row by only fiddling with the Group declarations.

Thanks!

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

hi,

What kind of control? MS Forms? Control Toobox?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Not knowing what kind of controls you are using, I can give you an answer regarding the cell collection.

I am guessing that you have more than one group of cells, but that each group of cells is 1) in the same row and 2) in columns D, E & G.

Furthermore, I am guessing that the user does something in the row that will trigger your code.

And I am guessing that you already have data in your cells in several rows.


Paste this example in the Code Window for this sheet (right-click sheet tab and select View code

In this example code, select any cell in the row and your values will be zeroed out...

Sub button_click(lRow As Long)
    Dim GroupOfCells As Range
    Dim TargetCell As Range

    Set GroupOfCells = Union([d18], [e18], [g18])
    '
    '{upon "Applicable" cell/row being selected...}
    
    For Each TargetCell In Intersect(Cells(lRow, "A").EntireRow, GroupOfCells.EntireColumn)
      TargetCell.Value = 0
    Next
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    button_click Target.Row
End Sub

Still need to know the kind of controls.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Actually this works, too, no loop...

Sub button_click(lRow As Long)
    Dim GroupOfCells As Range
    
    Set GroupOfCells = Union([d18], [e18], [g18])
    '
    '{upon "Applicable" button being pressed...}
    
    Intersect(Cells(lRow, "A").EntireRow, GroupOfCells.EntireColumn).Value = 0
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

The controls are ActiveX controls.

That second example looks very tight!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

What 'applicable button' causes this code to run?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

I am guessing that you have more than one group of cells, but that each group of cells is 1) in the same row and 2) in columns D, E & G."

Nope. It'll be the same row, but some variable number, from 1 to 4 of the cells in columns C through H.



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

So you will ONLY have one row to which this logic applys: row 18 I assume, and there will NEVER be any other row that will have similar logic.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

Okay, this is what I have so far:

I THINK what has been labeled "pseudocode" has made it more clear what I'm trying to do:

Private Sub CButton0899_Click()
  Dim GroupOfCells As Range
  Dim TargetCell As Range
  Dim NACell As Range
  Dim NAButton As CommandButton
  'Dim GroupOfControls as a collection of controls <-- pseudocode
  'Dim TargetControl as control <-- pseudocode

  ' Set Constants for this control
    Set GroupOfCells = Union([d18], [e18], [g18]) 'The data cells for this Condition
    'Set GroupOfControls = Union([CButton0896][CButton0897][CButton0898]) <-- pseudocode
    Set NACell = [j18] 'The N/A cell for this Condition
    Set NAButton = CButton0899 'The button pressed
    Sheets("Req. A1").Select 'The sheet for this Condition
  
  If NAButton.Caption = "Applicable" Then
    NAButton.Caption = "Not Applicable"
    NAButton.BackColor = vbRed
    NACell.Value = NAButton.TopLeftCell.Value
    ' With each TargetCell in GroupOfCells <-- pseudocode
      ' TargetCell.Value = 0 <-- pseudocode
    ' End With <-- pseudocode
    ' Set the value of each item in GroupOfCells to 0 <-- pseudocode
    ' With each TargetControl in GroupOfControls <-- pseudocode
      ' .Caption = "Not Applicable" <-- pseudocode
      ' .BackColor = vbRed <-- pseudocode
      ' .Enabled = False <-- pseudocode
    ' End With <-- pseudocode
    
  Else
    NAButton.Caption = "Applicable"
    NAButton.BackColor = vbGreen
    NACell.Value = 0
    ' With each TargetControl in GroupOfControls <-- pseudocode
      ' .Caption = "Applicable" <-- pseudocode
      ' .BackColor = vbGreen <-- pseudocode
      ' .Enabled = True <-- pseudocode
    ' End With <-- pseudocode
  End If
    
End Sub

Does that make sense at all?

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

So you will ONLY have one row to which this logic applies: row 18 I assume, and there will NEVER be any other row that will have similar logic."

In the whole collection of sheets, there are anywhere from ten to 100 rows. Each row can be declared "Not Applicable" and all 'points' on that row are reduced to 0, and all buttons on that row are switched to red and deactivated.

For each row, I don't mind hardcoding this section:

  ' Set Constants for this control
    Set GroupOfCells = Union([d18], [e18], [g18]) 'The data cells for this Condition
    'Set GroupOfControls = Union([CButton0896][CButton0897][CButton0898]) <-- pseudocode
    Set NACell = [j18] 'The N/A cell for this Condition
    Set NAButton = CButton0899 'The button pressed
    Sheets("Req. A1").Select 'The sheet for this Condition

which I think I'll just have to.

The rest, because it uses variables and such, should work fine -- once these "constants" are set.

Man, I hope I'm explaining this right...


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[combo]

Do you need buttons? Can you consider replacing them by cells and double-click event? The idea to simplify rules:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox Target.Address
End Sub

You can use [tt]Not Intersect(Target, HotRange) is Nothing[/tt] to identify hot area.

combo

 

[EdwardMartinIII]

Do you need buttons?"

Yeah, they're kinda fond of the buttons.

I'm doing it the dum-dum way -- listing each item individually. Oh well.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door