CODEWITHSUNDEEP
excelvba
Kaushal Kapoor
Kaushal Kapoor

Reputation: 472

Making fields mandatory of a specific sheet on workbook save

I am using macros in excel to make fields mandatory in excel workbook. However, the problem is that the workbook contains multiple worksheets and the macro applies to all the worksheets.

Is there a way to target a specific worksheet in the workbook?

Below is the code i am using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cells(5, 2).Value = "" Then
    MsgBox "Please fill cell B5"
    Cancel = True
End If
End Sub

Upvotes: 2

Views: 1781

Answers (3)

brettdj
brettdj

Reputation: 55692

More directly

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Set ws = Sheets("Your sheet name here")
If Len(ws.[b5]) = 0 Then
    MsgBox "Please fill cell B5 on sheet " & ws.Name, vbCritical, "File not saved"
    Cancel = True
    Application.Goto ws.[b5]
End If
End Sub

Upvotes: 0

user2941651
user2941651

Reputation:

Please try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  For i = 1 To ThisWorkbook.Sheets.Count 
   If ThisWorkbook.Sheets(i).Name = "Name of specific sheet" Then
     If ThisWorkbook.Sheets(i).Cells(5, 2).Value = "" Then
        MsgBox "Please fill cell B5"
        Cancel = True
     End If
   End If
  Next i
End Sub

Where the "Name of specific sheet" is the worksheet name that you validate Cells(5, 2).Value = "" for.

Upvotes: 1

Gene Skuratovsky
Gene Skuratovsky

Reputation: 581

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim shX As Worksheet
    Dim rX As Range

    Set shX = ThisWorkbook.Worksheets("Sheet1")
    Set rX = shX.Cells(5, 2)
    If rX = "" Then
        MsgBox "Please fill cell B5"
        Cancel = True
        shX.Select
        rX.Select
    End If
End Sub

Upvotes: 0

Related Questions