Multiple private sub

[thcook87]

Hi,

I have two different VBA functions: goalseek and automatic change of x- and y-axis ranges.
I would like to have both calculated in the same sheet. The codes are working indepentenly (on different sheets), but how can I get this working on 1 single sheet.

Thanks in advance!

The code:

Private oldGoalSeek1 As Variant
Private oldGoalSeek2 As Variant


Private Sub CommandButton1_Click()
ActiveWindow.SelectedSheets.PrintPreview ' preview
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("GoalSeek1").Value <> oldGoalSeek1 Then
Range("GoalSeek1").GoalSeek goal:=0, changingcell:=Range("ByChangingCell")
oldGoalSeek1 = Range("GoalSeek1").Value
End If
If Range("GoalSeek2").Value <> oldGoalSeek2 Then
Range("GoalSeek2").GoalSeek goal:=0, changingcell:=Range("ByChangingCell2")
oldGoalSeek2 = Range("GoalSeek2").Value
End If
End Sub

Private Sub Worksheet_Calculate()
Dim cht As Chart
Dim wks As Worksheet

Set wks = ActiveSheet
Set cht = wks.ChartObjects("Chart 3").Chart

If wks.Range("$G$9").Value <> cht.Axes(xlCategory).MaximumScale Then
cht.Axes(xlCategory).MaximumScale = wks.Range("$G$9").Value
End If
If wks.Range("$G$10").Value <> cht.Axes(xlCategory).MinimumScale Then
cht.Axes(xlCategory).MinimumScale = wks.Range("$G$10").Value
End If
If wks.Range("$G$11").Value <> cht.Axes(xlCategory).MajorUnit Then
cht.Axes(xlCategory).MajorUnit = wks.Range("$G$11").Value
End If
If wks.Range("$G$13").Value <> cht.Axes(xlValue).MaximumScale Then
cht.Axes(xlValue).MaximumScale = wks.Range("$G$13").Value
End If
If wks.Range("$G$14").Value <> cht.Axes(xlValue).MinimumScale Then
cht.Axes(xlValue).MinimumScale = wks.Range("$G$14").Value
End If
If wks.Range("$G$15").Value <> cht.Axes(xlValue).MajorUnit Then
cht.Axes(xlValue).MajorUnit = wks.Range("$G$15").Value
End If
End Sub

[:) Sixthsense :)]

First one is manual and second one is automatic. So confused what you are trying to do

[thcook87]

Actually, after posting the thread, I found out the problem, and both calculations are executed automatically (when a value that is related to the calculation is changed only).
If you're interested, I will still explain what I was trying to do (which works now ):

- for 2 different cells, I want to do a GoalSeek automatically (at least, it is doing it automatically now when 1 of the values is changed)
- then, for a graph (totally indepeding from the GoalSeek), I would like to have the axis automatically changed (the range), when a value of 1 of the axis (based on another cells' value) is changed.

I'm wondering why the first one is manual and the second one is automatic? It looks like the first one is automatically calculated as well....

Thanks!

[:) Sixthsense :)]

I'm wondering why the first one is manual and the second one is automatic? It looks like the first one is automatically calculated as well....

Oopss... The first one is targeted on the below code.

Please Login or Register  to view this content.

Also I have not read two other codes properly and treated rest of the two automatic codes as one.

Anyhow glad you fixed it