Oct 02 2022 02:20 PM
My file links an online source for daily Gold Prices.
I need an if statement, where if I enter a date in a certain cell (ie, cell A1), then the calculated Daily Gold Price per Gram in cell A2, should be frozen or pasted, keeping the Gold Price per Gram of that days.
I am not as familiar with VBAs, but any assistance is welcomed.
Thank you.
Oct 02 2022 02:32 PM - edited Oct 02 2022 02:33 PM
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If Range("A1").Value <> "" Then
Application.EnableEvents = False
Range("A2").Value = Range("A2").Value
Application.EnableEvents = True
End If
End If
End Sub
Oct 02 2022 07:54 PM - edited Oct 02 2022 07:57 PM
Thank you good sir.
Question, do I always have to run the macro or once it’s enabled & the file type is updated/saved, then as long as the first condition is met (date inserted in A1), the paste will happen automatically?
also, if I write the macro in tab 1, but duplicate the tab, will the macro now be enabled in tab 2 (copy of tab 1) or would I need to rewrite the macro in the tab 2?
thank you again Hans
Oct 03 2022 01:52 AM
The code is not a macro, but a so-called event procedure. Excel will execute it automatically each time you change the value of a cell in that worksheet. (but it will only do something when you enter a value in cell A1). You don't have to run it yourself (actually, you cannot run it since it is not a macro).
The code is stored with the worksheet, so if you duplicate the worksheet, the code will be duplicated to the copy, and run there automatically.
Oct 03 2022 07:40 AM
Oct 03 2022 08:56 AM
Oct 03 2022 02:03 PM
Oct 03 2022 02:11 PM
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
' Did A1 change?
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A1").Value <> "" Then
' Remove the formula
Range("A2").Value = Range("A2").Value
Else
' Restore the formula
Range("A2").Formula = "='Gold Prices'!A1"
End If
Application.EnableEvents = True
End If
End Sub
Oct 05 2022 06:18 AM
Oct 15 2022 10:23 AM
Oct 15 2022 01:08 PM
Will cell B11 be edited by the user or does it contain a formula?
Oct 15 2022 01:34 PM
Oct 15 2022 01:52 PM
Do you want to react each time a cell in that column is changed, or only when B2 is changed?
Oct 15 2022 02:43 PM
Oct 15 2022 02:51 PM
Thanks. Try this version:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("B2").Value <> "" Then
Range("E9").Value = Range("E9").Value
Range("G9").Value = Range("G9").Value
Else
Range("E9").Formula = "='Live Gold & Silver Prices'!$J$2"
Range("G9").Formula = "='Live Gold & Silver Prices'!$J$3"
End If
Application.EnableEvents = True
End If
If Not Intersect(Range("J:J"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("B11").Value > 0 Then
Range("B3").Value = "Not fully paid"
Else
Range("B3").Value = Date
End If
Application.EnableEvents = True
End If
End Sub
Oct 16 2022 11:22 AM