Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Message Box When Changing to another worksheet (Excel 2002)

    Hi

    I would like a Message box to appear when leaving a worksheetcalled Input , to remind a user he must enter something in cell C33 if it is blank before leaving the worksheet.

    I do not have any trouble with the Message Box, my problem is triggering the event.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Braddy

    You can use the Worksheet_Deactivate() event.
    <pre>Private Sub Worksheet_Deactivate()
    If IsEmpty(Range("C33").Value) Then
    MsgBox "Cell C33 is Blank"
    ' additional code...
    End If
    End Sub</pre>


  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    That's perfcect thanks.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    I have just discovered that I need two cells to be populated not just C33 the other cell is C5 can I adapt the code you have already given me if so could you please tell me how?

    Thanks Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Braddy

    You can use the OR operator
    <pre>Private Sub Worksheet_Deactivate()
    If IsEmpty(Range("C5").Value) Or IsEmpty(Range("C33").Value) Then
    MsgBox "Cells C5 and C33 must not be blank"
    ' Your own code
    End If
    End Sub</pre>


  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    The other thing I did not notice before, is the sheet changes before the msgbox comes up!

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Braddy

    You can add a line to the code that reselects the original sheet, now if either cell is blank the user can not leave the input sheet.
    <pre>Private Sub Worksheet_Deactivate()
    If IsEmpty(Range("C5").Value) Or IsEmpty(Range("C33").Value) Then
    Sheet1.Select
    MsgBox "Cells C5 and C33 must not be blank"
    ' Your own code
    End If
    End Sub</pre>


    You will need to replace Sheet1 with the actual sheet code name, or you could use Sheets("Input").Select
    For a discussion on that issue see Ozgrid discussion on sheet names

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    Grateful thanks that does the trick perfectly.

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    It's me again I am having the trouble of the sheet changing first again as you see below I have added another or but I have changed sheet1 to the name of the sheet to Installation request but it does not like it and I can't work out why!!


    Private Sub Worksheet_Deactivate()
    If IsEmpty(Range("F6").Value) Or IsEmpty(Range("AH32").Value) Or IsEmpty(Range("I68").Value) Then
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Installation Request.Select</span hi>

    MsgBox "Customer Number, Suggested delivery date and Deliver to: must not be blank"
    ' Your own code
    End If
    End Sub
    If you are a fool at forty, you will always be a fool

  10. #10
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Braddy

    You can use this code instead:
    <code>Sheets("Installation Request").Select</code>

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Message Box When Changing to another worksheet

    Hi Tony

    I thank you for your patience and your help with this. It is now working fine.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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