VBA Help for Beginner

[SharonVobs]

Hi

I am new here and to VBA and am looking to make some cells in my spreadsheet mandatory if another cell is completed,

If column B is complete then there must be something in C,E,F and this would be for rows 1:17

Thanks in advance

Sharon

 

[dreid1011]

Welcome to the board.

When should the code check to see if the cells are filled? Before saving/closing workbook? And will Column B always have something in it from 1 to 17? Can it just be 1 to 5, 10, etc?

 

[SharonVobs]

The code should check when saving\closing the workbook and column B will not always be complete it could be just 1-5, etc

 

[dreid1011]

Ok, with that being said, I imagine you want a message box or warning to popup telling the user they missed some data entry and not save/close until the columns have all the data?

 

[SharonVobs]

Hi. yes a message box telling the user to complete the required fields.

 

[mumps]

Place these 2 macros in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macros into the empty window that opens up. Close the window to return to your sheet. Save the workbook as a macro-enabled file.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range, rng2 As Range
    For Each rng In Sheets("Sheet1").Range("B1:B17")
        If rng <> "" Then
            If rng.Offset(0, 1) = "" Then
                MsgBox ("Cell " & rng.Offset(0, 1).Address(0, 0) & " has no data.  Please complete.")
                rng.Offset(0, 1).Select
                Cancel = True
                Exit Sub
            End If
            For Each rng2 In Range("E" & rng.Row & ":F" & rng.Row)
                If rng2 = "" Then
                    MsgBox ("Cell " & rng2.Address(0, 0) & " has no data.  Please complete.")
                    rng2.Select
                    Cancel = True
                    Exit Sub
                End If
            Next rng2
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range, rng2 As Range
    For Each rng In Sheets("Sheet1").Range("B1:B17")
        If rng <> "" Then
            If rng.Offset(0, 1) = "" Then
                MsgBox ("Cell " & rng.Offset(0, 1).Address(0, 0) & " has no data.  Please complete.")
                rng.Offset(0, 1).Select
                Cancel = True
                Exit Sub
            End If
            For Each rng2 In Range("E" & rng.Row & ":F" & rng.Row)
                If rng2 = "" Then
                    MsgBox ("Cell " & rng2.Address(0, 0) & " has no data.  Please complete.")
                    rng2.Select
                    Cancel = True
                    Exit Sub
                End If
            Next rng2
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[dreid1011]

Since mumps has provided you a solution, I will leave you with that.

 

[SharonVobs]

Thanks both for your help I will give this a go.

 

[SharonVobs]

That worked perfectly thanks again.

 

[mumps]

Glad to help.