Tracking Changes - questions

[samiamiam]

FIRST:
I know you can do a single "password required" to edit a document, but i would like to have multiple users that I establish during document creation, that have a locked font color attribute to their edits, and then another user for read only. For example:
- UserName-01: read/write (font color RED)
- UserName-02: read/write (font color GREEN)
- UserName-03: read/write (font color BLUE)
- UserName-04: read only

This way, the user is required to "login" and therefore will prevent him from having to change font colors every time they need to make an edit.

If this is not possible, what is the easiest way to implement something like this so that users can be streamlined in opening and editing documents quickly, without fussing around with font colors themselves.

 

[Ruddles]

In VBA, Environ("USERNAME") returns the login name of the user currently logged in. Perhaps you could use the Workbook_Open event to set a PUBLIC variable to the appropriate colour depending on who opened the workbook and then use the Worksheet_Change event to set any changed cells to the colour stored in the PUBLIC variable.

 

[samiamiam]

In VBA, Environ("USERNAME") returns the login name of the user currently logged in. Perhaps you could use the Workbook_Open event to set a PUBLIC variable to the appropriate colour depending on who opened the workbook and then use the Worksheet_Change event to set any changed cells to the colour stored in the PUBLIC variable.

sounds good...but way over my head. any more info you can provide on how to do this?

 

[Ruddles]

Always test in a copy of your workbook. You first need to find the Visual Basic Editor in Excel. Press Alt-F11 to open the VBE, then press Ctrl-R to view the
Project Explorer.

My suggestion has three components: a piece of code which detects who is logged in, a place where a colour code is stored, and a piece of code which reacts to a cell being changed by applying the colour code to it.

First of all, in the Project Explorer, double-click ThisWorkbook and when the code window opens up, replace any code you find with this:-

Option Explicit
 
Private Sub Workbook_Open()
 
  Select Case LCase(Environ("USERNAME"))
      Case "[COLOR=red][B]tom[/B][/COLOR]"
          FORECOLOR = [COLOR=blue][B]3[/B][/COLOR][COLOR=lime]   [/COLOR][COLOR=black] ' red[/COLOR]
      Case "[COLOR=red][B]****[/B][/COLOR]"
          FORECOLOR = [COLOR=blue][B]4[/B][/COLOR][COLOR=lime] [/COLOR][COLOR=black]   ' green[/COLOR]
      Case "[COLOR=red][B]harry[/B][/COLOR]"
          FORECOLOR = [COLOR=blue][B]5[/B][COLOR=lime] [/COLOR][/COLOR][COLOR=black]   ' blue[/COLOR]
      Case Else
          FORECOLOR = [COLOR=blue][B]7[/B][/COLOR][COLOR=lime]  [/COLOR][COLOR=black]  ' pink[/COLOR]
  End Select
 
  ' temporary message during testing
  MsgBox UCase(Environ("USERNAME")) & " will be using colour " & FORECOLOR

End Sub

This will detect who's logged in when someone opens the workbook and save a colour code in a variable called FORECOLOR. Insert your users' names in place of the names in red - and yours as well - repeating the Case statement as many times as is necessary, and the colour codes in place of the values in blue, with the default one in the Case Else statement in the event that the user is not recognised.

Colour codes can be found here: http://www.fontstuff.com/casebook/colorpicker/colorpicker.htm.

Next double-click the name of your main worksheet - the one you want the colouring to apply to. When the code window opens up, replace any code you find with this:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim oCell As Range
  
  For Each oCell In Target
    oCell.Font.ColorIndex = FORECOLOR
  Next oCell
  
End Sub

This is going to do the colour changing.

Finally you need to create somewhere to actually store the colour code. From the menu, go Insert > Module, then double-click the name of the module when it appears - it will be called Module1. When the code window opens up, replace any code you find with this:-

Public FORECOLOR As Integer

You're now good to go. Save the workbook as an XLSM file and close the workbook. Re-open it and enable macros if prompted. (We can suppress that warning message later.) Check that you receive that message telling you which colour code was selected.

Now type something in to your worksheet. Does it change colour when you hit Enter?

 

[samiamiam]

wow...thank you so much. running into a bit of an issue though.

Some of the users are registering OK, and when they open the document, click "enable macros" and then a new popup shows "USERNAME will be using color #". Edits work perfectly with them getting correct colors.

However...some users, like myself, are not able to register the username correctly. Instead it keeps defaulting to the unknown Case Else, and displays "will be using color #". The popup doesnt say my username, and doesnt select the correct color # for my username.

I made sure it matches exactly to what is displayed in my Preferences/settings.
I tried changing the Preferences to different style usernames using titlecase, all lower case, using _ instead of space. Nothing is working to get my username registered.

Any ideas?

 

[samiamiam]

ok...after doing more research, it appears the problem (according to posts back in 2008) is that the Environ("USERNAME") function is not available to MAC users. Im not sure if this still applies to Office 2011 which is the version I'm running on my MAC right now, but the code you gave is still not working for me, while the 3 PC users i tested work perfectly.

i cant find any recent references to that function in Office 2011 for MAC, so I dont know.
Is there an alternate function to use for calling the username maybe?

 

[samiamiam]

sry for multi-posting...just wanted to update :

i got it working by using Application.UserName instead. tested it on XP, Vista, and Mac OSX, all working perfectly. Thanks alot for your help Ruddles.

BTW...is there a way to remove the warning message about macros?

 

[Ruddles]

BTW...is there a way to remove the warning message about macros?

Yes - but I'm sure you can work that out for yourself!