Refining VBA code

Agnarr

New Member
Joined
Jan 15, 2023
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello everybody and you all do an amazing work.
I need your help please and i apologize in advance for any mistakes (English is not my native language).

So, I have made the following:
In B column appears a timestamp when in column C somone inputs a code which automatically reaplaces it with a given name and in column D is just for notes.
E.g.: in c1 i ender 3232 and press enter. Automatically 3232 turns to "3232 - Mr. Smith" and in b1 appears "29/01 12:05".
My problem is that I want to copy and paste entries alongside the length of the sheet but keeping the same timestamp. Now every time I copy-paste entries, even if i copy the already given date, the VBA automatically changes the timestamp to the moment i paste the entry.

The Code i use it the following:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range, WRng As Range, rg As Range, zOffsetColumn As Integer, f

Set inputR = Range("C14:C208")
Set codeR = Worksheets("ÊÙÄ.ÐÅË.").Range("A1:B300")
Set WRng = Intersect(Application.ActiveSheet.Range("c14:c208"), Target)
zOffsetColumn = -1
If Not WRng Is Nothing Then
Application.EnableEvents = False
For Each rg In WRng
If Not VBA.IsEmpty(rg.Value) Then
rg.Offset(0, zOffsetColumn).Value = Now
rg.Offset(0, zOffsetColumn).NumberFormat = "dd/mm, hh:mm"
Else
rg.Offset(0, zOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, inputR) Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    For Each cell In Target
        Set f = codeR.Find(cell.Value, , , xlWhole)
        If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
    Next
    .EnableEvents = True
End With
End Sub

Thank you all in advance.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I think you either need to
a) validate that you want that code to alter cells. Could use msgbox and exit sub if answer is No, but might become annoying. Or
b) use a different event. Your code will run every time you change a cell on that sheet. Perhaps a button click instead?
 
Upvote 0
I think you either need to
a) validate that you want that code to alter cells. Could use msgbox and exit sub if answer is No, but might become annoying. Or
b) use a different event. Your code will run every time you change a cell on that sheet. Perhaps a button click instead?
Yeah, msgbox will be annoying. The timestamp cell changes only when the range("c14:c208") is edited.
What other event could i use? Do you have any recomendation?
 
Upvote 0
Button click? Call macro from ribbon?
What about these other paste operations? If they are in a row that has the time stamp, then cancel the code else let it run?
Is there a way to run/not run based on the Target.Address that you pasted into?
How you are working in the sheet is obvious to you but unfortunately not me so I can only make guesses.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top