CODEWITHSUNDEEP
excelvba
user2732656
user2732656

Reputation: 13

Checking Values in Cells

I made a Sub. But its giving an error.! I want to check if column A has values then it checks for columns" H and I ". those must be filled. Else the file wont save..!! 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim cell As Range
    Dim LastRow As Long
    Dim oneRange As Range
    Dim aCell As Range

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    If cell.Value(Range("A8:A" & LastRow)) Is Nothing Then
        Exit Sub
    Else
        For Each cell In Range("H8:I" & LastRow)
            If IsEmpty(cell.Value) Then
                MsgBox "Please Select Value from Dropdown Menu...." & cell.Address
                Application.Goto cell
                Cancel = True
                Exit For
            End If
        Next cell

    End If
End Sub

Upvotes: 0

Views: 64

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

sheet1 and sheets(1) is often not the same. Best is using the name of the sheet. You might miss a "." at the second for (...in .range...) ?

PS, i'm new in VBA, what does application.goto aCell, do ? because it strage to put a "exit for" after a "goto"...

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

TRIED AND TESTED

Is this what you are trying?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim lRow As Long
    Dim aCell As Range

    Set ws = ThisWorkbook.Sheets("receivings")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        If Application.WorksheetFunction.CountA(.Range("A8:A" & lRow)) = 0 _
        Then Exit Sub

        For Each aCell In Range("H8:I" & lRow)
            If IsEmpty(aCell.Value) Then
                MsgBox "Please Select Value from Dropdown Menu...." _
                & aCell.Address

                Application.Goto aCell
                Cancel = True
                Exit For
            End If
        Next aCell
    End With
End Sub

Upvotes: 1

Related Questions