Tutorial - How To Link Multiple Checkboxes To Multiple Cells In Excel

  Tutorial - How To Link Multiple Checkboxes To Multiple Cells In Excel

Video Tutorial To Add Multiple Checkbox:

 If you want to create multiple checkboxes that linked to multiple cells, you can apply the following VBA code, and to apply code follow the below step.

1. Go to your worksheet and create checkboxes.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window or you can click on Developer tab ➡ Visual Basic.

3. Click Insert > Module

4. Double click on module1 and paste the below code in the Module Window (make sure module window is clear)

Sub CreateCheckboxes()
    Dim ws As Worksheet
    Dim rnge As Range
    Dim cbx As CheckBox
    Dim cell As Range
    Dim checkBoxColumn As Integer
    Dim resultColumn As Integer
    
    On Error Resume Next ' Enable error handling
    ' Code by Excelmind
    
    ' Set the worksheet where you want to add checkboxes
        Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Select the range where you want to add checkboxes
    Set rnge = Application.InputBox("Select a range", Type:=8)
    
    ' Check if the user cancelled the input box
    If rnge Is Nothing Then
        Exit Sub ' Exit the subroutine if cancelled
    End If
    
    On Error GoTo 0 ' Disable error handling
    
    ' Loop through each cell in the selected range
    For Each cell In rnge
        ' Check if the column index is odd
        If cell.Column Mod 2 = 1 Then
            ' Create a checkbox in the odd column
            Set cbx = ws.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)
            
            ' Set the linked cell for the checkbox to the cell in the next even column
            cbx.LinkedCell = ws.Cells(cell.Row, cell.Column + 1).Address
            
            ' Adjust the checkbox appearance
            cbx.Caption = ""
            cbx.Value = xlOff
        End If
    Next cell
End Sub

4. Now click F5 to run the code or create a button and assign the macro to it.

 

Google Sheets Automation - Automated Email Based On Cell Value

Google Sheets Automation

Automated Email Based On Cell Value

Copy below Code and paste under apps script (Make necessary changes as required)

function sendEmail() { var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with your sheet name var dataRange = sheet.getDataRange(); var values = dataRange.getValues(); var lastRow = dataRange.getLastRow(); for (var i = 1; i < lastRow; i++) { // Start from 1 to skip header row var stockCount = values[i][2]; // Column C (index 2) for stock count var emailAddress = values[i][1]; // Column B (index 1) for email address var emailSent = values[i][4]; // Column E (index 4) for email status if (stockCount > 0 && emailSent !== "SENT") { // Code by Excelmind var subject = "Stock count is greater than zero"; // Change the subject as per your choice var message = "Stock count for your item is: " + stockCount; //Change the message as per your choice MailApp.sendEmail(emailAddress, subject, message); // Mark the email as sent in column E sheet.getRange(i + 1, 5).setValue("SENT"); // i + 1 because getRange and setValue use 1-based indexing } } }