Matt
Below is the macro that you need. Note that this is a workbook macro
and, as such, it must be placed in the workbook module. To do this,
right-click on the Excel icon that is to the left of the word "File" in the
menu across the top of your worksheet, select "View Code", and paste this
macro into that module.
This macro is triggered when the user initiates the Save command.
As written, this macro will check cells A2 & A3 of the sheet "MySheet".
This macro checks for any content in both cells. If both cells are
occupied, the file will be saved. If either or both cells are empty, the
Save command is cancelled. In this case a message box will be displayed
advising the user that both cells have to be filled before the file can be
saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Application.CountA(Sheets("MySheet").Range("A2:A3")) < 2 Then
Cancel = True
MsgBox "Both A2 & A3 must be filled before this workbook can be
saved.", 16, "Save Cancelled"
End If
End Sub
Expand this message to avoid line wrap.
If you wish, send me direct via email a valid email address for you and I
will send you a small file with this macro properly placed. My email
address is ottokmnop@comcast.net. Remove the "nop" from this address. HTH
Otto
"Matt D Francis" <MattDFrancis@discussions.microsoft.com> wrote in message
news:573791D1-4C03-4DB4-B7B4-3057AB82736B@microsoft.com...
> Hi Otto.
>
> I think just not saving the worksheet would be sufficient. It maybe they
> don't have that exact data to hand at that moment, but it definitely needs
> to
> be there before the workbook is saved.
>
> I thought it would be coding, but was hoping it wasn't!
>
> Matt
>
> "Otto Moehrbach" wrote:
>
>> The big question here is what do you mean by "progress"? You can setup
>> code
>> to check if both cells are occupied when the user selects any cell other
>> than A2 & A3. Or if he tries to save the file. Or if he tries to close
>> the
>> file. In short, what do you want the user to NOT be able to do unless
>> those
>> two cells are occupied? HTH Otto
>> "Matt D Francis" <MattDFrancis@discussions.microsoft.com> wrote in
>> message
>> news:B2D8C94D-64FC-40BA-BF7E-FEC5CEEACBAD@microsoft.com...
>> > Hi,
>> >
>> > I need validation on a cell where if any value is entered in one cell
>> > the
>> > user MUST enter a value in an adjacent cell. This must be easier than
>> > I'm
>> > making it..
>> >
>> > e.g
>> >
>> > If they enter a number in say A2, a I need to ensure they can't
>> > progress
>> > without also entering a number in A3.
>> > Equally, if they enter a number in say A3, a I need to ensure they
>> > can't
>> > progress without also entering a number in A2.
>> >
>> >
>> > The numbers have logical relationship (i.e <=) , but are mutually
>> > exclusive,
>> > I can't have one without the other.
>> >
>> > Is this a custom formula in the Validation box?
>>
>>
>>