Macro if Specific Cell Value is Updated

[hmltnangel]

Just being silly, but is this code right, or is there a better way of writing it? I want it to check if userid input in cell I2 meets the conditions, then run a macro if it does/msg box if it doesnt match. I only want it to run if i2 is changed.

Sub worksheet_change(ByVal target As Range)
Set target = Range("I2")
If target.Value = Environ$("UserName") Then
 Call Macro1 'add macroname
ElseIf target.Value = "1234" Then
 Call Macro1 'add macroname
ElseIf target.Value = "2345" Then
 Call Macro1 'add macroname
ElseIf target.Value = "3456" Then
 Call Macro1 'add macroname
ElseIf target.Value = "4567" Then
 Call Macro1 'add macroname
ElseIf target.Value <> Environ$("UserName") Then
    MsgBox "UserID does not match Windows Login"
End If
End Sub

 

[Joe4]

Apologies Joe, I should be way more specific.

It doesnt produce the expected results - not an "error" as such. The code you just provided gives a msg box as follows: (note - names actually appear instead of numbers as im just using a number value for the names on here)

I2 Value: 0123
Username: 1234

Well, that seems to suggest that it is working exactly as you asked, as the value in I2 does not seem to match either the Username, or any of the other values are you are checking for.
"0123" does not match either of the things you are checking.
So it SHOULD be returning the message box without calling your "Autofiltercolumns" macro.

 

[hmltnangel]

Not quite,

1. If Environ Username matches the input in I2 then yes, I want it to call the autofiltercolumns macro.
2. If the Environ Username matches one of the 4 specified usernames in the code - I also want it to call the macro (regardless of whats in I2)
3. If it doesnt work using either instances 1 or 2 then yes I want the msg box to pop up

Its number two that I cant get to work.

 

[Joe4]

The example you posted does not meet either of the first two conditions ("0123" is NOT one the conditions you listed!).

Try updating the value in I2 to match one of the values in the list in the code ("1234", "2345", "3456", or "4567"), then run my latest version of the code, and tells me what the MsgBox returns if it still does not work.

 

[hmltnangel]

Sorry Joe, bit of confusion I think. Again probably my poor explanations.

In instance 2. If the Environ Username matches either of the 4 usernames in the code(1234/2345/3456/4567) then it should call the Macro. Regardless of whats in Cell I2. Rightly the code you gave specifies that my Environ Username is indeed 1234. This definitely doesnt match I2. But thats fine - because its one of the four specified userids. In this instance I want it to call the macro. Disregarding whats in I2.

 

[Joe4]

OK, I think I see what you are after now.

Try this:

Sub LineMan()

    Dim inp As String
    Dim userName As String
    
'   Get value from cell I2
    inp = Range("I2").Value
'   Get userName from Environ
    userName = Environ$("UserName")
    
    If userName = inp Then
        Call Autofiltercolumns
    Else
        Select Case userName
            Case "1234", "2345", "3456", "4567"
                Call Autofiltercolumns
            Case Else
                MsgBox "UserID does not match Windows Login"
        End Select
    End If
   
End Sub

 

[hmltnangel]

Joe, that is bang on. Thanks

Works brilliant!

Silly thing is - I understand what youve done, I just cant write it :O

 

[Joe4]

Joe, that is bang on. Thanks

Works brilliant!

You are welcome.

Silly thing is - I understand what youve done, I just cant write it :O

If you start seeing enough of them, eventually it will all come together and you will be able to start to figure out how to do it.

You original code was actually very close, you were just checking the wrong thing, i.e.

ElseIf target.Value = "1234" Then

this is checking to see if the value in cell I2 is "1234", when you really wanted to check the username, i.e.

ElseIf Environ$("UserName") = "1234" Then

So if you update all the "ElseIf" statements like that, it probably would have worked.