none
How do I error handle when a cell is blank and there are many routines RRS feed

  • Question

  • I have a sheet with a lot of code that I have worked on throughout the last couple of years but I am new to VBA and did not incorporate any error handling. I tried a few things but I could never make it work. It did not really affect me until recently. Most of my routines are driven by a cell that contains a sales price (cell D5). I am using Worksheet_Change ByVal target as range to change things when different cells are changed so when the sales price is changed it starts running routines. What I have noticed is that this particular routine is the first one that gives me a VBA error if I delete the sales price in cell D5. So I thought I can ask someone to give me a simple code to catch the empty cell before the routine fires and maybe a popup saying "Sales Price cannot be blank" and perhaps revert to the previous value of that cell. The debug error takes me to the line of code that starts with "If Sheets("Main").Range("D6").Value < 0.8001"

    Sub Calc_MI()
    If Sheets("Main").Range("D12").Value = "FHA" Then
    Sheets("Main").Range("D16").Value = 0.85
    Else
    
    If Sheets("Main").Range("D6").Value < 0.8001 Or Sheets("Main").Range("D12").Value = "VA" Then
    Sheets("Main").Range("D16").Value = ""
    Else
    If Sheets("Main").Range("G14").Value > 0.45 Then
       Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + Sheets("Closing Costs").Range("BP101").Value + Sheets("Closing Costs").Range("BP102").Value)
    Else
       Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + Sheets("Closing Costs").Range("BP102").Value)
    End If
    End If
    End If
    End Sub

    Any help would be greatly appreciated :-)


    MEC

    Sunday, October 7, 2018 4:56 AM

All replies

  • You mention that the routines are called from Worksheet_Change when cell D5 is changed.

    You could do something like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("D5"), Target) Is Nothing Then
            If Range("D5").Value <> "" Then
                Application.EnableEvents = False
                ' Do stuff
                …
                Application.EnableEvents = True
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 7, 2018 8:48 AM
  • Thank you Hans for the reply

    This code did not work, it showed me the message box but I still got the same error and it did not revert to previous value. I actually have a working code from the StackOverflow forum, here it is:

    Option Explicit
    
    Dim oldVal As Variant
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$D$5" Then
            If IsEmpty(Target) Then
                MsgBox "Sales Price cannot be blank"
                Application.EnableEvents = False ' disable events to prevent change event fire in an infinite loop
                Target.Value = oldVal ' restore old backup value
                Application.EnableEvents = True ' enable events back
            End If
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Address = "$D$5" Then oldVal = Target.Value ' if D5 selected then backup its value
    End Sub


    MEC

    Sunday, October 7, 2018 1:01 PM
  • Hi mecerrato,

    It works fine in my  machine. However, My environment is Office2016, 32-bit. Could you provide your information of Office?

    Best Regards,

    Bruce



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, October 16, 2018 10:58 AM
    Moderator
  • Hi mecerrato,

    I can see that this thread is still open and you did not posted on this thread since last 10 days.
    is your issue is solved now?

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 29, 2018 10:28 AM
    Moderator