Vb script for mandotory column

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

Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False

ws=CType(wb.SheetDim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False

ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
rng=CType(ws.Range(PlaceRange),Microsoft.Office.Interop.Excel.Range)

2

Hi @Demo_User

Imports Microsoft.Office.Interop.Excel

Public Class ExcelAutomation
    Private excel As Application
    Private wb As Workbook
    Private ws As Worksheet
    Private rng As Range

    Public Sub OpenExcelFile(Filename As String, TargetSheet As String, PlaceRange As String)
        excel = New Application()
        wb = excel.Workbooks.Open(Filename)
        excel.Visible = False

        ws = CType(wb.Sheets(TargetSheet), Worksheet)
        rng = CType(ws.Range(PlaceRange), Range)

        ' Assign the Worksheet_Change event handler to the worksheet
        AddHandler ws.Change, AddressOf Worksheet_Change
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
        ' Check if the changed cell is in the first column (Column A)
        If Target.Column = 1 Then
            ' Check if the corresponding cell in the second column (Column B) is empty
            If Target.Offset(0, 1).Value = "" Then
                ' Prompt the user or take action to make the second column mandatory
                MsgBox("Column B is mandatory when filling Column A.")
            End If
        End If
    End Sub

    Public Sub CloseExcelFile()
        wb.Close()
        excel.Quit()

        ReleaseObject(rng)
        ReleaseObject(ws)
        ReleaseObject(wb)
        ReleaseObject(excel)
    End Sub

    Private Sub ReleaseObject(obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class
3

How to define this Worksheet_Change

4

Worksheet change is not declared it may inaccessible due to its protection level

5

@mkankatala could u please help on this

6

Hi @Demo_User

Try the below one.

' Assuming you have the Excel file already opened and stored in the variable "excelApp" (type: Microsoft.Office.Interop.Excel.Application)
' Assuming the worksheet name is "Sheet1"

' Get the worksheet
Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.ActiveWorkbook
Dim excelWorksheet As Microsoft.Office.Interop.Excel.Worksheet = excelWorkbook.Sheets("Sheet1")

' Assuming the first column is column "A" and the second column is column "B"
Dim firstColumnRange As Microsoft.Office.Interop.Excel.Range = excelWorksheet.Range("A:A")
Dim secondColumnRange As Microsoft.Office.Interop.Excel.Range = excelWorksheet.Range("B:B")

' Loop through the rows in the first column
For Each firstCell As Microsoft.Office.Interop.Excel.Range In firstColumnRange.Cells
    ' Get the corresponding cell in the second column
    Dim secondCell As Microsoft.Office.Interop.Excel.Range = excelWorksheet.Cells(firstCell.Row, 2)

    ' Check if the first column cell is not empty
    If Not IsNothing(firstCell.Value2) Then
        ' Set the second column cell as mandatory by applying data validation
        Dim dataValidation As Microsoft.Office.Interop.Excel.Validation = secondCell.Validation
        dataValidation.Delete() ' Delete any existing data validation
        dataValidation.Add(Type:=Microsoft.Office.Interop.Excel.XlDVType.xlValidateCustom, AlertStyle:=Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Formula1:="LEN(TRIM(B" & firstCell.Row & "))>0")
        dataValidation.IgnoreBlank = True
        dataValidation.ErrorTitle = "Validation Error"
        dataValidation.ErrorMessage = "Please fill in this column as it is mandatory when the first column is filled."
        dataValidation.ShowError = True
    Else
        ' If the first column cell is empty, remove any data validation from the second column cell
        Dim dataValidation As Microsoft.Office.Interop.Excel.Validation = secondCell.Validation
        dataValidation.Delete()
    End If
Next

Hope it helps!!

8

Getting run time exception
At microsoft.office.interop.excel_worksheet.get_Range(object cell1,Object cell2)

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.