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:
Workbook_Open
eventWorksheet_Activate
eventWorksheet_Deactivate
event, if the "client input" cells are different among each other - update the VBA variablesThis 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
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.
Comments