Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: MsgBox for unmet conditions (?)

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey! I'm looking for a way to have a message box appear when a person tries to email, save and/or print a worksheet AND they haven't filled in all required fields. The fields are scattered throughout the sheet (ie: A1,B50,C12)
    PLEASE help. This is driving me nuts.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Save and Print are "easy", but Email... that's a complicated one. I feel that the easy way out would be to replace the current menus with your own, and test, when they are run, if they have filled the info.

    To do Save and Print you use the Workbook events, BeforeSave and BeforePrint


    _________________
    Regards,

    Juan Pablo G.
    MrExcel.com Consulting

    [ This Message was edited by: Juan Pablo G. on 2002-02-20 11:54 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok. I thought I could keep up but quite obviously I'm a novice. Can you give me a specific...in other words could I beg you enough to actually spell it out for me? I'd be grateful forever.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, let's take it step by step then.

    First, create this function in a module

    Function Validate() as Boolean
    Dim Rng as Range
    Dim Cll as Range

    Validate = True 'This is the default
    Set Rng = Union(Range("A1"),Range("B50"),Range("C12"))

    For each Cll in Rng
    If Len(Cll) = 0 then 'Cell is Empty
    Validate = False
    Exit Function
    End If
    Next Cll
    End Function
    And in your code you can use it like this:

    Sub Test()
    If Not Validate Then MsgBox ("Hey !, FILL ME UP")
    End Sub
    This is the first step. Tell me when you have it completed.

    _________________
    Regards,

    Juan Pablo G.
    MrExcel.com Consulting

    [ This Message was edited by: Juan Pablo G. on 2002-02-20 11:59 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok. I'm with you so far...

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, now right click on the little workbook icon near the "File" menu, and select view code. That should take you to the VB Editor.

    Put this there.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Not Validate Then
    MsgBox "Hey, FILL ME UP !"
    Cancel = True
    End If
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not Validate Then
    MsgBox "Hey, FILL ME UP !"
    Cancel = True
    End If
    End Sub

    That way, the user will not be able to Save or Print unless they fill the values.

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah JUAN! You are the true GuRu! Thanks a million!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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