Code for updating values from Input boxes

Sub Macro666() Dim MyRange1 As Range, MyRange2 As Range Set MyRange1 = Application.InputBox _ (Prompt:="Select first cell", Type:=8) If MyRange1 Is Nothing Then Exit Sub Set MyRange2 = Application.InputBox _ (Prompt:="Select second cell", Type:=8) If MyRange2 Is Nothing Then Exit Sub MsgBox "TOTAL =(" & MyRange1.Value & ") + (" & MyRange2.Value & ")" 'Display message box. ActiveCell.FormulaR1C1 = ("TOTAL =(" & MyRange1.Value & ")+ (" & MyRange2.Value & ")")

Hi Gloria

It was I that gave you the code. As you result is not an actual formula then we can update it with use of the Sheet Change Event. But first I have modified the code so the ranges have names.

Sub Macro666()
Dim MyRange1 As Range, MyRange2 As Range

On Error Resume Next
Set MyRange1 = Application.InputBox _
(Prompt:="Select first cell", Type:=8)
If MyRange1 Is Nothing Then Exit Sub
MyRange1.Name = "MyCell1"

Set MyRange2 = Application.InputBox _
(Prompt:="Select second cell", Type:=8)
If MyRange2 Is Nothing Then Exit Sub
MyRange2.Name = "MyCell2"
MsgBox "TOTAL =(" & MyRange1.Value & ") + (" & MyRange2.Value & ")"
'Display message box.
ActiveCell.FormulaR1C1 = ("TOTAL =(" & MyRange1.Value & ")+ (" & MyRange2.Value & ")")
ActiveCell.Name = "MyTotal"
End Sub

Now we can use the sheet change event.

Right click on the sheet picture top left next to "File" and select "View Code" then paste in this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim MyCell1Val As String, MyCell2Val As String

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = Range("MyCell1").Address Or _
Target.Address = Range("MyCell2").Address Then
MyCell1Val = Range("MyCell1")
MyCell2Val = Range("MyCell2")
Range("MyTotal") = "TOTAL =(" & MyCell1Val & ")+ (" & MyCell2Val & ")"
End If
End Sub

Should do the trick!

Dave

OzGrid Business Applications

Re: Code for updating values - add on question

Thank You Dave -- You are soooo smart. It worked great. (Actually, looking back I realize you provided the code to my friend, Gina, who was trying to help me. I did not know she got it from you! Anyway, if I could trouble you or someone else, for another answer? If I email the spreadsheet to someone else (I know I'm showing my ignorance), because of the "Private Sub Workbook", will they be able to make changes and have it update too?
Thank you again for donating all the time you do to us beginners.

Please, somebody... I need help changing the following macro, so that if I change the value on the spreadsheet (MyRange1 and MyRange2), the equation will also update. I found most of this code from other questions here, but don't know how to make the changes. Thanks in advance. Sub Macro666() Dim MyRange1 As Range, MyRange2 As Range Set MyRange1 = Application.InputBox _ (Prompt:="Select first cell", Type:=8) If MyRange1 Is Nothing Then Exit Sub Set MyRange2 = Application.InputBox _ (Prompt:="Select second cell", Type:=8) If MyRange2 Is Nothing Then Exit Sub MsgBox "TOTAL =(" & MyRange1.Value & ") + (" & MyRange2.Value & ")" 'Display message box. ActiveCell.FormulaR1C1 = ("TOTAL =(" & MyRange1.Value & ")+ (" & MyRange2.Value & ")")

Now we can use the sheet change event. Right click on the sheet picture top left next to "File" and select "View Code" then paste in this code:

Re: Code for updating values - add on question

Gloria
The worksheet will work if you email it to
someone else as long as they ENABLE the macros
when prompted.

Ivan Thank You Dave -- You are soooo smart. It worked great. (Actually, looking back I realize you provided the code to my friend, Gina, who was trying to help me. I did not know she got it from you! Anyway, if I could trouble you or someone else, for another answer? If I email the spreadsheet to someone else (I know I'm showing my ignorance), because of the "Private Sub Workbook", will they be able to make changes and have it update too? Sub Macro666() Dim MyRange1 As Range, MyRange2 As Range