Consulting

Results 1 to 10 of 10

Thread: Fields mandatory

  1. #1
    VBAX Regular
    Joined
    Nov 2020
    Posts
    7
    Location

    Fields mandatory

    Hello Community,

    I am creating an excel sheet where if the user has filled a cell in column A, he has to fill input also on column B, C and D, otherwise, he shouldn't be able to save and close the file.

    Does someone have an idea on how could be this enabled?

    Thank you in advance,
    Alina

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I am creating an excel sheet where if the user has filled a cell in column A, he has to fill input also on column B, C and D, otherwise, he shouldn't be able to save and close the file.

    Does someone have an idea on how could be this enabled?
    Yes

    In the workbook code module. If you want to check all or most sheets, there's an easy way to do that also (edited a typo)

    Option Explicit
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Cancel = NotOkay
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Cancel = NotOkay
    End Sub
        
        
    Private Function NotOkay() As Boolean
        Dim rColA As Range, rCell As Range
        
        NotOkay = False
        
        With Worksheets("Sheet1")
            Set rColA = Nothing
            On Error GoTo 0
            Set rColA = Intersect(.UsedRange, .Columns(1))
            On Error GoTo 0
        End With
        
        If rColA Is Nothing Then Exit Function
            
        For Each rCell In rColA.Cells
            If IsEmpty(rCell) Then GoTo NextCell
            
            With rCell
                If IsEmpty(.Offset(0, 1)) Then
                    Call MsgBox("Cell " & .Offset(0, 1).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                    NotOkay = True
                    Exit Function
                
                ElseIf IsEmpty(.Offset(0, 2)) Then
                    Call MsgBox("Cell " & .Offset(0, 2).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                    NotOkay = True
                    Exit Function
                
                ElseIf IsEmpty(.Offset(0, 3)) Then
                    Call MsgBox("Cell " & .Offset(0, 1).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                    NotOkay = True
                    Exit Function
                End If
            End With
    NextCell:
        Next
    End Function
    Attached Files Attached Files
    Last edited by Paul_Hossler; 11-23-2020 at 07:30 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Nov 2020
    Posts
    7
    Location
    Thanks a lot Paul, it worked!
    Cheers,
    Alina

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    That macro required that the empty cells be 'fixed' on at a time

    Another approach that I typically use is to do it all at once by shading the incorrect cells, making a Msgbox "There are 15 empty cells, shaded gray", and letting the user do all at once

    Let me know; it's not much different from the original macro
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Nov 2020
    Posts
    7
    Location
    Can you please provide me the code for that too?
    Thank you in advance,
    Alina

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Maybe start with something like this

    I added logic so that the Msgbox would not show on both the Close and the Save events, and added an option to save even with missing data (you can delete that if you really don't want it)

    Option Explicit
    
    
    Dim NotOkay As Boolean, bClosed As Boolean
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
        If bClosed Then
            Cancel = False
        
        Else
            NotOkay = False
            CheckRequiredCells
            Cancel = NotOkay
        End If
    End Sub
    
    
    
    
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        NotOkay = False
        bClosed = False
        CheckRequiredCells
        Cancel = NotOkay
        If Not NotOkay Then bClosed = True
    End Sub
        
        
    Private Sub CheckRequiredCells()
        Dim rColA As Range, rCell As Range
        Dim n As Long, i As Long
        
        With Worksheets("Sheet1")
            Set rColA = Nothing
            On Error GoTo 0
            Set rColA = Intersect(.UsedRange, .Columns(1))
            On Error GoTo 0
        End With
        
        If rColA Is Nothing Then Exit Sub
            
        For Each rCell In rColA.Cells
            If Not IsEmpty(rCell) Then
                With rCell
                    .Offset(0, 1).Resize(1, 3).Interior.ColorIndex = xlColorIndexNone
                    For i = 1 To 3
                        If IsEmpty(.Offset(0, i)) Then
                            n = n + 1
                            .Offset(0, i).Interior.ColorIndex = 15
                        End If
                    Next i
                End With
            End If
        Next
    
    
        If n > 0 Then
            If MsgBox("There are " & n & " cells (shaded Gray) that need filling" & vbCrLf & vbCrLf & _
                "Save anyway?", vbYesNo + vbQuestion, "Filling Cells") = vbNo Then
                NotOkay = True
            End If
        End If
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Nov 2020
    Posts
    7
    Location
    Thank you very much Paul!

  8. #8
    VBAX Newbie
    Joined
    Dec 2020
    Posts
    2
    Location
    So the difference here is that the second solution highlights all the cells that need to be filled and tells you how much of them there is, while the first one tells you the earliest cell that threw the error?

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Or:
    if you fill in something in column C, and columns D to J are obligatory:

    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column & Target.Count = 31 Then Target.Offset(, 1).Resize(, 6).FormatConditions.Add(xlBlanksCondition).Interior.ColorIndex = 3
    End Sub

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by FKemp View Post
    So the difference here is that the second solution highlights all the cells that need to be filled and tells you how much of them there is, while the first one tells you the earliest cell that threw the error?
    Post #2 macro does them one at a time (since that's what the OP originally thought they wanted)

    Post #6 was a suggestion based on the way I'd re-think the desired results, which is to high light all cells with missing data and say "Fix these"
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •