If one column is filled next column is mandatory to fill

Help Activities
1

Need a vb script
Need to set a condition If one column is filled another column is mandatory to fill in excel

2

Hi @Demo_User

  1. Use “Read Range Wrokbook” activity to read the excel and store it an DataTable variable.
  2. Add a For Each Row activity to loop through each row in the DataTable.
  3. Inside the For Each Row, add a VBScript condition to check if one column is filled and if another column is mandatory to fill. In this example, let’s assume you want to check if “Column A” is filled, then “Column B” is mandatory to fill.
  4. In the Invoke Code activity inside For Each Row in Data Table activity and add the following VBScript code:
If Not IsDBNull(row("Column A")) Then
    If IsDBNull(row("Column B")) Then
        Throw New Exception("Column B is mandatory to fill when Column A is filled.")
    End If
End If

Replace Column A and Column B with your header names.

Hope it helps!!
Regards,

3

@Demo_User

You can check as below

  1. Read the data into datatable dt
  2. Use filter datatable and set filters as column1 iisnotempty and column2 isempty…
  3. Use if condition if filtereddt.count>0 then atleast one row has a validation error if not all are filled

Cheers

4

I should directly work on excel file not in datatable

5

Hi @Demo_User

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim requiredColumn As Range
    Dim dependentColumn As Range
    Dim checkRange As Range
    
    ' Set the range of the column that must be filled first
    Set requiredColumn = Range("A:A") ' Change "A:A" to the column you want to check
    
    ' Set the range of the dependent column that becomes mandatory to fill
    Set dependentColumn = Range("B:B") ' Change "B:B" to the column you want to make mandatory
    
    ' Set the range to check if there are any changes in the required column
    Set checkRange = Intersect(requiredColumn, Target)
    
    ' Check if any change occurred in the required column
    If Not checkRange Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent infinite loop
        
        Dim cell As Range
        For Each cell In checkRange
            ' Check if the corresponding cell in the dependent column is empty
            If cell.Offset(0, 1).Value = "" Then
                ' If it's empty, prompt the user to fill it
                MsgBox "Column B must be filled if Column A is filled.", vbExclamation, "Mandatory Field"
                ' You can add more specific instructions in the message box if needed
                
                ' Optionally, you can set the focus to the dependent cell to encourage the user to fill it
                cell.Offset(0, 1).Select
                
                ' Exit the loop after the first instance to avoid multiple messages if multiple cells are changed at once
                Exit For
            End If
        Next cell
        
        Application.EnableEvents = True ' Enable events again
    End If
End Sub
  1. Press “Alt + F11” to open the Visual Basic for Applications (VBA) editor.
  2. In the VBA editor, find the worksheet where you want to apply this condition in the “Project Explorer” (usually in the left pane).
  3. Double-click on the sheet’s name to open its code window.
  4. Copy and paste the above code into the code window.
  5. Close the VBA editor and save the workbook as a macro-enabled workbook (with a .xlsm extension) to enable the code.

Hope it helps!!

6

Can I use this directly invoke code without doing any setup in excel