View Full Version : Conditional Formating - Based on the Originator of Document?
Is is possible to set a conditional formating so that if someone other than the person listed in the properties as the Originator/Creator enters in data - it would change either color/font/both?
I have a document that is being provided using SharePoint - each person has the ability to 'check it' out - I had put a column for user initials - but it's hard to determine what they inserted (or tampered) with - I was wondering if it were possible to place a conditional format.
Thanks so much!
-- Jae
Simon Lloyd
04-23-2008, 01:21 PM
well i dont have the skills to check the document properties but...you could lodge you username in the workbooks open code and then check the user opening it:
Private Sub Workbook_Open()
If Environ(UserName) <> "MyName" Then
ThisWorkbook.KeepChangeHistory = True
ThisWorkbook.ListChangesOnNewSheet = True
End Sub
the above are just some options, the MyName would be your network username.
Just so I understand -
This will look at the "UserName" (My network name) and compare it to the person who has the document open - and if it's not me - which would be 'true' (?) keep the change history...
and put those changes on a new sheet - correct?
-- Thanks so much for your response!
-- Jae
Simon Lloyd
04-23-2008, 02:14 PM
If Environ(UserName) <> "MyName" Then says if the user opening the workbook is not me then...do the below, i haven't tested it, you can by :
Private Sub Workbook_Open()
Dim MyName As String
MyName = "Fred"
If Environ(UserName) <> MyName Then
ThisWorkbook.KeepChangeHistory = True
ThisWorkbook.ListChangesOnNewSheet = True
End If
End Sub
if you look i have now set a variable called myname as a string so removed the quotes from the original, i have set the variable to Fred, add the code to a test workbook (Alt+F11, then double click ThisWorkbook and paste it in), save and close the workbook reopen it make some changes then save and close the workbook (this can be automated) re-open and see what you have!
Bob Phillips
04-23-2008, 02:55 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If DocProps("author") <> Environ("Username") Then
With Target
.Interior.ColorIndex = 35
End With
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
'-----------------------------------------------------------------
Private Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Simon Lloyd
04-23-2008, 02:58 PM
Thanks Bob another lesson learned!
Thank you both so much!
I am going to have to play with these to check/learn the results.
I know I can 'break it' so it doesn't work - just to see how it does will be the test...
Again, thank you so very much for your time!
-- Sincerely
-- Jae
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.