Start Macro when specific Cells are selected

[Mik]

I have a range of cells b3:b5 which show UserNames of people who will
use the spreadsheet.
The adjacent cells c3:c5 allow a manual input from the user.
The user must enter the info in their specific adjacent cell only.

So, when any of these cells c3:c5 are selected, i wish to run a macro
(preferably before user input) which will check to see that the
current user is entering info within the correct cell.
I determine the user with the Environ("username") function.

For info, i am using Excel 2007.

My code at present is:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$c$3" Then
If Range("b3").Value = Environ("username") Then 'allow entry
Exit Sub
Else
MsgBox ("Wrong User Input!")
End If
Else
'code to repeat for $c$4, and $c$5 etc...
End If
End Sub

 

[Gary''s Student]

This works, but it still is not very good:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, s As String
Set r = Range("C3:C5")
s = Environ("username")
If Intersect(Target, r) Is Nothing Then Exit Sub
If Target.Offset(0, -1).Value = s Then Exit Sub
Application.EnableEvents = False
Target.Clear
Application.EnableEvents = True
MsgBox "Wrong User Input"
End Sub

The problem is the
Target.Clear
It destroys an pre-set value. An alternative approach is to have a workbook
open macro that protects C3 thru C5 and then unprotects a single cell based
on username.

 

[Mik]

I have a range of cells b3:b5 which show UserNames of people who will
use the spreadsheet.
The adjacent cells c3:c5 allow a manual input from the user.
The user must enter the info in their specific adjacent cell only.

So, when any of these cells c3:c5 are selected, i wish to run a macro
(preferably before user input) which will check to see that the
current user is entering info within the correct cell.
I determine the user with the Environ("username") function.

For info, i am using Excel 2007.

My code at present is:-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$c$3" Then
        If Range("b3").Value = Environ("username") Then 'allow entry
            Exit Sub
        Else
            MsgBox ("Wrong User Input!")
        End If
    Else
'code to repeat for $c$4, and $c$5 etc...
    End If
End Sub

Sorry,

When I look back at my previous entry it does not read very clear.

Basically, can anybody advise me how to Run a Macro when a specific
cell is selected, using Excel 2007?
Macro must run before text is entered into the cell.

Thanks in advance.

Mik

 

[Mik]

This works, but it still is not very good:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, s As String
Set r = Range("C3:C5")
s = Environ("username")
If Intersect(Target, r) Is Nothing Then Exit Sub
If Target.Offset(0, -1).Value = s Then Exit Sub
Application.EnableEvents = False
    Target.Clear
Application.EnableEvents = True
MsgBox "Wrong User Input"
End Sub

The problem is the
Target.Clear
It destroys an pre-set value.  An alternative approach is to have a workbook
open macro that protects C3 thru C5 and then unprotects a single cell based
on username.
--
Gary''s Student - gsnu200908










- Show quoted text -

Thanks for your assistance.
This works OK.

Would like to change it slightly if possible.....

The user can type in the cell, and only upon pressing Enter are they
advised that the cell does not allow their input.

Is it possible to prevent the user from entering text in the cell upon
selection prior to entering text?

 

[Gary''s Student]

The usual way is to prottect the cell or to use some form of data validation.

 

[Gord Dibben]

Substitute msgbox for your code or macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("C3,C4,C5")) Is Nothing Then Exit Sub
MsgBox Target.Address
End Sub


Gord Dibben MS Excel MVP

 

[Mik]

Substitute msgbox for your code or macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("C3,C4,C5")) Is Nothing Then Exit Sub
MsgBox Target.Address
End Sub

Gord Dibben  MS Excel MVP






- Show quoted text -

Thank you both very much for your assistance.
That did exactly what i wanted.

Mik