1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Message box for the given Criteria on all the sheets

Discussion in 'Business Applications' started by VJAN, Dec 24, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Hi,

    Need a help message box, whenever my given criteria is matching the cell it should pop up message.

    I have written my queries in the sheet attached so that you will be able to help me on the same.
    So please have a look at the attached sample sheet
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Is the problem that the further down the sheet you are it's difficult to keep track of which "Part" you are updating?

    You could just freeze the top 5 rows, then apply a conditional formatting rule to B1:B5; if cell value is greater than 69, pattern (AKA fill colour) = red.

    That way there would always be a visible visual check, and no VBA.
     
  3. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Hello Bomb good to here from you.
    I want the Message box to pop up only when the Part of B1:B5 is equal to 70, it may exceed 70, but I want it to pop up only upon showing 70.

    If the conditional formatting works its good can I have the same with the message box.
    Please help.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    This (for the workbook module) seems to fire for the active worksheet only -- HTH.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name = "Sheet1" Then Exit Sub
    If Target.Column <> 4 Then Exit sub
    If WorksheetFunction.CountIf(Range("B1:B5"), 70) = 0 Then Exit Sub
    x = Target.Offset(, -3).End(xlUp).Row
    x = Right(Cells(x, 1), 1)
    If Cells(x, 2) = 70 Then
    MsgBox "Part " & x & " equals 70 - do you want to" & vbCrLf & _
    "have a break or continue?"
    End If
    End Sub
     
  5. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Thanks it works
    but if I want the Part B1:B5 to be Change the place to B1:F1 then it won't work.
     
  6. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Bomb sorry for the changes, but would appreciate if you help me through
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Try this. Change:

    Range("B1:B5")

    to:

    Range("B1:F1")

    And change:

    If Cells(x, 2) = 70 Then

    to:

    If Cells(1,1).Offset(,x) = 70 Then
     
  8. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Hi Bomb thanks its working with my sample sheet.

    I have the original data sheet which is totally different then this what have uploaded. So I tired to solve my queries by posting the sample sheet which really worked and I'm really thankful to you for that. so I thought I can modify the codes and work on but couldn't do so, as I'm not much in coding.
    Having known that I cannot uploaded my original sheet here I'm uploading my sample sheet again over here.
    I Want a Message to Pop up when Cell D2:J2 have the count of 70.
    Sorry for really troubling you by changing my criteria ever now an then.
     

    Attached Files:

  9. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Living for the day. Wish you Happy Christmas Bomb
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Same to you. :)

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name = "Sheet1" Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    If WorksheetFunction.CountIf(Range("D2:J2"), 70) = 0 Then Exit Sub
    x = Target.Offset(, -3).End(xlUp).Row
    x = Replace(Cells(x, 1), "Part ", "")
    If Application.Index(Range("D2:J2"), 1, x) = 70 Then
    MsgBox "Part " & x & " equals 70 - do you want to" & vbCrLf & _
    "have a break or continue?"
    End If
    End Sub
     
  11. VJAN

    VJAN Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    35
    Hi Bomb,

    Can I have your email id so that I can send you my original sheet. And explain what I really require.
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You can send me a private message for my email address.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Message given Criteria
  1. gars45
    Replies:
    4
    Views:
    375
  2. laarp
    Replies:
    1
    Views:
    523
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/888026

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice