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