Hi OssieMac

Thankyou for your constructive critisism, I am new to VBA and only starting
to put things together so thanks for the advice on some aspects. Can you
help me with a piece of code to put in place of "Do something if answer is
yes"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Do you want to save as 'Week Comm " & Range("N2") & _
"'", vbYesNo + vbDefaultButton2 + vbQuestion, "File Save") = vbYes Then
'Do something if answer is yes
End If
End Sub

I am looking for a command to put in the code to replace: 'Do something if
answer is yes, I want this to save a file as the new name e.g Resources WC
and the range N2 on the desktop, if no is selected I want the workbook to
close and make no changes, can you help with this code.

Regards


Mark


"OssieMac" wrote:

Hello Mark,

Firstly a little constructive criticism of your code.

This is a Workbook_SheetSelectionChange event and you do not test for the
sheet name. This code will run whenever Range S2 is selected on any worksheet
and not only when changing the selection on the required sheet.

You can either place the code in the module for the specific worksheet and
use
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

or you should have something like this in your code to identify which sheet
has the selection change:-
If Sh.CodeName < "Sheet1" Then Exit Sub

You can get the CodeName from the Project Explorer (Left column of the VBA
editor screen.) The Code name is first and the worksheet given name is in
parenthesis. If the user changes the given name then the CodeName does not
change and is therefore safer to use in the VBA code.

Secondly what do you want to do if the user answers No the question €œAre you
wanting to save as Week Comm 06 July 2009?€�
At this point I suggest that you need to allow the user to:-
Abort the Save or Close and keep file open.
Save as existing file name.
Save as a new filename given by the user.
Close without saving.

Reason to allow Close without saving is that the user might have messed up
and wants to start again. For this reason it is dangerous not to allow a user
to close without saving.

--
Regards,

OssieMac