Cells required in excel Private Sub Workbook_BeforeSave(Cancel As Boolean) If Sh

Question

Cells required in excel

Private Sub Workbook_BeforeSave(Cancel As Boolean)
If Sheet1.Range("A3:B3").Value = "" Then
Application.EnableEvents = True
MsgBox "Please enter info in required cells.!"
Cancel = True     
End If  
End Sub

I found above code and change this code to fit what I need in my workbook.

I have a Table 1 in Sheet 1.

This table's start row is 6 and row 5 is the header.

This table's last row which has data is changing, but this table size is A6 to J35.

I want to column F is mandatory column, so users have to enter data.

I added 'Dim lastRow', but it always indicated row 35.

For example, if I have data until row 10, then lastRow is supposed to be 10, but it says lastRow = 35.

My code is;

Private Sub Workbook_BeforeSave(Cancel As Boolean)

For r = 6 to lastRow

If Sheet1.Range("F6:F" & r).value = "" Then

masbox " Pls enter"

Cancel = true

End if

How can I fix this problem?

Thanks

Explanation / Answer

Change the column definition of the variable LRow from F to J and try:

'=========>>
Option Explicit

'--------->>
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim SH As Worksheet
Dim Rng As Range, RngEmpty As Range, myCell As Range
Dim LRow As Long


Set SH = Me.Sheets("Sheet1")

With SH
LRow = Cells(Rows.Count, "J").End(xlUp).Row
Set Rng = SH.Range("F6:F" & LRow)
End With

On Error Resume Next
Set RngEmpty = Rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not RngEmpty Is Nothing Then
Cancel = True
Set myCell = RngEmpty.Cells(1)
Call MsgBox(Prompt:="The following cell on " _
& SH.Name & " needs data: " _
& vbNewLine _
& myCell.Address(0, 0), _
Title:="Unable to Save!")
Application.Goto myCell
End If

End Sub
'<<=========

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.