If you know the username that the two users log into the system with, then
possibly this is a solution. This code would go into the worksheet's code
module. This code will only allow a specific username to enter anything in
column C, and will keep that same user from entering anything into any other
cell on the sheet. It isn't perfect - doesn't take into consideration
selecting multiple cells that may or may not include the 'protected' column.
To test this out, choose the sheet with the 'special' column and right-click
on its name tab and choose [View Code] from the list of options. Cut and
paste the code below into that module - change the username as needed. The
username is what you/they use to log into Windows, not the password, but the
username.
Option Explicit
' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Private Declare Function GetUserName Lib _
"advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Private Function Get_Win_User_Name() As String
' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long
' Get the user name minus any
' trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
Get_Win_User_Name = Left(lpBuff, _
InStr(lpBuff, Chr(0)) - 1)
End Function
Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)
'assumes that only a user named John may
'make entries into column C (column #3)
'and
'that John may not make entries anywhere else
'on the worksheet
'
'the usernames must be typed into this code
'using ALL CAPS
'
Dim userName As String
userName = UCase(Get_Win_User_Name())
If userName = "JOHN" Then
If Target.Column = 3 Then ' in column C
Exit Sub ' let john make changes in C
Else
' force John into C
Range("C" & Target.Row).Select
End If
End If
'user is not JOHN, only he may make changes in C
If Target.Column = 3 Then
'force the selected cell to someplace else
Range("A" & Target.Row).Select
'if you want to explain things:
MsgBox _
"You are not permitted to make changes in cell " & _
Target.Address
End If
End Sub
