How to add checkbox accordingly with spill range?

Lacan

Lacan

Active Member
Joined
Oct 5, 2016
Messages
281
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, 🖐👍

Would like to add checkbox accordingly with spill range (both and Column I and Column J)?
Thank you very much for the help!!! 👍👍🍻

2025.xlsx
ABCDEFGHIJKL
1
2CUSTOMERSELECTEDSALES
3ABC1500CUSTOMERSELECTEDSALESSELECTEDCHANGERESULTS OF CHANGE 
4ABC2S1000ABC2S1000 S
5ABC3S300ABC3S300S
6ABC4400ABC5S2000S
7ABC5S2000ABC6S50
8ABC6S50ABC7S10
9ABC7S10ABC8S11
10ABC8S11
11
12
13
Caixa de Verificação
Cell Formulas
RangeFormula
F3:H9F3=VSTACK(TAKE(Tabela3[#Headers],1),FILTER(Tabela3[#All],Tabela3[[#All],[SELECTED]]="S"))
L3L3=IF(AND(I4="VERDADEIRO",J4="VERDADEIRO"),"s","")
I4:I8I4=IF(G4:.G8="S",TRUE)
K4:K6K4=IF(I4>J4,"S",IF(I4=J4,"",IF(I4<J4,"S")))
Dynamic array formulas.
 
Sort by date Sort by votes
PeteWright said:
Hi @Lacan

You can add checkboxes from the "Insert" Ribbon:
View attachment 123815

the cell will be "TRUE" if selected and "FALSE" if not:

View attachment 123816
Click to expand...

Dear @PeteWright
Hope are fine.
Yes know how to insert "Checkbox". Even it doesnt show did that in range I4:I8.
However the spill range goes to I9.
Instead have to scroll down how everytime time it goes behond spill range how can be inserted automatically?
Would like in Column I range and Colum J range accordingly.
Thank you very much.👍🍻🍻

1743267527051.png
 
Upvote 0
Lacan said:
Dear @PeteWright
Hope are fine.
Yes know how to insert "Checkbox". Even it doesnt show did that in range I4:I8.
However the spill range goes to I9.
Instead have to scroll down how everytime time it goes behond spill range how can be inserted automatically?
Would like in Column I range and Colum J range accordingly.
Thank you very much.👍🍻🍻

View attachment 123820
Click to expand...
Do you want the number of checkboxes to reflect the populated rows in columns F:G every time the formula in cell F3 is evaluted?

When refreshed the values in the Checkboxes will have to be FALSE, is this OK?
 
Upvote 0
HighAndWilder said:
Do you want the number of checkboxes to reflect the populated rows in columns F:G every time the formula in cell F3 is evaluted?

When refreshed the values in the Checkboxes will have to be FALSE, is this OK?
Click to expand...
Dear @HighAndWilder

Thanks for the help!
Yes, thats correct!
Can you please give a hand?
Very grateful!!! 👍👍🍻
 
Upvote 0
Lacan said:
Dear @HighAndWilder

Thanks for the help!
Yes, thats correct!
Can you please give a hand?
Very grateful!!! 👍👍🍻
Click to expand...
I suggest splitting the data and report onto different sheets.

I have a sheets named 'Data' although you can call it what you like and a sheet called 'Report' which you can also change.

Put this code in the code module for the sheet which you want the report in.

When this sheet is activated you can choos whether to compile the report.

If you want the report on the same sheet as the data table then let me know and I'll tweak the code.

The check boxes don't show up on the XL2BB mini-sheet unfortunately.

VBA Code: 
Private Sub Worksheet_Activate()
Dim strFormula As String
Dim intRows As Integer
Dim rngTopLeft As Range

  ActiveWorkbook.Save

  If MsgBox("Create Report?", vbYesNo, "") = vbYes Then
  
    'Clear the worksheet.
    Cells.Clear
    
    ' Position the grid.
    Set rngTopLeft = Range("B2")
    
    ' Define and apply the formula.
    strFormula = "VSTACK(TAKE(Tabela3[#Headers],1),FILTER(Tabela3[#All],Tabela3[[#All],[SELECTED]]=""S""))"

    rngTopLeft.Formula2 = "=" & strFormula
    
     ' Ascertain the number of rows.
    intRows = Evaluate("ROWS(" & strFormula & ")")
    
    ' Substitute formula result with values.
    With rngTopLeft.Resize(intRows, 3)
      .Value = .Value
    End With
      
    ' Set up the checkboxes.
    With rngTopLeft.Resize(intRows - 1, 2).Offset(1, 3)
      .CellControl.SetCheckbox
    End With
    
    ' Additional grid titles.
    rngTopLeft.Offset(0, 3).Resize(1, 3).Value = Array("SELECTED", "CHANGE", "RESULTS OF CHANGE")
    
    ' Define and apply formula to analyse checkboxes
    strFormula = "=IF(/>\," & "" & """S""" & "" & ",IF(/=\," & """""" & ",IF(/<\," & "" & """S""" & ")))"
    
    strFormula = Replace(strFormula, "/", rngTopLeft.Offset(1, 3).Address(False, False))
    
    strFormula = Replace(strFormula, "\", rngTopLeft.Offset(1, 4).Address(False, False))
        
    rngTopLeft.Offset(1, 5).Formula2 = strFormula
    
    rngTopLeft.Offset(1, 5).AutoFill rngTopLeft.Offset(1, 5).Resize(intRows - 1, 1)
    
    ' Format the worksheet.
    With rngTopLeft.Resize(intRows, 6)
      
      With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
      End With
      
      .Font.Size = 12
      .Font.Name = "Arial"
      .EntireRow.RowHeight = 24
      
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlCenter
      .IndentLevel = 1
      
      .Columns(4).HorizontalAlignment = xlCenter
      .Columns(5).HorizontalAlignment = xlCenter
      
      With .Rows(1)
        .Interior.Color = RGB(219, 219, 219)
        .Font.Bold = True
      End With
      
      .EntireColumn.AutoFit
      
    End With
      
  End If

End Sub

How to add checkbox accordingly with spill range.xlsm
ABCDEFGH
1
2CUSTOMERSELECTEDSALESSELECTEDCHANGERESULTS OF CHANGE
3ABC2S1000S
4ABC3S300S
5ABC5S2000S
6ABC6S50S
7ABC7S10 
8ABC8S11S
9
Report
Cell Formulas
RangeFormula
G3:G8G3=IF(E3>F3,"S",IF(E3=F3,"",IF(E3<F3,"S")))
 
Upvote 0
HighAndWilder said:
I suggest splitting the data and report onto different sheets.

I have a sheets named 'Data' although you can call it what you like and a sheet called 'Report' which you can also change.

Put this code in the code module for the sheet which you want the report in.

When this sheet is activated you can choos whether to compile the report.

If you want the report on the same sheet as the data table then let me know and I'll tweak the code.

The check boxes don't show up on the XL2BB mini-sheet unfortunately.

VBA Code: 
Private Sub Worksheet_Activate()
Dim strFormula As String
Dim intRows As Integer
Dim rngTopLeft As Range

  ActiveWorkbook.Save

  If MsgBox("Create Report?", vbYesNo, "") = vbYes Then
 
    'Clear the worksheet.
    Cells.Clear
   
    ' Position the grid.
    Set rngTopLeft = Range("B2")
   
    ' Define and apply the formula.
    strFormula = "VSTACK(TAKE(Tabela3[#Headers],1),FILTER(Tabela3[#All],Tabela3[[#All],[SELECTED]]=""S""))"

    rngTopLeft.Formula2 = "=" & strFormula
   
     ' Ascertain the number of rows.
    intRows = Evaluate("ROWS(" & strFormula & ")")
   
    ' Substitute formula result with values.
    With rngTopLeft.Resize(intRows, 3)
      .Value = .Value
    End With
     
    ' Set up the checkboxes.
    With rngTopLeft.Resize(intRows - 1, 2).Offset(1, 3)
      .CellControl.SetCheckbox
    End With
   
    ' Additional grid titles.
    rngTopLeft.Offset(0, 3).Resize(1, 3).Value = Array("SELECTED", "CHANGE", "RESULTS OF CHANGE")
   
    ' Define and apply formula to analyse checkboxes
    strFormula = "=IF(/>\," & "" & """S""" & "" & ",IF(/=\," & """""" & ",IF(/<\," & "" & """S""" & ")))"
   
    strFormula = Replace(strFormula, "/", rngTopLeft.Offset(1, 3).Address(False, False))
   
    strFormula = Replace(strFormula, "\", rngTopLeft.Offset(1, 4).Address(False, False))
       
    rngTopLeft.Offset(1, 5).Formula2 = strFormula
   
    rngTopLeft.Offset(1, 5).AutoFill rngTopLeft.Offset(1, 5).Resize(intRows - 1, 1)
   
    ' Format the worksheet.
    With rngTopLeft.Resize(intRows, 6)
     
      With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
      End With
     
      .Font.Size = 12
      .Font.Name = "Arial"
      .EntireRow.RowHeight = 24
     
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlCenter
      .IndentLevel = 1
     
      .Columns(4).HorizontalAlignment = xlCenter
      .Columns(5).HorizontalAlignment = xlCenter
     
      With .Rows(1)
        .Interior.Color = RGB(219, 219, 219)
        .Font.Bold = True
      End With
     
      .EntireColumn.AutoFit
     
    End With
     
  End If

End Sub

How to add checkbox accordingly with spill range.xlsm
ABCDEFGH
1
2CUSTOMERSELECTEDSALESSELECTEDCHANGERESULTS OF CHANGE
3ABC2S1000S
4ABC3S300S
5ABC5S2000S
6ABC6S50S
7ABC7S10 
8ABC8S11S
9
Report
Cell Formulas
RangeFormula
G3:G8G3=IF(E3>F3,"S",IF(E3=F3,"",IF(E3<F3,"S")))
Click to expand...

@HighAndWilder

Thank you very much for the help!
Even Im OK with VBA think my data will become more complex.
Just to see how it visualy looks like can you please show a sample print screen?
Grateful!!! 🔝👍👍🍻
 
Upvote 0
Lacan said:
@HighAndWilder

Thank you very much for the help!
Even Im OK with VBA think my data will become more complex.
Just to see how it visualy looks like can you please show a sample print screen?
Grateful!!! 🔝👍👍🍻
Click to expand...
See attached but it can look like whatever you would like it to look like.

If your data changes and you need modifications then just let me know.
 

Attachments

  • Screenshot 2025-03-30 150336.png
    Screenshot 2025-03-30 150336.png
    12.5 KB · Views: 2
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Solved
How to Xlookup fuzzy match and standard Xlookup in the same formula?
2
Replies
11
Views
352
Peter_SSs
Peter_SSs
JenniferMurphy
  • Solved
How to calculate rank (subrank?) in NFL conference and division?
Replies
6
Views
271
JenniferMurphy
JenniferMurphy
Lacan
  • Solved
How to sort columns in a particular sequence?
Replies
4
Views
125
Lacan
Lacan
Lacan
  • Question Question
How to filtered/extract names and calculate difference between values?
2
Replies
19
Views
527
Lacan
Lacan
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
258
Fluff
Fluff

Share this page

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
Back
Top