Rotating a 3D Chart Using Mouse Events

[ricemcm]

All,

I'm using Excel 2007 and I have a reasonable level of experience with VBA coding in normal / object modules, but I have no experience with class modules or events ... so I do not have a good command on what goes into a normal module vice a class module if I want to use mouse events to rotate a chart.

I am looking to rotate a chart ... Since I am not allowed to post a file, I will give the 3D array here:

	1.0000	2.0000	3.0000	4.0000	5.0000	6.0000	7.0000	8.0000
1.0000	0.0571	0.0629	0.0691	0.0761	0.0837	0.0885	0.3353	0.2794
2.0000	0.0800	0.0880	0.0968	0.1065	0.1171	0.1416	0.3688	0.3074
3.0000	0.1120	0.1232	0.1355	0.1491	0.1640	0.2266	0.4057	0.3381
4.0000	0.1568	0.1725	0.1897	0.2087	0.2296	0.3625	0.4463	0.3719
5.0000	0.2195	0.2415	0.2656	0.2922	0.3214	0.5800	0.4909	0.4091
6.0000	0.3074	0.3381	0.3719	0.4091	0.4500	0.6000	0.5400	0.4500
7.0000	0.2937	0.3231	0.3554	0.3909	0.4300	0.5900	0.4500	0.3750
8.0000	0.2670	0.2937	0.3231	0.3554	0.3909	0.5364	0.3462	0.2885
9.0000	0.2427	0.2670	0.2937	0.3231	0.3554	0.4876	0.2663	0.2219
10.0000	0.2207	0.2427	0.2670	0.2937	0.3231	0.4433	0.2048	0.1707
11.0000	0.2006	0.2207	0.2427	0.2670	0.2937	0.4030	0.1576	0.1313
12.0000	0.1824	0.2006	0.2207	0.2427	0.2670	0.3663	0.1212	0.1010
13.0000	0.1658	0.1824	0.2006	0.2207	0.2427	0.3330	0.0932	0.0777
14.0000	0.1507	0.1658	0.1824	0.2006	0.2207	0.3028	0.0717	0.0598
15.0000	0.1370	0.1507	0.1658	0.1824	0.2006	0.2752	0.0552	0.0460
16.0000	0.1246	0.1370	0.1507	0.1658	0.1824	0.2502	0.0424	0.0354
17.0000	0.1132	0.1246	0.1370	0.1507	0.1658	0.1925	0.0326	0.0218
18.0000	0.1029	0.1132	0.1246	0.1370	0.1507	0.1481	0.0251	0.0167
19.0000	0.0936	0.1029	0.1132	0.1246	0.1370	0.1139	0.0193	0.0129
20.0000	0.0851	0.0936	0.1029	0.1132	0.1246	0.0876	0.0176	0.0117
21.0000	0.0773	0.0851	0.0936	0.1029	0.1132	0.0674	0.0160	0.0106
22.0000	0.0703	0.0773	0.0851	0.0936	0.1029	0.0518	0.0145	0.0097
23.0000	0.0639	0.0703	0.0773	0.0851	0.0936	0.0399	0.0132	0.0088
24.0000	0.0581	0.0639	0.0703	0.0773	0.0851	0.0307	0.0120	0.0080
25.0000	0.0528	0.0581	0.0639	0.0703	0.0773	0.0236	0.0109	0.0073
26.0000	0.0480	0.0528	0.0581	0.0639	0.0703	0.0182	0.0099	0.0066
27.0000	0.0437	0.0480	0.0528	0.0581	0.0639	0.0140	0.0090	0.0060
28.0000	0.0397	0.0437	0.0480	0.0528	0.0581	0.0107	0.0082	0.0055
29.0000	0.0361	0.0397	0.0437	0.0480	0.0528	0.0083	0.0074	0.0050
30.0000	0.0328	0.0361	0.0397	0.0437	0.0480	0.0064	0.0068	0.0045

<tbody>
</tbody>

I have placed into a normal module the following code (based on what I was able to pick up from MSDN):

