Workbook_BeforeSave Head ache

[JTH]

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

 

[cush]

it seems that my variables are not that public….. <<

Replace the word "Dim" with "Public" to make them public.
This will allow these variables to maintain values after a sub runs.
Dimmed variable lose content at the close of a sub.

This should help you get started. Also, avoid selecting cells and ranges.
Just use something like:

Range("k2").Select
Rev = ActiveCell
Rev = Rev + 0.1
ActiveCell = Rev

Range("k2")=Range("k2")+0.1
Rev = Range("k2")

 

[GB]

Part of the problem is that you never reset the bttnclick variable, so it is
always true after being set the one time. As for controlling the users
actions in regards to the save button, I have found it possible to insert and
remove the save button from the default menu. The important part though, is
that you put it back. Basically you could record a macro of you editing the
menu bars, and see how it works. Ultimately what I would do in your
situation, is remove the save button, then add your own save button to the
list, that performs the programmed actions you want. Anytime you leave that
workbook, reverse the process, and prior to exiting the application also
reverse the process.

Might not be the best, but it worked for me.