loop to check status of checkboxes and return result(s)?

[Sanjuro]

I have a sheet that has groups of rows of data. Each of these groups has a column of check boxes. I use the check boxes to highlight the selected row and change some various values when selected etc.

At the top of the sheet, I'd like to have the title of the selected row from each group appear, which shouldnt be a problem, as normally only one row from each group is selected. Thing is, in a lot of cases, there are too many rows to solve it with IF/ELSE.

I probably need VBA and some sort of loop. I suppose there are at least two alternatives - a macro is attached to each checkbox and runs and checks what's selected and isnt every time a check box is hit - a macro attached to a button next to the cells at the top of the sheet that says what's selected, but the loop only runs and updates these cells when the button is hit.

For the life of me I cannot figure out the code though. Any thoughts? Probably really easy for most of you. I'm learning, but improving thanks to these forums.

 

[jvanbonn]

Here is something I use to toggle Show/Hide rows based upon check boxes in the first column (A), which when checked sets Column A in that row to true.

Cell A1 is my SHOW/HIDE toggle. All other cells in column A identify which rows I want to SHOW or HIDE. I also use conditional formatting for each row; if the cell in the first column of a row is TRUE (checked), I change all text to italics and strikethrough for that row. Then depending on the state of cell A1, these row are either hidden or shown, but with Italics and Strikethrough text.

The code is placed in the sheet. Right click the sheet tab and select "view code"

There is probably a much better way to do this, but it is food for thought at least.

[COLOR=Blue]Private Sub[/COLOR] Worksheet_Change([COLOR=Blue]ByVal[/COLOR] Target [COLOR=Blue]As[/COLOR] Range)

[COLOR=Blue]If [/COLOR]Target.Address <> "$A$1" [COLOR=Blue]Then[/COLOR]
[COLOR=Blue]  Exit Sub[/COLOR]
[COLOR=Blue]End If[/COLOR]
[COLOR=Blue]Stop[/COLOR]
Application.Enableevents = [COLOR=Blue]False[/COLOR]

[COLOR=Blue]Dim [/COLOR]w [COLOR=Blue]As [/COLOR]Worksheet, t [COLOR=Blue]As [/COLOR]Worksheet
[COLOR=Blue]Dim [/COLOR]h [COLOR=Blue]As [/COLOR]Long, i [COLOR=Blue]As [/COLOR]Long, j [COLOR=Blue]As [/COLOR]Long, k [COLOR=Blue]As [/COLOR]Long
[COLOR=Blue]Dim [/COLOR]Rng [COLOR=Blue]As [/COLOR]Range
[COLOR=Blue]Dim [/COLOR]cell [COLOR=Blue]As [/COLOR]Variant
[COLOR=Blue]Dim [/COLOR]rowlist(1) [COLOR=Blue]As [/COLOR]String

[COLOR=SeaGreen]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'  h is the row index of the selected range (looping)
'  i is the row index of first non-header row of selected range
'  j is the row index of the last row of the selected range
'  k is the kth member of the Range Rng
'
'  Rng is the union of rows in the selected range where
'       column A has the value "TRUE" <= to be hidden
'
'[/COLOR]


[COLOR=Blue]If [/COLOR]Target.Address = "$A$1" [COLOR=Blue]And [/COLOR]Target.Value = [COLOR=Blue]True Then[/COLOR]
  [COLOR=Blue]With [/COLOR]ActiveSheet.Cells(4, 2).CurrentRegion
  i = .Item(2, 1).Row       [COLOR=SeaGreen]'FIRST ROW OF DATA[/COLOR]
  j = .Item(.Rows.Count, .Columns.Count).Row    [COLOR=SeaGreen]'SECOND ROW OF DATA[/COLOR]
