Vb Sourcing/Macros Freezing formulas after input

[Sammy1994]

Hello,

I've been trying to figure something out and currently I am required to use vb sourcing for a solution to my issue.

My issue is trying to freeze a formula which is =IF(A3="","",IF(B3="",NOW(),B3)) after someone places the input into A3.

I need it to freeze so if that user deletes what is in A3 it will not change the date and time, I'm trying to clock input times and secure it from modification. I am not very familiar with VB sourcing or macros, so please be detailed about the solution if there is one.


Thank you,

Sam

 

[Joe4]

Welcome to the Board!

Sounds like you want a Date/Time stamp. This question comes up a lot.
Here is one thread on the topic that shows you how to do it: https://www.mrexcel.com/forum/excel-questions/575734-timestamp-want-change-when-workbook-opened.html

 

[Sammy1994]

Welcome to the Board!

Sounds like you want a Date/Time stamp. This question comes up a lot.
Here is one thread on the topic that shows you how to do it: https://www.mrexcel.com/forum/excel-questions/575734-timestamp-want-change-when-workbook-opened.html

I'm still having trouble with this I understand placing the code in vb but it doesn't work! It needs a macro and I am super new to this kind of thing so I'd appreciate some explanation on how to get this to run. Thank you.

 

[Joe4]

For Excel purposes, macros and VBA are basically the same thing (the terms are used interchangeably).

In order for Event Procedure VBA code to work automatically (and that is what this code is), the following things MUST happen:

1. The VBA code must be put in the proper module. In this example, it needs to go in the Sheet module of the sheet that you want to apply this to.
The easiest way to get there is to go to the sheet you want to apply it to, right-click on the Sheet tab name at the bottom of the sheet, select View Code, and past the code in the resulting VB Editor window that pops up.

2. The Procedure MUST be named a certain way. The first line of the code, which in this case is:

Private Sub Worksheet_Change(ByVal Target As Range)

CANNOT be altered in any way, or else it will not automatically run.

3. Macros/VBA code must b enabled for it to run.

Your VBA code might look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one cell is updated
    If Target.Count > 1 Then Exit Sub
    
'   Check to see if cell A3 was updated
    If (Target.Address = Range("A3").Address) And (Target <> "") Then
'       Add timestamp to cell B3
        Range("B3") = Now()
    End If
    
End Sub

 

[Sammy1994]

Sorry for the long delay in response, I got caught up on projects. That works really well! I wasn't putting the code in the sheet I guess was my issue. Another question is if I wanted more than just A3 and B3 to time stamp, for example I'm trying to allow the user to stamp through out A3:A250 onto B3:B250 How would I go about doing that?

Thank you very much!

 

[Joe4]

Here is one way:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange as Range
    Dim cell as Range

'   See if update in range A3:A250
    Set myRange=Intersect(Target, Range("A3:A250"))

'   Exit if update not in range
    If myRange is Nothing Then Exit Sub
    
'   Loop through cells in range that were updated
    For each cell in myRange
'       Add timestamp to column B if value in column A
         If cell<>"" Then cell.Offset(0,1) = Now()
     Next cell
    
End Sub

 

[Sammy1994]

Here is one way:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange as Range
    Dim cell as Range

'   See if update in range A3:A250
    Set myRange=Intersect(Target, Range("A3:A250"))

'   Exit if update not in range
    If myRange is Nothing Then Exit Sub
    
'   Loop through cells in range that were updated
    For each cell in myRange
'       Add timestamp to column B if value in column A
         If cell<>"" Then cell.Offset(0,1) = Now()
     Next cell
    
End Sub

You're a lot of help I'm so close to making what I want this completed code to do,


Private Sub Worksheet_Change(ByVal Target As Range)


Dim MyRange As Range
Dim cell As Range


' See if update in range A3:A250
Set MyRange = Intersect(Target, Range("A3:A250"))


' Exit if update not in range
If MyRange Is Nothing Then Exit Sub

' Loop through cells in range that were updated
For Each cell In MyRange
' Add timestamp to column B if value in column A
If cell <> "" Then cell.Offset(0, 1) = Now()
Next cell


Set MyRange = Intersect(Range("B3:B250"), Target)
If Not MyRange Is Nothing Then
Sheets("xyz").Unprotect Password:="password"
MyRange.Locked = True
Sheets("xyz").Protect Password:="password"
End If
End Sub

is what I have I'm trying to time stamp and have vb lock the individual row in column B but I'm having trouble getting both to run.

Thank you,

sam

 

[Joe4]

is what I have I'm trying to time stamp and have vb lock the individual row in column B but I'm having trouble getting both to run.

Not quite clear on what you are trying to do.
Are you saying that after the timestamp has been added, you want to lock that row?

 

[Sammy1994]

Oh sorry I want it to lock the cell after the timestamp has been placed

 

[Joe4]

Do you want it to lock column A or column B or both?
Note that if you lock column B, but not column A, that it will enable them to change column A (and column B will not change).

Also note that by default, all cells on a page are locked. Locking does nothing until protection is invoked.
So, what you will need to do at the very beginning is to highlight your whole sheet and unlock all cells.
Otherwise, when we invoke protection, ALL cells will be locked and protected.