Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved By" U
Subject says it all.
Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved By" U
If you have a Workbook Open Event macro, include lines like:
i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved By" U
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean) ThisWorkbook.Names.Add Name:="_SavedBy", RefersTo:=Environ("Username") End Sub Private Sub Workbook_Open() Dim mpName As String On Error Resume Next mpName = ActiveSheet.Evaluate(ThisWorkbook.Names("_SavedBy" ).RefersTo) On Error GoTo 0 If mpName < Environ("Username") Then Call myMacaro End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MikeZz" wrote in message ... Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved By" U
Gary's Student,
I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved B
You are correct...The user will not become the author until the file is saved.
Thanks for catching this error. -- Gary''s Student - gsnu200745 "Steve Yandl" wrote: Gary's Student, I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved B
Man you guys are GODS!
I can think of a million things to do with this! Save a person's last view and retrieve it when they open it next.... Have a special pop-up message if the boss logs in, Ideas are endless.... THANKS! "Gary''s Student" wrote: You are correct...The user will not become the author until the file is saved. Thanks for catching this error. -- Gary''s Student - gsnu200745 "Steve Yandl" wrote: Gary's Student, I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved B
Hi Steve, Thanks for the help...
But what's difference between A & B? Also, never heard of Wscript.Network but googled it.... Is there any benefit to using either of the following? A: strUser = Application.UserName B: Set objNetwork = CreateObject("Wscript.Network") B: strUser = objNetwork.UserName B: Set objNetwork = Nothing "Steve Yandl" wrote: Gary's Student, I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved B
Mike,
On my home computers, there is no difference between the two options. However, suppose you have Excel installed on a PC that is used by multiple users who each log in under their own ID. In that case, Option B is going to reveal the name of the user currently logged in. Option A will show the name of the person that was entered as the user when Excel was installed on that PC which may or may not be the currently logged on user. The "WScript.Network" object is familiar to those who work with scripts running under the Windows Script Host, typically system administrators creating vbs files to manage PCs on a network. Along with "Wscript.Shell", "Scripting.FileSystemObject" and "Scripting.Dictionary" it provides a small set of tools not built in to VBA. Steve "MikeZz" wrote in message ... Hi Steve, Thanks for the help... But what's difference between A & B? Also, never heard of Wscript.Network but googled it.... Is there any benefit to using either of the following? A: strUser = Application.UserName B: Set objNetwork = CreateObject("Wscript.Network") B: strUser = objNetwork.UserName B: Set objNetwork = Nothing "Steve Yandl" wrote: Gary's Student, I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Run Macro on Opening if user is not the "Last Saved B
Also, some companies don't allow scripting, so the second option will not
work. You could just use strUser = Environ("UserName") though -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steve Yandl" wrote in message . .. Mike, On my home computers, there is no difference between the two options. However, suppose you have Excel installed on a PC that is used by multiple users who each log in under their own ID. In that case, Option B is going to reveal the name of the user currently logged in. Option A will show the name of the person that was entered as the user when Excel was installed on that PC which may or may not be the currently logged on user. The "WScript.Network" object is familiar to those who work with scripts running under the Windows Script Host, typically system administrators creating vbs files to manage PCs on a network. Along with "Wscript.Shell", "Scripting.FileSystemObject" and "Scripting.Dictionary" it provides a small set of tools not built in to VBA. Steve "MikeZz" wrote in message ... Hi Steve, Thanks for the help... But what's difference between A & B? Also, never heard of Wscript.Network but googled it.... Is there any benefit to using either of the following? A: strUser = Application.UserName B: Set objNetwork = CreateObject("Wscript.Network") B: strUser = objNetwork.UserName B: Set objNetwork = Nothing "Steve Yandl" wrote: Gary's Student, I was just doing some testing of this while you were posting. Your line for ActiveWorkbook.BuiltInDocumentProperties(7) works fine to retrieve the Last Author (last saved by) but item(3) to retrieve 'Author' came up null for me until the workbook was saved. I think it might work better to use strUser = Application.UserName to get the name of the user registered for the Excel application used to open the workbook, or _________________ Set objNetwork = CreateObject("Wscript.Network") strUser = objNetwork.UserName Set objNetwork = Nothing __________________ if you want the currently logged on user. Steve "Gary''s Student" wrote in message ... If you have a Workbook Open Event macro, include lines like: i = ActiveWorkbook.BuiltinDocumentProperties(3) j = ActiveWorkbook.BuiltinDocumentProperties(7) If i = j Then Else 'run your first time code End If -- Gary''s Student - gsnu200745 "MikeZz" wrote: Subject says it all. Basically, I want to run a macro that applies various filters if a new user opens the file. If it's the same user that saved the file before, I assume they don't want the view to change. Thanks, MikeZz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Document not saved" "error in loading DLL" | Excel Discussion (Misc queries) | |||
Stop "Book1" from opening from a saved file | Excel Discussion (Misc queries) | |||
User form closes after opening another one -- Problem with "Unload Me"? | Excel Programming | |||
Is there a "last saved on date/user" macro/function for Excel 2003 | Excel Discussion (Misc queries) | |||
Solution "Your changes could not be saved" "The document may be read-only or encrypted" | Excel Discussion (Misc queries) |