Mac – VBA making cells mandatory based on previous cell entry

macrosmicrosoft excelvba

Is there a way of making a number of cells mandatory based on the entry of a cell? The cell contents may vary.

Best Answer

You would create a "BeforeSave" sub in your workbook VBA. To do this:

Open Excel VBA (Alt+F11)
Click ThisWorkbook
There are two bars above the code window, click the first bar and select "Workbook"
Click the second bar and click "Before save" This will fill in your sub for you

Then add the following (Assuming Cell 1A was to be your reference cell and 2A was mandatory):

    If Not IsEmpty(Cells(1,1)) Then
       If Cells(2,1).Value = "" Then
          MsgBox "Cell 2A requires user input before saving"
          Cancel = True
       End if
    End if

You can then just add elses for any other cells you want to check

Related Solutions

Mac – Making a cell mandatory based on previous cell entry

Based on my understanding of your question, I suggest a VBA Solution. In this example the sample column range is H2:H7 and your intended Validation is already in place for this range.

In your worksheet, press ALT + F11 to access VBA Editor. Under Left Pane see Microsoft Excel Objects. Below that double click the intended worksheet to open the Even Driven programming window. From the first drop down select Worksheet then from the second drop down select Change. This is Worksheet Change Event. The following lines will be displayed in the code editor.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Put the following code between these two lines.

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
    Exit Sub
End If

Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("H2:H7"))
If isect Is Nothing Then

Else

 If Target.Value = "High" Or Target.Value = "Medium" Or Target.Value = "Low" Then
     com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)

     Do While comm1 = ""
        comm1 = Application.InputBox(prompt:=com, Type:=2)
        On Error GoTo myloop
        If comm1 = False Then
            comm1 = ""
        End If
myloop:
     On Error GoTo -1
     Loop
     Target.Offset(0, 1).Value = comm1

 Else
    Target.Offset(0, 1).Value = ""  'Remove this line if not desired
 End If

End If

Save the file as Macro Enabled Excel workbook. Back in the worksheet. Test this code by selecting the intended values from the Validation List Box and see if it works for you and meets your requirment. This code may not have extensive error checking though.

One small thing I have added is later if you change the status to Pass the comment is removed from the column I.

Best Answer

Related Solutions

Mac – Making a cell mandatory based on previous cell entry

Related Question