How to update two cells simultaneously, through one or the other?

debugcn Published at Dev

Lurco

I have a Excel workbook calculator dependent on a few parameters. I want the client to be able to insert those parameters into an appropriate "client input" cell on every spreadsheet so he doesn't have to jump back and forth between spreadsheets.

Is there a good way to do it? I tried the following scheme, but it's buggy for reasons unknown to me:

make a VBA module declaring variables to hold my parameters
initialize them with appropriate initial values on the Workbook_Open event
make the specific sheets write those values into "client input" cells on Worksheet_Activate event
in a Worksheet_Deactivate event, if the "client input" cells are different among each other - update the VBA variables

This works sometimes, but not always. Is there a better way to do this?

EDIT:

This is my "GM" Module:

Option Explicit

Public perspective As String
Public RSS As String
Public Payback As Double

This is my "ThisWorkbook:

Private Sub Workbook_Open()
    GM.perspective = Worksheets("Hidden variables").Range("A1").Value
    GM.RSS = Worksheets("Hidden variables").Range("B2").Value
    GM.Payback = Worksheets("Hidden variables").Range("C3").Value
End Sub

Private Sub Workbook_Close()
    Worksheets("Hidden variables").Range("A1") = GM.perspective
    Worksheets("Hidden variables").Range("B2") = GM.RSS
    Worksheets("Hidden variables").Range("C3") = GM.Payback
End Sub

This is in my worksheet 1 (in worksheet 2 there is an analogous code):

Option Explicit

Private Sub Worksheet_Activate()
     'SIMULTANEOUS UPDATE p.1
     Worksheets("1").Range("I32") = GM.Payback
     Worksheets("1").Range("I29") = GM.RSS
     Worksheets("1").Range("I26") = GM.perspective
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'BASIC PRICE CALCULATION
     If Target.Count > 1 Then Exit Sub
     If Target = Range("I32") _
     Or Target = Range("I29") _
     Or Target = Range("I26") _
     Or Target = Worksheets("Intro").Range("price") _
     Then

    Worksheets("Hidden variables").Range("condition") = 2
        Worksheets("Hidden variables").Range("basic_price") = Worksheets("Intro").Range("price").Value
        Range("M44").GoalSeek Goal:=0, ChangingCell:=Worksheets("Hidden variables").Range("basic_price")
        If Worksheets("Hidden variables").Range("basic_price").Value < 0 Then
            Range("M46") = "Error"
        Else
            Range("M46") = Worksheets("Hidden variables").Range("basic_price").Value
        End If
    Worksheets("Hidden variables").Range("condition") = 1
    End If
End Sub

Private Sub Worksheet_Deactivate()
'SIMULTANEOUS UPDATE p.2
        GM.Payback = Worksheets("1").Range("I32").Value
        GM.RSS = Worksheets("1").Range("I29").Value
        GM.perspective = Worksheets("1").Range("I26").Value
End Sub

Maxime Porté

To avoid infinite loop, you can use a global variable Outside a function (at the begin of your module)

Public isUpdating As Double

inside your Worksheet_change

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if an update is in progress. If so, exit the change
    if isUpdating then
        exit sub
    end if

    ' Begin of the update
    isUpdating = true

    ' Here your update

    ' End of the update
    isUpdating = false
End sub

Collected from the Internet

Please contact [email protected] to delete if infringement.