VBA worksheet change event

[hrayani]

Hello Friends after a long time,

I need a small VBA code
When I enter "humayun" in cell A1 then A1 value should change to "humayun rayani" automatically

Regards,

Humayun

 

[ranman256]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If LCase(Range("A1").Value) = "humayun" Then Range("A1").Value = "humayun rayani"
End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Address = Range("A1").Address Then
        If Target.Value = "humayun" Then
            Application.EnableEvents = False
            Target.Value = "humayun rayani"
            Application.EnableEvents = True
        End If
    End If
        
End Sub

 

[hrayani]

My Bad, for not putting up my question correctly

change A1 value to A1 value and "rayani"

if I enter Abdul it should be changed to "abdul rayani" as soon as i press enter
if i enter Humayun it should be chnaged to "humayun rayani" as soon as i press enter

 

[Joe4]

My Bad, for not putting up my question correctly

change A1 value to A1 value and "rayani"

if I enter Abdul it should be changed to "abdul rayani" as soon as i press enter
if i enter Humayun it should be chnaged to "humayun rayani" as soon as i press enter

Simple change:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Address = Range("A1").Address Then
        Application.EnableEvents = False
        Target.Value = Target.Value & " rayani"
        Application.EnableEvents = True
    End If
        
End Sub

 

[hrayani]

Simple change:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
    If Target.Address = Range("A1").Address Then
        Application.EnableEvents = False
        Target.Value = Target.Value & " rayani"
        Application.EnableEvents = True
    End If
       
End Sub

Thanks Joe
Works like a charm

 

[Joe4]

You are welcome.

 

[hrayani]

You are welcome.

hello, one more thing
what is wrong when i write the code like below
why it is not working

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

    Application.EnableEvents = False
    
    Range("A1").Value = Range("A1").Value & " Humyaun"
    
    Application.EnableEvents = True

        End If
End Sub

 

[Joe4]

hello, one more thing
what is wrong when i write the code like below
why it is not working

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

    Application.EnableEvents = False
   
    Range("A1").Value = Range("A1").Value & " Humyaun"
   
    Application.EnableEvents = True

        End If
End Sub

Nothing wrong with that code, it should work as long as:
- You have placed it in the proper sheet module
- Events are enabled

When you have code that enables/disables events, sometimes if you are testing you may accidentally disable it and not re-enable it. In that case, all events will stop working.
You can re-enable them by manually running this code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[hrayani]

Nothing wrong with that code, it should work as long as:
- You have placed it in the proper sheet module
- Events are enabled

When you have code that enables/disables events, sometimes if you are testing you may accidentally disable it and not re-enable it. In that case, all events will stop working.
You can re-enable them by manually running this code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

ok but when i press delete then the code stops working next time
so every time I have to run the enable events then worksheet change would work ?? is that so ??