Consulting

Results 1 to 17 of 17

Thread: Asking user to please enter all mandatory filed

  1. #1

    Asking user to please enter all mandatory filed

    Hi

    Marketing Plan Name
    Related Initiatives
    Region
    Start Period
    End Period
    Business Unit

    I have above coulumns in my Excel sheet i would like make the sheet without entreing data into these filed should show an error while saving the file

    "please enter all mandatory filed"

    without user form

    Can any one help on this

    Regards
    Rajesh

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Quote Originally Posted by rajesh nag
    Hi

    Marketing Plan Name
    Related Initiatives
    Region
    Start Period
    End Period
    Business Unit
    If these are in column A and your required fields are in column B then put this in the ThisWorbook module.
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i
    For i = 1 To 6
        If Range("B" & i) = "" Then
            MsgBox "Please enter all mandatory fields!"
            Cancel = True
        End If
    Next
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    Hi these columns are in A:F source

    A:-Marketing Plan Name
    B:-Related Initiatives
    C:-Region
    D:-Start Period
    E:-End Period
    F:-Business Unit

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Range("a1") = "" Or Range("b1") = "" Or Range("c1") = "" Or _
           Range("d1") = "" Or Range("e1") = "" Or Range("f1") = "" Then
        MsgBox "Please enter all mandatory fields!"
        Cancel = True
        End If
    End Sub
    Semper in excretia sumus; solum profundum variat.

  5. #5

    Asking user to please enter all mandatory filed

    Hi


    I have attached two files one with user form another without


    A:-Marketing Plan Name
    B:-Related Initiatives
    C:-Region
    D:-Start Period
    E:-End Period
    F:-Business Unit

    I have above coulumns in my Excel sheet i would like make the sheet without entreing data into these filed should show an error while saving the file

    "please enter all mandatory filed"

    without user form, i want the same machanism showing error "please enter all mandatory filed" to test file as in mktg plan

    Can any one help on this

    Rajesh

  6. #6
    http://vbaexpress.com/forum/showthread.php?p=125774

    i have attached files in this can u pls help mr.Paulked

  7. #7
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    I haven't looked at your workbooks, however you can use this in the Thisworkbook module (get there by pressing Alt+F11, right click thisworkbook view code then paste in).
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim MyCell As Range
        Dim Rng As Range
        Set Rng = Sheets("Sheet1").Range("A1:Z100") 'set your range
        For Each MyCell In Rng 'check each cell in that range
        If IsEmpty(MyCell) Then 'if it is empty
            MsgBox "Empty Cells exist" 'actions To Do If True
            MyCell.Select 'goto the offending cell
            Exit Sub
        End If
        Next
    End Sub
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Threads merged
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location
    Hi rajesh,

    I'm sure you want the code to do more but here's a solution that does what you asked for.

    - It checks Cols A to F for the largest number of rows
    - Then checks each cell in those Columns
    - If it finds a blank in any of the cells it displays a message and stops (it cancels the save)

    to use it:

    1) Open the VBE (Alt+F11)

    2) Double click on "This Workbook" module and paste the code in

    3) Close the VBE

    4) Edit and save the file

    Option Explicit
     
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim A As Long
    Dim B As Long
    Dim C As Long
    Dim D As Long
    Dim E As Long
    Dim F As Long
    Dim xRow As Long
    Dim xCol As Long
    Dim lastrow As Long
    ' Get last row of each column
    A = Range("A65536").End(xlUp).Row
    B = Range("B65536").End(xlUp).Row
    C = Range("C65536").End(xlUp).Row
    D = Range("D65536").End(xlUp).Row
    E = Range("E65536").End(xlUp).Row
    F = Range("F65536").End(xlUp).Row
    ' Check which is largest
    If A = B And A = C And A = D And A = E And A = F Then
    ' all equal - use Col A
    lastrow = A
    ElseIf A >= B And A >= C And A >= D And A >= E And A >= F Then
    ' is A (typ)
    lastrow = A
    ElseIf B >= A And B >= C And B >= D And B >= E And B >= F Then
    lastrow = B
    ElseIf C >= A And C >= B And C >= D And C >= E And C >= F Then
    lastrow = C
    ElseIf D >= A And D >= B And D >= C And D >= E And D >= F Then
    lastrow = D
    ElseIf E >= A And E >= B And E >= C And E >= D And E >= F Then
    lastrow = E
    ElseIf F >= A And F >= B And F >= C And F >= D And F >= E Then
    lastrow = F
    End If
    'check all cells
    ' All rows
    For xRow = 2 To lastrow
    ' All Cols
    For xCol = 1 To 6
    'If blank
    If Cells(xRow, xCol) = "" Then
    ' Inform user
    MsgBox ("Mandatory field!")
    ' Select blank cell
    Cells(xRow, xCol).Select
    ' Cancel save
    Cancel = True
    'bail
    Exit Sub
    End If
    Next xCol
    Next xRow
    End Sub
    This one might be faster as it uses the 'Find' command instead of checking every cell:

    Option Explicit
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim A As Long
    Dim B As Long
    Dim C As Long
    Dim D As Long
    Dim E As Long
    Dim F As Long
    Dim lastrow As Long
        ' Get last row of each column
        A = Range("A65536").End(xlUp).Row
        B = Range("B65536").End(xlUp).Row
        C = Range("C65536").End(xlUp).Row
        D = Range("D65536").End(xlUp).Row
        E = Range("E65536").End(xlUp).Row
        F = Range("F65536").End(xlUp).Row
        ' Check which is largest
        If A = B And A = C And A = D And A = E And A = F Then
            ' all equal - use Col A
            lastrow = A
        ElseIf A >= B And A >= C And A >= D And A >= E And A >= F Then
            ' is A (typ)
            lastrow = A
        ElseIf B >= A And B >= C And B >= D And B >= E And B >= F Then
            lastrow = B
        ElseIf C >= A And C >= B And C >= D And C >= E And C >= F Then
            lastrow = C
        ElseIf D >= A And D >= B And D >= C And D >= E And D >= F Then
            lastrow = D
        ElseIf E >= A And E >= B And E >= C And E >= D And E >= F Then
            lastrow = E
        ElseIf F >= A And F >= B And F >= C And F >= D And F >= E Then
            lastrow = F
        End If
    'allow not found
        On Error GoTo endo
        'find blank cell
        Range(Cells(2, 1).Address, Cells(lastrow, 6).Address).Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    ' Inform user
    MsgBox ("Mandatory field!")
        ' Cancel save
        Cancel = True
    endo:
        On Error GoTo 0
    End Sub
    Last edited by rbrhodes; 12-09-2007 at 10:21 AM.
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http:\\www.ExcelVBA.joellerabu.com

  10. #10
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    Hi,

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim r As Long, a, i As Long
    r = Sheets("Sheet1").Range("a1").CurrentRegion.Rows.Count
    a = Range(Cells(r, 1), Cells(r, 6))
    For i = 1 To UBound(a, 2)
        If Len(Application.Trim(a(1, i))) = 0 Then
            MsgBox "Please enter all mandatory fields!"
            Sheets("Sheet1").Cells(r, i).Activate
            Cancel = True
        End If
    Next
    End Sub

  11. #11
    Hi

    The codes are not working for me, I was able to close the file with out any data in all other columns, In line two where the values present in column a and b still there is no error while saving the file or closing the file

    I have created my own module

    Module 2

    After running the module it is showing error

    i dont want my user to run the module it should be hided

    If i use private it's not working

    I have one more problem in my module it is showing errro even all the columns are it shoul show error if any one of the column contains any value.

    Please help me on this

    Regards
    Rajesh

  12. #12
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    Hi Rajesh,

    Place the code in ThisWorkbook module.

  13. #13
    I have posted in work book module still its not working please down load the attachment and could u pls help me trying in it

  14. #14
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    Hi,

    See the attachment.

    HTH

  15. #15
    Thanks a lot for spending your time for me

    but i have a trouble it is working only for last line

    if any values in previous rows is null it is not showing any error

    could you please help me on this

    Regards

    Rajesh

  16. #16
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    Replace the old code with the following

    Public Flg  As Boolean
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Sample
    If Flg Then Cancel = True
    End Sub
    Private Sub Sample()
    Dim r As Long, a, i As Long, ws As Worksheet
    Set ws = Sheets("Sheet1")
    a = ws.Range("a1").CurrentRegion
    Flg = False
    For i = 1 To UBound(a, 2)
        With Application
            If .CountA(.Index(a, 0, i)) <> UBound(a, 1) Then
                Flg = True
                MsgBox "Please enter all mandatory fields!"
                ws.Activate
                Exit Sub
            End If
        End With
    Next
    End Sub

  17. #17
    thanks a lot for helping me to soleve my issue

Posting Permissions

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