VBA to : Total Sum cell allows for manual entry or Sum if cells have value

StrawberryDreams

Board Regular
Joined
Mar 26, 2022
Messages
80
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
Looking for VBA to allow manual entry for the Total Cell if both Cell 1 & Cell 2 are empty. If either Cell 1 or Cell2 have a value but the other is blank then still Sum total as the formula in total.


Total3
Cell 12
Cell 21
 
Put the following code in your sheet events.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cell_1 As Range, cell_2 As Range, cell_T As Range, rng As Range
 
  Set cell_T = Range("C2")    'fit the cells to your needs
  Set cell_1 = Range("C3")
  Set cell_2 = Range("C4")
 
  Set rng = Intersect(Target, Union(cell_1, cell_2, cell_T))
  If Not rng Is Nothing Then
    Application.EnableEvents = False
      If cell_1 = "" And cell_2 = "" Then
        If cell_T.HasFormula Then cell_T.Value = ""
      Else
        cell_T.Formula = "=SUM(" & cell_1.Address & ":" & cell_2.Address & ")"
      End If
    Application.EnableEvents = True
  End If
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


:giggle:
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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