How do I Run Macro on Opening if user is not the "Last Saved By" U

[MikeZz]

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

 

[Gary''s Student]

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

 

[Bob Phillips]

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)

 

[Steve Yandl]

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]

You are correct...The user will not become the author until the file is saved.

Thanks for catching this error.

 

[MikeZz]

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!

 

[MikeZz]

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]

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

 

[Bob Phillips]

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)