VBA – 13. Events in VBA

workshet_change

Change the text to upper case in Column B whenver you type in Column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim our_range As Range

'intersect method ensures that the range we will change has to be from Range A2:A6


Set our_range = Application.Intersect(Target, Range("A2:A6"))


'if users selects any other range show a message

If our_range Is Nothing Then
MsgBox "select only in Range A2:A16"

Exit Sub

Else

'since whenver we make changes in any cell in WOrksheet, the Worksheet_change event is fired
'so if we makes changes it will again call worksheet_change event again and again.
' to stop this we must first set the events triggering to false

Application.EnableEvents = False


our_range.Offset(0, 1).Value = UCase(Target.Value)


'after changes are done, set back the evnts triggering to true
Application.EnableEvents = True

End If

worksheet_chng2

whenver a text is changed in column A, get the time stamp in column B

Private Sub Worksheet_Change(ByVal Target As Range)

Dim our_range As Range

Set our_range = Application.Intersect(Target, Range("A2:A11"))

If our_range Is Nothing Then
MsgBox "select Range in A2:A11 only"

Else


Application.EnableEvents = False
our_range.Offset(0, 1).Value = Now()

Application.EnableEvents = True

End If

Application.EnableEvents = False
If our_range = "" Then
our_range.Offset(0, 1) = ""
Application.EnableEvents = True
Exit Sub
End If

End Sub

Workbook Open Event

workbooko

 

 

 

 

Leave a Reply