Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: excel pop up message

  1. #11
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    i got type mismatch on this line : If cell <= 7 Then
    That seems to imply that you have some non-numeric entries in column BX (other than row 1, which it is ignoring).

    You can try changing it to:
    Code:
    If cell.Value <= 7 Then
    but I still suspect you may have some non-numeric data there.
    Can you confirm that?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #12
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    If you are having trouble locating the error, this variation should tell you what line it is having issues with:
    Code:
    Private Sub Workbook_Open()
    
        Dim lr As Long
        Dim cell As Range
        Dim ct As Long
        Dim msg As String
        
        Application.ScreenUpdating = False
        
        Sheets("Data").Activate
        
        msg = "Adjustment is Below the Total in rows "
        
    '   Find last row with data in column BX
        lr = Cells(Rows.Count, "BX").End(xlUp).Row
        
    '   Loop through all rows
        On Error GoTo err_chk
        For Each cell In Range("BX2:BX" & lr)
            If cell.Value <= 7 Then
                cell.Interior.ColorIndex = 3
                cell.Font.ColorIndex = 2
                cell.Font.Bold = True
                ct = ct + 1
                msg = msg & cell.Row & ","
            End If
        Next cell
        On Error GoTo 0
        
        Application.ScreenUpdating = True
        
    '   Return message if found any cells
        If ct > 0 Then MsgBox msg
    
    
        Exit Sub
        
        
    err_chk:
        MsgBox "Error in cell " & cell.Address(0, 0)
        Application.ScreenUpdating = True
    
    End Sub
    Three things to check with the cell it is having issues with:
    - What is in that cell?
    - Is it protected?
    - Is it part of a merged cell?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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

    Default Re: excel pop up message

    you are right joe .. some numbers are showing #VALUE ! instead ..maybe it is an issue with a prior formula

  4. #14
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    Yep, sounds like you may have an issue to correct.
    If those values in column BX are being returned by formulas, you can update your formula to handle that, using an IFERROR formula.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #15
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel pop up message

    the error is some of the cells referenced by the formulas are empty .. but the user used the formula on the rows ..
    can we workaround this ?

  6. #16
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    What do you want it to return in that cell in the event of an error?

    If you wanted a zero, you would replace the current formula with this:
    =IFERROR(current formula,0)

    If you wanted a blank, it would look like this:
    =IFERROR(current formula,"")

    Anything else, just change the last argument of the formula above to whatever you want to return instead of the error.

    Here is a good write-up on the IFERROR formula: https://exceljet.net/excel-functions...error-function
    Last edited by Joe4; Nov 9th, 2018 at 10:12 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #17
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel pop up message

    i did not understand .. where should i add this exactly : =IFERROR(current formula,"")

    i want the macro to ignore the error and continue working

  8. #18
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    i want the macro to ignore the error and continue working
    I am saying it is better to correct the formula returning the error. If IFERROR formula will return the result of your original formula, but if it encounters an error, it will return whatever you tell it to in that case.

    For example, let's say that your current formula is something simple like:
    Code:
    =A1/B1
    You would just change that formula to:
    Code:
    =IFERROR(A1/B1,"")
    So if it can evaluate A1/B1 without any errors, it will do that and return the value.
    But if evaluating the formula would result in errors, it will return whatever you designate instead of the error (the empty string, in this case).

    It is typically better to address errors at their source, than it is to try to ignore them down the road.
    Otherwise, you would need to add another IF block in your VBA code and nest the other one under it. You can do that, but it could affect performance, as you are adding another check it has to do for every row of data you have, i.e.
    Code:
    Private Sub Workbook_Open()
    
        Dim lr As Long
        Dim cell As Range
        Dim ct As Long
        Dim msg As String
        
        Application.ScreenUpdating = False
        
        Sheets("Data").Activate
        
        msg = "Adjustment is Below the Total in rows "
        
    '   Find last row with data in column BX
        lr = Cells(Rows.Count, "BX").End(xlUp).Row
        
    '   Loop through all rows
        On Error GoTo err_chk
        For Each cell In Range("BX2:BX" & lr)
            If IsError(cell.Value) Then
            Else
                If cell.Value <= 7 Then
                    cell.Interior.ColorIndex = 3
                    cell.Font.ColorIndex = 2
                    cell.Font.Bold = True
                    ct = ct + 1
                    msg = msg & cell.Row & ","
                End If
            End If
        Next cell
        On Error GoTo 0
        
        Application.ScreenUpdating = True
        
    '   Return message if found any cells
        If ct > 0 Then MsgBox msg
    
        Exit Sub
        
        
    err_chk:
        MsgBox "Error in cell " & cell.Address(0, 0)
        Application.ScreenUpdating = True
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #19
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel pop up message

    this code worked i think , but i am not able to run it from inside excel .. i think i am doing something wrong
    1-go into developper tab -> visual basic
    2-insert module
    3-add the code
    4-click on save
    5-close the visual basic
    6-click on macros in developer tab : it is empty ( even after restarting excel )
    7-i wanted the macro to start each time i open the page

  10. #20
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: excel pop up message

    The issue is here:
    2-insert module
    There are two kinds of Procedures in VBA:
    1. Event Procedures, which run automatically in Excel upon some event happening (like the opening of a file, updating of a cell, etc).
    2. All other procedures, which run when called by the user. Typically, these are placed in a Module which you insert yourself.

    The "Workbook_Open" procedure is an Event Procedure. There are very specific requirements for Event Procedures:
    1. They must be named a certain way. There is no leeway in how you name the procedure (you cannot change it).
    2. They MUST be placed in the proper module. If placed in a General Module that you insert, they will NOT work.
    "Workbook_Open" Event Procedures MUST be placed in the "ThisWorkbook" Module, which is automatically included in every workbook. That is where you need to place this code.

    So if you move your code to the "ThisWorkbook" module, then as long as VBA/Macros are enabled, the code will run when the workbook is opened.
    Last edited by Joe4; Nov 9th, 2018 at 11:46 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

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
  •