Worksheet problem

[clifftech]

This should be easy but so far I haven't found a solution.


In worksheet 1 I can enter values in cells for a chart manually or send values from worksheet 2 by using a command button. Worksheet 1 has an Worksheet_SelectionChange routine that updates chart 1 axes in worksheet 1. Is there a way to run Worksheet_SelectionChange for Worksheet 1 from the command button in Worksheet 2?

I want chart 1 in worksheet 1 to update the axes after I send the new values from Worksheet 2.

 

[combo]

Change the procedure (Worksheet_SelectionChange) to public. Using code names:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Private Sub CommandButton1_Click()
With Sheet1
    Call .Worksheet_SelectionChange(.Range("A1"))
End With

combo

 

[clifftech]

Thanks for helping.


I tried your code and am getting a compile error:

Method or data member not found

 

[combo]

Could you give more details how you run the code?
Do you have a sheet with 'Sheet1' code name (code name is the same as (Name) property in properties window)?
Is the Worksheet_SelectionChange procedure located in the sheet module of Sheet1 (as above)? If no, replace 'Sheet1' with proper name.
Is all the code (and sheets) in the same workbook, where is the button?

combo

 

[clifftech]

All the worksheets are in the same workbook.
Worksheet1 is named "SARS"
Worksheet2 is named "SetUp"

Worksheet_SelectionChange is located in the SARS objects folder. Here is the code:


Public Sub Worksheet_SelectionChange(ByVal Target as Range)

With ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlCategory)
.MaximumScale = Range("W2") + 10
.MinimumScale = Range("X2") - 10
End With

With ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue)
.MaximumScale = Range("W3") + 10
.MinimumScale = Range("X3") - 10
End With


The Commandbutton is located in the "SetUp" worksheet objects folder.

I tried replacing Sheet 1 with Worksheets("SARS") in your code and get the error "Unable to get the ChartObjects property of the Worksheet class.

 

[NWBeaver]

Try using this in Sheet 2

Private Sub CommandButton1_Click()
Call Sheet1.Worksheet_SelectionChange(Cells(1, 1))
End Sub

 

[PHV]

Don't use ActiveSheet but Sheets("SARS") in your SelectionChange event procedure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

As for code name: in general it is different from sheet's name in the tab. The name is visible and can be changed by the user in excel interface. The code name is recognised by the code as reference to the worksheet as an object. See (Name) property in VBE "Properties' window. Alternatively, in sheet's module add and execute:

Sub ShowCodeName()
MsgBox Me.CodeName
End Sub

BTW, 'Me' is a reference to the object associated with the object's module where it is called. If Worksheets("SARS") is in your code the same object as ActiveSheet, you can use:
With Me.ChartObjects("Chart 6").Chart.Axes(xlCategory)
This will work whatever name or code name is.

combo

 

[clifftech]

Getting closer.

The Command button in Sheet1 will now make the changes to the axes when sending the new values.

I now need to add code so that if I manually add values in Sheet 2 ("SARS") the axes will change. I am adding this code to Worksheet_SelectionChange:

If Not Intersect(Target, Sheets("SARS").Range("S17:T51")) Is Nothing Then

With Me.ChartObjects("Chart 6").Chart.Axes(xlCategory)
.MaximumScale = Range("W2") + 10
.MinimumScale = Range("X2") - 10
End With

With Me.ChartObjects("Chart 6").Chart.Axes(xlValue)
.MaximumScale = Range("W3") + 10
.MinimumScale = Range("X3") - 10
End With

End If

End Sub



When I add the code I get a run-time error - Method 'Intersect' of Object'_Global'failed

 

[combo]

Debug your project. Compile it. You could try to set error trapping to 'break on all errors' ('general' tab in VBE options) for debugging. Execute the cose step by step. Test values.
I am not sure if the SelectionChange is the best event for this case. It fires every time when user changes selection, whereas it seems that you are more interested in the case values are changed (=> Change event).
Maybe there would be better to split yhe code to event procedure and other one, that is called either by the code or button. This is more readible.
If you choose 'Change' event there may be necessary to switch off events when the code changes workbook (Application.EnableEvents).

combo

 

[clifftech]

Combo,

Good thoughts on splitting the code. I'll give it a try.

Thanks for your help.