I am writing some VBA code for an excel spreadsheet that includes a button on
the sheet that forces a SaveAs with a incremental version # and such. That
all works great.

Now I want to prevent the user from saving from the tool bar or file menu. I
wanted the Button Click value to be visible from the “Workbook_BeforeSave”
sub but it seems that my variables are not that public….. Ideas? If I could
get values into the Workbook_BeforeSave sub it would be a “No Brainer”



Dim Today As Date
Dim DateText As Double
Dim FileText As String
Dim Final As String
Dim Rev As Single
Dim ProjName As String
Dim FileNameAndLocal As Variant
Public BttnClick As Boolean

Public Sub CommandButton1_Click()
BttnClick = True
RenameNRev
SaveAsWhatEver

End Sub


Public Sub RenameNRev()

Worksheets("InstrumentIndex").Activate

Range("d2").Select

FileText = ActiveCell & "_"


Today = Date + Time
DateText = Today


Range("k2").Select
Rev = ActiveCell
Rev = Rev + 0.1
ActiveCell = Rev
Range("k3").Select
ActiveCell = Today
Rev = Round(Rev, 1)
Final = FileText & Rev & ".xls"


End Sub


Public Sub SaveAsWhatEver()
FileNameAndLocal = Application.GetSaveAsFilename(Final)

If FileNameAndLocal = False Then
MsgBox "The file was not saved", vbCritical, "The file was not saved"
End If

If BttnClick = True Then
If FileNameAndLocal = False Then
Range("k2").Select
Rev = ActiveCell
Rev = Rev - 0.1
ActiveCell = Rev
Else
ActiveWorkbook.SaveAs (FileNameAndLocal), FileFormat:=xlNormal

End If
End If

End Sub