[COLOR=#2E8B57][FONT=Monaco]Dim myClassModule As New EventClassModule[/FONT][/COLOR]

[COLOR=#2E8B57][FONT=Monaco]Sub InitializeChart()[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]    Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]End Sub[/FONT][/COLOR]

And I have placed the following code into a class module named EventClassModule (also based on what I was able to pick up from MSDN):

[COLOR=#2E8B57][FONT=Monaco]Public WithEvents myChartClass As Chart[/FONT][/COLOR]

I want to rotate the chart by using click and drag, and, if possible, I'd also like to be able to zoom in and out via some method (say a mouse wheel event).

In the following link, a guy named Gabor Doka has done a beautiful job of enabling scatter plot rotation using scroll bars, but I'd like to be able to use mouse events instead:

3d scatter plot for MS Excel (VBA macro)

Please help! Thanks, and I hope to hear from someone

Justin

 

[ricemcm]

All,

I submitted the same question to ExcelKey.com, and they were not to happy to find a duplicate post here.

So, please accept my apologies for such duplication. I informed ExcelKey.com that I would first receive help from Mr. Excel.

I have already received a helpful PM from this site suggesting the use of transparent UserForms since the graphic object only accepts the command Shapes ... Click.

Just wanted to offer a preemptive apology for doing something that might set you off.

Thanks, and I look forward to more helpful PMs (or posts) like that identified above
Justin

 

[ricemcm]

Okay, so I pretty much answered my own question.

In a normal module (that I named CodeModule), insert the following code:

Dim myClassModule As New ClassModule

Sub Set_This_Chart()
ActiveSheet.ChartObjects(1).Activate
Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

Sub Reset_Chart()
Set myClassModule.myChartClass = Nothing
End Sub

In a class module (that I named ClassModule), insert the following code:

Public WithEvents myChartClass As Chart

' Site (1) http://www.computorcompanion.com/LPMArticle.asp?ID=221
' Site (2) http://www.office-archive.com/2-excel/b2554c6a146b484e.htm
' Site (3) http://social.msdn.microsoft.com/Forums/en/isvvba/thread/d4737f34-af87-4298-9997-4f39b03b7fc7


' Site (1), above, was a fantastic resource for understanding chart events.
' The key takeaway was in the section called "Identify the Point Clicked By
' the User" where the Shift argument in the mouse move event was discussed.
' Shift = 1 means that the SHIFT key is being pressed, and Shift = 2 means
' that the CTRL key is being pressed. I mention these options because they
' are what I use below.


' The rotation and elevation are adjusted by moving the mouse over the
' chart and holding down the SHIFT key, while the zoom and perspective
' are adjusted by moving the mouse over the chart and holding down the
' CTRL key. It helps to have a button on the homescreen to which you
' assign the main macro.


' Note: The x and y values provided as arguments to the mouse move event are
' in chart object client coordinate units, while the chart element dimensions
' are in points. Sites (2) and (3), above, discuss this. As it turns out, the
' x and y arguments, when multiplied by 3 / 4, give the correct chart element
' values in units of points. Go figure.


Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    
    Range("A1").Value = ActiveChart.Parent.Width
    Range("A2").Value = ActiveChart.Parent.Height
    
    TOTAL_WDTH_IN_POINTS = ActiveChart.Parent.Width
    TOTAL_HGHT_IN_POINTS = ActiveChart.Parent.Height
    
    If Shift = 1 Then
        
        ' Range("A4").Value = x * 3 / 4
        ' Range("A5").Value = y * 3 / 4
        
        LOCAL_WDTH_IN_POINTS = x * 3 / 4
        LOCAL_HGHT_IN_POINTS = y * 3 / 4
        
        If LOCAL_WDTH_IN_POINTS >= 0 And LOCAL_WDTH_IN_POINTS <= TOTAL_WDTH_IN_POINTS And _
           LOCAL_HGHT_IN_POINTS >= 0 And LOCAL_HGHT_IN_POINTS <= TOTAL_HGHT_IN_POINTS Then
            
            ' Range("A3").Value = "Y"
            
            WDTH_FRACTION = Round(LOCAL_WDTH_IN_POINTS / TOTAL_WDTH_IN_POINTS, 2)
            HGHT_FRACTION = Round(LOCAL_HGHT_IN_POINTS / TOTAL_HGHT_IN_POINTS, 2)
            
            ' Range("A7").Value = WDTH_FRACTION
            ' Range("A8").Value = HGHT_FRACTION
            
            ' Note: Rotation must be between 0 and 360.
            ActiveChart.Rotation = Round(WDTH_FRACTION * 360, 0)
            
            ' Note: Elevation must be between -90 and 90.
            ActiveChart.Elevation = Round(180 * HGHT_FRACTION - 90, 0)
            
        Else
            
            ' Range("A3").Value = "N"
            
        End If
        
    End If
    
    If Shift = 2 Then
        
        ' Range("A4").Value = x * 3 / 4
        ' Range("A5").Value = y * 3 / 4
        
        LOCAL_WDTH_IN_POINTS = x * 3 / 4
        LOCAL_HGHT_IN_POINTS = y * 3 / 4
        
        If LOCAL_WDTH_IN_POINTS >= 0 And LOCAL_WDTH_IN_POINTS <= TOTAL_WDTH_IN_POINTS And _
           LOCAL_HGHT_IN_POINTS >= 0 And LOCAL_HGHT_IN_POINTS <= TOTAL_HGHT_IN_POINTS Then
            
            ' Range("A3").Value = "Y"
            
            WDTH_FRACTION = Round(LOCAL_WDTH_IN_POINTS / TOTAL_WDTH_IN_POINTS, 2)
            HGHT_FRACTION = Round(LOCAL_HGHT_IN_POINTS / TOTAL_HGHT_IN_POINTS, 2)
            
            ' Range("A7").Value = WDTH_FRACTION
            ' Range("A8").Value = HGHT_FRACTION
            
            ' Note: Perspective must be between 0 and 100.
            ActiveChart.Perspective = Round(WDTH_FRACTION * 100, 0)
            
            ' Should we put zooming capability here?
            ' ActiveChart.Axes(xlValue).MinimumScale
            ' ActiveChart.Axes(xlValue).MaximumScale
            ' ActiveChart.Axes(xlCategory).MinimumScale
            ' ActiveChart.Axes(xlCategory).MaximumScale
            
        Else
            
            ' Range("A3").Value = "N"
            
        End If
        
    End If
    
End Sub

Then, make a button on the same spreadsheet as your embedded chart, as in the following image:

This solution is not PERFECT, but it's a good start at getting something Microsoft should have given us to begin with ... the ability to use our mouse to rotate a chart.

Should we add zooming capability? Look at the end of my "If Shift = 2 Then ... End If" block to see where I've included the ActiveChart.Axes() stuff.

Any feedback would be appreciated

Can't wait to hear what you've discovered ...

Justin