How do I make a column in excel mandatory based on if the another column is filled?

This vba code
I need in vb script format

Private Sub STOPWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim wsToTest As Worksheet 
    Dim rngToTest As Range 
    Dim rCel As Range 
     
    Set wsToTest = Worksheets("Sheet1")     'Edit "Sheet1" to your worksheet name with required data 
     
    With wsToTest 
        Set rngToTest = .Range(.Cells(2, "C"), .Cells(.Rows.Count, "C").End(xlUp))     'Edit "C" to column with "Zone" (2 instances) 
        For Each rCel In rngToTest 
            If rCel <> 0 Then 
                If .Cells(rCel.Row, "D") = "" Then      'Edit "D" to your column for "Returned to service" 
                    MsgBox "Cannot save while cell " & .Cells(rCel.Row, "D").Address(0, 0) & " is not populated." & vbCrLf & _ 
                            "Please enter data and save again." 
                    Cancel = True 
                    Application.Goto .Cells(rCel.Row, "D") 
                    Exit For 
                End If 
            End If 
        Next rCel 
    End With 
    
End Sub

Sure, I’d be happy to assist you further!

If you’re looking to replicate the functionality of the VBA code you shared within UiPath, you can accomplish this with a combination of activities. Here’s a step-by-step guide:

  1. :file_folder: Excel Application Scope Activity: This activity opens an Excel workbook and provides a scope for Excel Activities. When the execution of this activity ends, the specified workbook and the Excel application are closed. You need to specify the path of your Excel file in the “WorkbookPath” property.

  2. :mag: Read Range Activity (inside the Excel Application Scope): This activity reads the value of an Excel range and stores it in a DataTable. You need to specify the “SheetName” property as “Sheet1” and leave the “Range” property empty to read the entire sheet. Create a new DataTable variable (let’s call it “dt”) in the “DataTable” property to store the data.

  3. :arrows_counterclockwise: For Each Row Activity (outside the Excel Application Scope): This activity enables you to process each row from the DataTable. In the “DataTable” field, input the “dt” variable from the previous step.

  4. :jigsaw: If Activity (inside the For Each Row): This activity enables you to make decisions based on the value of a specific cell. In the “Condition” field, write row("C").ToString <> "0" And String.IsNullOrWhiteSpace(row("D").ToString). This condition checks if the value in the “C” column is not “0” and the “D” column is empty or null.

  5. :speech_balloon: Message Box Activity (inside the If activity): This activity shows a message box with a specific text. In the “Text” field, you can write "Cannot save while cell " + row("D").ToString + " is not populated. Please enter data and save again."

After this, you might want to add activities to handle the case where the condition in the If activity is not met.

Does this help to solve your issue or is there anything more specific you need assistance with?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)


Dim rngCell As Range
Dim lngLstRow As Long


lngLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("A1:A" & lngLstRow)
    If rngCell.Value = 0 Then
        MsgBox ("Please enter a name in cell " & rngCell.Address)
        rngCell.Select
    End If
Next
End Sub

What method should give in invoke vba

Firstly, you should know that the VBA code you provided is an Excel event, specifically the Workbook_BeforeSave event, which is triggered when you attempt to save a workbook. However, UiPath’s Invoke VBA activity doesn’t support Excel events directly.

But don’t worry, I will guide you on how to make a separate function in VBA that can be called from UiPath.

Here’s the step-by-step guide:

  1. Save the VBA code in a .txt file:Open a text editor like Notepad and paste your VBA code into it. Modify your VBA code to make it a function that can be invoked. Here’s how you can do it:
Public Function CheckEmptyCells()

Dim rngCell As Range
Dim lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count

For Each rngCell In Range("A1:A" & lngLstRow)
    If rngCell.Value = 0 Then
        MsgBox ("Please enter a name in cell " & rngCell.Address)
        rngCell.Select
    End If
Next

End Function

Save this file with a .txt extension. Let’s say you saved it as “VBA_Code.txt”.

  1. In UiPath, use Invoke VBA activity:This activity allows you to invoke a VBA function. To configure it:
    :file_folder: “CodeFilePath” field: Enter the path of the .txt file where you saved your VBA code. For example, "C:\VBA_Code.txt".
    :scroll: “EntryPoint” field: Enter the name of the VBA function you want to invoke. In this case, it’s "CheckEmptyCells".
    :label: “MethodName” field: Leave it blank as we are not invoking a method, but a function.

Now, when you run your UiPath workflow, it will invoke the VBA function which checks for empty cells in your Excel sheet.

Please note that this approach requires the “Trust Access to the VBA project object model” option to be enabled in Excel. This can be found in Excel Options > Trust Center > Trust Center Settings > Macro Settings.

Let me know if you need further assistance.