Thanks Ron,
I really appreciate the offer. It is a quote module for my company, I
will show you the code here, and if you are not able to puzzle it out
from there, I will email the file to you.
It has a whole lot of password protection on it, confidential
information, and it's really big. So, I am sure you can see why I am
reticent about sending it.
Here is the code:
Code:
--------------------
Private Sub CommandButton2_Click()
'Makes the quote email ready
'
'
'Prep 1 - Declares the variables for the file name to be used in the "saved as" function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String
'
'Prep 2 - Defines the variable names from Quote Form
QNum = Range("X10").Text
CNam = Range("O19").Text
CrDt = Format(Now, "mmddyy")
VNum = Range("AB10").Text
'
'Step 1 - Save, and hide all non-essential sheets
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.SelectedSheets.Visible = False
'
'Step 2 - Locks all cells, hides user notes, and re-hides the column and row headings
ActiveWindow.DisplayHeadings = True
ActiveSheet.Unprotect Password:="STLMOB@900"
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Columns("AD:AI").Select
Range("AI1").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.DisplayHeadings = False
'
'Step 3 - Protects the worksheet and workbook
ActiveSheet.Protect Password:="STLMOB@900", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
ActiveWorkbook.Protect Password:="STLMOB@900", Structu=True, Windows:=False
'
'Step 4 - Saves the protected file with a unique name, and makes it read-only recommended
ActiveWorkbook.SaveAs Filename:= _
"X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" _
& QNum & "-" & CNam & "-" & CrDt & " _ver " & VNum & " - EMAIL" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="STLMOB@900", _
ReadOnlyRecommended:=True, CreateBackup:=False
'
'Step 5 - Displays a message informing the user that the protection is complete
MsgBox "Your quote has been successfully protected." & _
Chr(13) & "It is now safe to email.", 64, "Security Check Complete!"
End Sub
--------------------
--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=500357