VBA Code to Enter Cell Data or Execute Formula

jmd78

New Member
Joined
Sep 24, 2013
Messages
26
I have a spreadsheet that I need help writing the correct VBA code to allow for a cell containing a formula to be overwritten with hard coded data. To explain I have a cell that calculates a date however from time to time I need to override that calculated date with a hard coded date. The two cells that contain the formula are G22 and G24, respectively. One or the other or both, from time to time, may need to have the calculated value overwritten with a hard coded date.

The formula I am using in each cells to calculate the date is as follows:
G22 | =IF(F22="","",(DATE(YEAR(F22)+1,MONTH(F22),DAY(F22))-1))
G24 | =IF(F24="","",(DATE(YEAR(F24)+1,MONTH(F24),DAY(F24))-1))

Any help would be most appreciated.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Re: VBA Code to Enter Cell Data or Eexcute Formula

Here is one way

A toggle based on double-click event
- the value in each of the 2 cell toggles between a formula and hard-coded value
- when user double-clicks in cell the value in that cell is changed to a formula or the formula is replaced by hard coded value

Paste into sheet module
(right-click sheet tab \ select View Code \ paste code below into code window \ go back to Excel with {ALT}{F11}
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Const G22 = "=IF(F22="""","""",(DATE(YEAR(F22)+1,MONTH(F22),DAY(F22))-1))"
    Const G24 = "=IF(F24="""","""",(DATE(YEAR(F24)+1,MONTH(F24),DAY(F24))-1))"
    Dim Ref As String: Ref = Target.Address(0, 0)

    If Ref = "G22" Or Ref = "G24" Then
        Cancel = True
        If Target.HasFormula Then
            Target.Value = Target.Value
        ElseIf Ref = "G22" Then
            Target.Formula = G22
        Else
            Target.Formula = G24
        End If
    End If
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Re: VBA Code to Enter Cell Data or Eexcute Formula

same as a above but more elegant :)

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Ref As String, Ref2 As String, F As String
    Ref = Target.Address(0, 0):    Ref2 = Target.Offset(, -1).Address(0, 0)
    
    F = Replace("=IF(F22="""","""",(DATE(YEAR(F22)+1,MONTH(F22),DAY(F22))-1))", "F22", Ref2)
    
    If Ref = "G22" Or Ref = "G24" Then
        Cancel = True
        If Target.HasFormula Then Target.Value = Target.Value Else Target.Formula = F
    End If
End Sub
 
Last edited:

jmd78

New Member
Joined
Sep 24, 2013
Messages
26
Re: VBA Code to Enter Cell Data or Eexcute Formula

Thank you, Yongle. Your solution was ingenious, worked perfectly and was most appreciated. I do have another question, is it possible to have a pop-up or mouse over note that would instruct the user of these two cells unique properties, perhaps..."Double click the cell to override the calculated date and insert a static date"
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Re: VBA Code to Enter Cell Data or Eexcute Formula

Cells do not have mouse-over events (although something similar can be engineered)
Instead, try adding a comment to each cell (right-click on cell - adding comments is one of the options)
- when user hovers over cell, the comment is made visible
 

jmd78

New Member
Joined
Sep 24, 2013
Messages
26
Re: VBA Code to Enter Cell Data or Eexcute Formula

Yongle, your formula works very well. Would help with the same type of code for two other cells within the same spreadsheet that I need to be able to double click and override the formula and enter static data or double click and return to the formula?

Cell C61 | Formula =IF(OR(D55="SA", D55="ESA"),0, IF(OR(C59="N/A", C59="?"),0, IF(C59<=25,20, IF(C59>25,ROUNDUP((((C59-25)*1)+20),),"ERROR"))))

Cell C63 | Formula =IF(OR(D55="SA", D55="ESA"),0, IF(OR(C59="N/A",C59="?"),0, IF(C59>25,ROUNDUP((((((C59-25)*12)+300)*D63)),),IF(C59<=25,ROUNDUP((300*D63),)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

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
Top