when drop down value is month or weeks or days, force user to enter required rows

[tsr_83]

I've an excel where a drop down list with 3 options (Days, Weeks, Months) in A1 cell.

My requirement:
When A1 value = Days, row 1 to row 20 are to be mandatory
When A1 value = Weeks, row 21 to row 25 are to be mandatory
When A1 value = Months, row 26 to row 30 are to be mandatory

User should not exit the cell unless the required values are entered.

[Paul_Hossler]

1. Welcome to the forum - read the FAQ's for Do's and Don't's

2. "Please" and "Thank You" go a long way. Members volunteer their time to help others

3. What have you done so far? This is a Help forum, not really a free coding service

[tsr_83]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If Cells(12, 4).Value = "VOL (DAYS)" Then
 
If Application.Sheets("sheet1").Range("C4,C5,C6,C7").Value = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End If
 
If Cells(12, 4).Value = "VOL (WEEKS)" Then
 
If Application.Sheets("sheet1").Range("C4").Value = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End If
 
If Cells(12, 4).Value = "VOL (MONTHS)" Then
 
If Application.Sheets("sheet1").Range("C6").Value = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End If
 
Application.EnableEvents = True
End Sub

The code works only for the first rule. The code is showing up the first message even when i select Weeks or Months.

[Paul_Hossler]

    If Application.Sheets("sheet1").Range("C4,C5,C6,C7").Value = "" Then

only tests C4. You need to repeat for each cell

Cells(12, 4) tests on the ActiveSheet - suggest that you add a WS name to it

I'd try something like this

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
With Worksheets("sheet1")
 
    Select Case .Cells(12, 4).Value   ' Note the'dot'
        Case "VOL (DAYS)"
            If Len(.Range("C4").Value) = 0 Or Len(.Range("C5").Value) = 0 Or Len(.Range("C6").Value) = 0 Or Len(.Range("C7").Value) = 0 Then
                Cancel = True
                MsgBox "Save cancelled"
            End If

        Case "VOL (WEEKS)"
            If Len(.Range("C4").Value) = 0 Then
                Cancel = True
                MsgBox "Save cancelled"
            End If

        Case "VOL (MONTHS)"
            If Len(.Range("C6").Value) = 0 Then
                Cancel = True
                MsgBox "Save cancelled"
            End If
    End Select
 
End With
End Sub