[COLOR=Blue]  End With[/COLOR]

  k = 0
    
    [COLOR=Blue]For [/COLOR]h = i [COLOR=Blue]To [/COLOR]j [COLOR=Blue]Step [/COLOR]1
      [COLOR=Blue]If [/COLOR]ActiveSheet.Cells(h, 1) = [COLOR=Blue]True Then[/COLOR]
        k = k + 1
          [COLOR=Blue]If [/COLOR]k = 1 [COLOR=Blue]Then[/COLOR]
            [COLOR=Blue]Set [/COLOR]Rng = ActiveSheet.Cells(h, 1)
            [COLOR=Blue]Debug.Print[/COLOR] Rng.Address
          [COLOR=Blue]Else[/COLOR]
            [COLOR=Blue]Set [/COLOR]Rng = Union(Rng, ActiveSheet.Cells(h, 1))
            [COLOR=Blue]Debug.Print[/COLOR] Rng.Address
[COLOR=Blue]          End If[/COLOR]
[COLOR=Blue]      End If[/COLOR]
    [COLOR=Blue]Next[/COLOR] h
    
  Rng.EntireRow.Hidden = [COLOR=Blue]True[/COLOR]

[COLOR=Blue]ElseIf [/COLOR]Target.Address = "$A$1" [COLOR=Blue]And [/COLOR]Target.Value = [COLOR=Blue]False Then[/COLOR]
 
  ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = [COLOR=Blue]False[/COLOR]

[COLOR=Blue]End If[/COLOR]
Application.Enableevents = [COLOR=Blue]True[/COLOR]
[COLOR=Blue]End Sub[/COLOR]

 

[Sanjuro]

Thanks! Not sure its what I am looking for, but I'll have a look at this and see if I can put it to use. To better explain what I'm needing, I thought I'd attach a simple sample workbook.

On Sheet 1, when a check box in each Group is selected (typically onle one at a time), looking to get the title field in the appropriate cell at the top, ie into B2 for Group 1. There's too many rows in each Group to do so with an IF/ELSE statement in B2-B4.

In other cases, I am looking to do something as in Sheet 2. Here, when items are selected in the Group, looking to get their titles listed up in B2-B6. Here though it needs to figure out if a cell is occupied and, if it is, put it in the one below. Not sure what happens if a check box is later deselected and the entry in B2 is removed, if the macro would then reorganize cell contents. That was where I was thinking it might be necessary to have a button, say in C2, that when hit runs the macro, checks the selected rows, and populates the cells. No idea how to run a loop to do this though. In fact, I think both solutions require a loop (?).

Not sure if either is possible, complicatde, or easy, but if anyone could get me going it would be great. I am not having any luck myself. Any help would be much appreciated.

Edit: I dont appear to be able to do attachments.

The sample Sheets are simple. There are checkboxes in A9 down through A40 or so, with titles next to these in column B. On Sheet 1, they are arranged in three groups of 10. On Sheet 2, its just one long list. Up on B2-B6 there's simply blank cells into which the titles go when check boxes are selected (three for Sheet 1 as only one title goes there per group; five for Sheet 2 as any number can go up there and I could adjust the amount).

 

[Sanjuro]

I think I figured out something that works for Sheet2. Some Google-fu and lots of trial and error:

Dim x As Integer
Dim y As Integer

y = 232

For x = 136 To 139
    If Cells(x, 2) = True And Cells(y, 2) = vbNullString Then
        ActiveSheet.Range(Cells(y, 2), Cells(y, 37)).Value = ActiveSheet.Range(Cells(x, 3), Cells(x, 38)).Value
        y = y + 1
    ElseIf Cells(x, 2) = True Then
        y = y + 1
        x = x - 1
    End If
Next x

The cell addresses are different than in my sample description, but that's easy to change if anyone comes across this in the future and finds a need for a similar thing (note here that y is the "copy into" row). It applies the above macro to a button, which then runs through the (x, 2) locations and tests for TRUE and then checks the copy location and checks to see if its empty. If it is, it sets the values equal. If it isnt, it reruns the loop at the same x but adds 1 to the y (so it moves down a row).

I believe a loop similar to this will also solve Sheet1, though I havent yet tried. I think that's simpler, so started here.

Thanks again for the reply! I'll try to wrap my head around what you're doing and see what I can learn from it.