Static Date and Time and VBA editing

[MilleniumPro]

Hello all,

I have no idea how to write VB and have spent an extensive a mount of time
doing trial and error. The following code is what I currently use that does
the following:

It updates columns A and B to uppercase letters when a user enters a
lowercase one, and it updates certain columns with current date and time
that data was entered in the cell to the left. As of yesterday, I thought
this was working, however when I opened the sheet today, all of the times
and date updated to the time I opened the sheet. The idea is to have the
cells value never change once it is populated with the timestamp. Please
look at the code and provide any guidance as needed.

I would also like to know the following:

1.How can I modify the code to add or remove colums from the code for each
of the uppercasing or the timestamping?

2. When adding other functions in VBA, I find them by themselves online,
like the timestamp was something that was by itself, yet I already had the
code set up for the uppercase function. It took me forever of trial and
error to combine the 2 so that they would work. Please describe how to
combine code when 2 private subs are found that could be useful together on
a single worksheet.

Thanks in advance

 

[MilleniumPro]

Here is the code I forgot to paste:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Target is an arguments which refers to the range being changed
If Target.Column = 1 Or Target.Column = 2 Then
'this check is to prevent the procedure from repeatedly calling itself
If Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)


End If
If Target.Column = 4 Or Target.Column = 6 Then
Target.Offset(0, 1) = Now()
End If



End If
End Sub

 

[Shane Devenshire]

Hi,

First the most common way to control the range that is triggering the change
is to add these lines rather than Target.Columsn stuff. You should try to
specify the area that when changes results in the running of the macro, here
is an example with the range A1:B100 being the range being checked:

Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:B100"))
If Not isect Is Nothing Then
'Your code here
End If

Also to stop the code from triggering itself:

Application.EnableEvents = False
and then turn it back on later in the macro with
Application.EnableEvents = True

I suspect that you are making a change with multiple cells selected or that
you have function somewhere in your target area.