We can take this discussion "offline" to keep from disturbing others.
Then we can bring it back when we get close..
I'll email you directly.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: "steven.eckley@accenture.com" <steven.eckley@accenture.com>
To: ExcelVBA@yahoogroups.com
Sent: Mon, November 5, 2012 1:21:11 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
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of
Paul Schreiner
Sent: 05 November 2012 3:22 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] If Filename conains the ProjectName then run code
What is it that is "not working"?
I noticed that your macro was "Sub SaveAS() "
Not a BeforeSave event macro...
Are you familiar with Event macros?
Is that what you really want to do?
That is: monitor when a user tries to save the template
and force the user to save it as a separate project file.
Even if you want to have the user use your SaveAs sub,
you can still use the BeforeSave event to check to see
if the file is the template file and abort saving.
I check the login id and, if it is my own id, or one
other admin, I allow the template to be saved.
Otherwise, I issue a reprimand and tell the user to
quit trying to mess with my stuff and go play with his own file.
let me know if I can be of assistance.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: "steven.eckley@accenture.com<mailto:steven.eckley%40accenture.com>"
<steven.eckley@accenture.com<mailto:steven.eckley%40accenture.com>>
To: ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>
Sent: Mon, November 5, 2012 10:06:00 AM
Subject: RE: [ExcelVBA] If Filename conains the ProjectName then run code
Thanks Paul, on my initial attempts I did not get it working but going to try to
figure it out and get back to you.
Thanks for your help
Regards,
Steven
From: ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>] On Behalf
Of
Paul Schreiner
Sent: 05 November 2012 2:01 PM
To: ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>
Subject: Re: [ExcelVBA] If Filename conains the ProjectName then run code
It sounds like you have the user start out by opening the template.
Then, when the user saves the file, you want the file saved as: ProjectName &
"_Estimate"
Once it has been saved with the ProjectName, then subsequent "saves" would
proceed normally.
Given what you already have, I would move the macro to a BeforeSave Event macro.
then use:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
WbP = ActiveWorkbook.Path
ProjectName = sheets(1).Cells(5, 6) '(include sheet name, to ensure proper
sheet is used)
If (InStr(1, ActiveWorkbook.Name, ProjectName) <= 0) Then
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
WbP = .SelectedItems(1)
End If
End With
If (Right(WbP, 1) <> "\") Then WbP = WbP & "\"
Application.Enableevents = false
'This will ensure that the "BeforeSave" event macro isn't called repeatedly.
'----------------------------------------------------------
ActiveWorkbook.SaveAs filename:=WbP & ProjectName & "_Estimate",
FileFormat:=52
'----------------------------------------------------------
Application.Enableevents = true
Cancel = true 'This keeps the file from being saved twice
End If
End Sub
Things to note here are:
Since this macro is triggered when the user hits "Save" or "SaveAs",
then if the Project Name is ADDED, then the macro does a "SaveAs" with the new
name,
FORCING the file format to .xlsm. (format 52)
Once the macro runs, then the "Save" that the user issued would normally run,
causing the file to be saved twice.
To keep this from happening, use "Cancel = true" to "cancel" the user-issued
Save.
Since this is an event macro, the Application.SaveAs function will actually
cause the
BeforeSave Event to be triggered.
To keep this from happening, use Application.EnableEvents = false
(be sure to turn it back on)
Note: If the user decides to save the TEMPLATE as a different format other than
.xlsm,
then this file will override the users choice.
Say the user opens the template and enters a ProjectName.
He then decides to save it as an Excel2003 format by using SaveAs.
This event macro will execute,
notice that the template name does not contain the Project Name,
save the file with the Project Name as a .xlsm file,
then cancel the user's request to save as Excel2003.
Now, once this file is saved with the Project Name,
THEN when the user issues a SaveAs, since the file contains the project name,
the user's request will proceed as.. er.. requested.
I just wanted to let you know that this is EXPECTED, not a "bug"..
If it's an issue, I'm sure we could figure out an alternative method.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: steven.eckley
<steven.eckley@accenture.com<mailto:steven.eckley%40accenture.com><mailto:steven.eckley%40accenture.com>>
To:
ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com><mailto:ExcelVBA%40yahoogroups.com>
Sent: Sun, November 4, 2012 7:29:35 AM
Subject: [ExcelVBA] If Filename conains the ProjectName then run code
I have created a excel template which has a button that i click to save
automatically safe the file with a specific file name to the active directory.
Before the file is saved I'd like to check if the filename contains the
ProjectName, if it does it should continue to save the file, if not I'd like to
be prompted where to save the file to. If current save as macro is below. Any
assistance will be much appreciated.
Sub SaveAS()
' SaveAS Macro
'
Dim ProjectName As String
WbP = ActiveWorkbook.Path
ProjectName = Cells(5, 6)
'
ActiveWorkbook.SaveAS Filename:=ProjectName & "_Estimate", FileFormat:=52
End Sub
[Non-text portions of this message have been removed]
________________________________
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in error,
please notify the sender immediately and delete the original. Any other use of
the e-mail by you is prohibited.
Where allowed by local law, electronic communications with Accenture and its
affiliates, including e-mail and instant messaging (including content), may be
scanned by our systems for the purposes of information security and assessment
of internal compliance with Accenture policy.
__________________________________________________________
www.accenture.com<http://www.accenture.com>
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
________________________________
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in error,
please notify the sender immediately and delete the original. Any other use of
the e-mail by you is prohibited.
Where allowed by local law, electronic communications with Accenture and its
affiliates, including e-mail and instant messaging (including content), may be
scanned by our systems for the purposes of information security and assessment
of internal compliance with Accenture policy.
__________________________________________________________
www.accenture.com
[Non-text portions of this message have been removed]
[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 (7) |
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