VBA Code to Enter Cell Data or Execute Formula

[jmd78]

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.

 

[Yongle]

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}

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

 

[Yongle]

Re: VBA Code to Enter Cell Data or Eexcute Formula

same as a above but more elegant

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

 

[jmd78]

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]

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]

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),)))))