Solved

VBA to prevent save if conditional flag exists in column i15 - i84

Posted on 2014-09-18
4
223 Views
Last Modified: 2014-09-18
This is what I have so far. Seems simple but can not figure out the range so the code scans column i15 - i84 for a value. If value is detected then a message comes up and save is not allowed until flag is removed. Currently only works for i15. Have tried $i15 thinking that would cover the entire column with no luck.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheets("Form").Range("i15").Value = "l" Then
  MsgBox "Mandatory Field Alert!! You must complete 'Employee Needing Coverage' field before save can be performed, vbInformation"
 Cancel = True
End If
End Sub


Image included to show layout of form and flag
image.jpg
0
Comment
Question by:Walter Williams
  • 2
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40331444
    Dim rngFound As Range
    Range("I15:I84").Select
    Set rngFound = Selection.Find(What:="l", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False)
    If Not rngFound Is Nothing Then
        MsgBox "Mandatory Field Alert!! You must complete 'Employee Needing Coverage' field before save can be performed, vbInformation"
    End If
 Cancel = True

Open in new window

0
 

Author Comment

by:Walter Williams
ID: 40331561
MartinLiss,

That did it, works perfectly.. Thank you..
0
 

Author Closing Comment

by:Walter Williams
ID: 40331563
Very quick response...  thank you again, worked perfectly...
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40331575
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question