I want a macro that will not mess up if rows are deleted...

moore.1671 · Sep 17th 2013

I am trying to create macro to find specific text "Prior Month Premium" and then check cell to the right to see if left empty and if so then Msgbox the user to enter missing info. Here's what I got so far which is specific to certain cells being populated...

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (Sheets("Statement").Range("h84").Value = "") Then
        MsgBox "Please enter a Prior Month Premium"
        Cancel = True
    End If
    Dim c As Range
    For Each c In Sheets("Statement").Range("j86")
            If Not IsEmpty(c) And (Sheets("Statement").Range("j86") = "adjustment") Then
                If (Sheets("Statement").Range("c82") = "") Then
                        MsgBox "Please explain the difference between prior and current month premiums before saving." & Chr(13)
                        Cancel = True
                End If
        End If
    Next c
 
End Sub

Display More

patel · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

can you attach a sample file for testing ?

HaHoBe · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

Hi, moore.1671,

please wrap your cod ein tags.

Maybe your code could be simplified to

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("statement")
  If .Range("H84").Value = "" Then
    MsgBox "Please enter a Prior Month Premium"
    Cancel = True
  End If
  If .Range("J86").Value = "adjustment" And .Range("C82").Value = "" Then
    MsgBox "Please explain the difference between prior and current month premiums before saving."
    Cancel = True
  End If
End With
End Sub

Display More

because I doubt a loop through a single cell will not really be help.

Ciao,
Holger

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

forum.ozgrid.com/index.php?attachment/56405/

HaHoBe · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

Hi, moore.1671,,

no need to quote the full post.

The code does not go behind Sheet("Statement") but goes into ThisWorkbook in order for the event to get fired.

You should copy the code from here and paste it into ThisWorbook as the code you used should raise an exception.

Ciao,
Holger

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

Thank you soo soo soo much... you are awesome :rock:

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

One more thing. now that the macro is searching for specific text how do i get it to stop once the cell to the right has a value and is no longer empty?

I want a macro that will not mess up if rows are deleted...

Participate now!

Share

Users Viewing This Thread