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

 

[hmltnangel]

Edit, that code does work, but I did make a bit of a mess of it. I need to alter it so that the following happens:

If the UserID in I2 matches Environ Username, then it calls Macro1
If The Environ Username matches 1234,2345,3456,4567 then call Macro1
Otherwise - Msg box - Doesnt match.

 

[hmltnangel]

Actually Got it working

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

 

[hmltnangel]

But I have one snag, any suggestions?

How to stop the msg box coming up if the call macro part runs ok? Basically I only want the msg box to pop up if the final part is true

 

[Joe4]

If you want it to run automatically, you need to keep it as a "Worksheet_Change" event procedure.

Also, a "Case" statement is much neater than a bunch of ElseIf statements.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "I2" Then
        If Target.Value = Environ$("UserName") Then
            Call Autofiltercolumns
        Else
            Select Case Target.Value
                Case "1234", "2345", "3456", "4567"
                    Call Autofiltercolumns
                Case Else
                    MsgBox "UserID does not match Windows Login"
            End Select
        End If
    End If
   
End Sub

 

[hmltnangel]

Thanks Joe, I need it to only run when a button is clicked. So I can assign the macro to a button really easy, but I dont want the auto run anymore. How do I amend that? I tried to change it and remove the target.address lines but it doesnt work

 

[Joe4]

Try this then:

Sub LineMan()

    Dim userName As String
    
    userName = Range("I2").Value
    
    If userName = Environ$("UserName") 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]

Still got an error: Even with one of the userids selected it just pops up the msg box.

Basically, all I want is for it to

Carry out the Autofilter if the username matches the windows username or if the username matches one of the four additional usernames. But if the username doesnt match the windows username or the four additional ones, dont run the autofilter, and pop up a msg box.

 

[Joe4]

Still got an error: Even with one of the userids selected it just pops up the msg box.

Basically, all I want is for it to

Carry out the Autofilter if the username matches the windows username or if the username matches one of the four additional usernames. But if the username doesnt match the windows username or the four additional ones, dont run the autofilter, and pop up a msg box.

When you say you "still got an error", are you actually getting an error, or just unexpected results?
If an error, please provide the exact error code and message.

If it is not producing the expected results, run this code and tell me exactly what the first message box returns:

Sub LineMan()

    Dim userName As String
    
    MsgBox "I2 Value: " & Range("I2").Value & vbCrLf & _
            "UserName: " & Environ$("UserName")
    
    userName = Range("I2").Value
    
    If userName = Environ$("UserName") 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]

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