How To Use Vlookup In Merged Cells

Click Here to download sample file for practice

Note: Download the file and open with microsoft excel

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 } } }

Assign Value to Single Alphabet In Excel By Excelmind

Assign Value to Single Alphabet In excel 

    Dim AffectedRange As Range

    Dim Cell As Range

    Dim TrgetRow As Long

    Set AffectedRange = Intersect(Target, Me.Range("B2:B100"))

    

    'VBA Code By Excelmind

        

    If Not AffectedRange Is Nothing Then

        Application.EnableEvents = False

        For Each Cell In AffectedRange

            TrgetRow = Cell.Row

            If Cell.Value = "P" Then

                Me.Cells(TrgetRow, "B").Value = "Present"

            ElseIf Cell.Value = "A" Then

                Me.Cells(TrgetRow, "B").Value = "Absent"

            Else

                Me.Cells(TrgetRow, "B").ClearContents

            End If

        Next Cell

        Application.EnableEvents = True

    End If

End Sub

Auto Uncheck checkBox If Corresponding Cell Task/Text Removed/Cleared

 VBA code for to-do checklist where if any task deleted corresponding checkbox will be uncheck by itself

Copy Below VBA code and paste under sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTasks As Range
Dim rngChanged As Range
Dim Cell As Range
'Set the range for tasks in column C
Set rngTasks = Me.Range("C:C")
Set rngChanged = Intersect(Target, rngTasks)
'Check if any changes intersect with the tasks range
If Not Intersect(Target, rngTasks) Is Nothing Then
Application.EnableEvents = False 'Disable events to prevent re-triggering
For Each Cell In rngChanged
'If the task in column C is deleted, clear the corresponding text in column A
If Cell.Value = "" Then
Cell.Offset(0, -2).ClearContents 'Offset by -2 columns (Column A)
End If
Next Cell
Application.EnableEvents = True 'Re-enable events
End If
End Sub

Excel Mind - VBA Code To Add Multiple Checkbox In Multiple Cells Excel

 VBA Code To Add Multiple Checkbox In Multiple Cells Excel 

Sub CreateCheckboxesWithCellReferences()

    Dim ws As Worksheet

    Dim rng As Range

    Dim cb As CheckBox

    Dim cell As Range

    

    ' VBA 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 rng = Application.InputBox("Select a range", Type:=8)

    

    ' Loop through each cell in the selected range

    For Each cell In rng

        ' Create a checkbox in each cell

        Set cb = ws.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)

        

        ' Set the linked cell for the checkbox

        cb.LinkedCell = cell.Address

        

        ' Adjust the checkbox appearance

        cb.Caption = ""

        cb.Value = xlOff

    Next cell

End Sub

Google Sheets Automation - Auto Email On Basis Of Cell Value

 Google Sheets Automation - Auto Email On Basis Of Cell Value

Copy below Code and paste under apps script (Change the email id to required)

function sendEmail() {

  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet

  var range = sheet.getRange("C:C"); // Replace "C:C" with the range of your column

  var values = range.getValues();

  var lastRow = sheet.getLastRow();

  var emailSent = sheet.getRange("E1:E").getValues(); // Assuming E2 is the starting cell of the emailSent column

  for (var i = 0; i < lastRow; i++) {

    if (values[i][0] === 0 && emailSent[i][0] !== "SENT") { // Change "0" to the value you want to monitor and adjust emailSent accordingly

      var emailAddress = "dummy@excelmind.com"; // Replace with the email address you want to send the notification to

      var subject = "Stock count went to zero";

      var message = "Stock went zero for: " + sheet.getRange("I2").getValue(); // Replace "D1" with the cell you want to include in the email body

      

      MailApp.sendEmail(emailAddress, subject, message);

      sheet.getRange("E" + (i + 1)).setValue("SENT"); // Assuming E2 is the starting cell of the emailSent column

      }

  }

}