Hey Steven,
Unfortunately attachements as you have guessed.... are stripped. If you want a workbook uploaded to the files area though... do send it to a moderator and she/he will check it and upload it for people to have a look at.
HTH
Lisa
Sent: Mon, Nov 5, 2012 7:18 pm
Subject: RE: [ExcelVBA] If Filename conains the ProjectName then run code
Hi Paul, I'm pretty new to VBA and have been building everything together just
based on the functions I needed and tried to sting them together in a sort of
logical steps.
The file process is follow:
File is opened from a excel macro enabled template (File name is
"NBT_Connectivity Cost Estimate")
When a user creates an estimate they will click on the save estimate button to
save the file instead of selecting File --> save (The reason I do this I can't
force them to use the file the project name and to protect the file before
sending it out.
My code is overly simplistic everything pasted together, so I'm not sure how to
integrate your code which is much more advanced. I could not even assign it to
the button. I'm not sure if you are able to accept attachments but this is the
module 1. I won't even try to show how I tried to copy your recommendation into
the code
Sub CCTDOCSAVE()
'
' CCTDOCSAVE Macro
'
If ActiveSheet.ProtectContents = True Then
MsgBox "Contact your Quote team for changes"
Else
If ActiveWorkbook.Name = "NBT_Connectivity Cost Estimate1" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
Application.Run "DoCPreP"
Application.Run "SaveAS"
End If
End If
End Sub
Sub SaveAS()
'
' SaveAS Macro
'
Dim ProjectName As String
Dim CurVer As String
Dim WbP As String
WbP = ActiveWorkbook.Path
ProjectName = Cells(5, 6)
CurVer = Range("I5")
'
ActiveWorkbook.SaveAS Filename:=ProjectName & "_ Estimate" & "_" & CurVer,
FileFormat:=52
End Sub
Sub DoCPreP()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
For Each ws In wb.Worksheets
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="password"
Next ws
Application.ScreenUpdating = True
Set wb = Nothing
Set ws = Nothing
End Sub
Steven
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar