Thanks for your help Rob. This has bothered me for some time. What made
you think of this solution?
Hi Rock,
It's just one of the stock list of things I try when a perfectly good
procedure doesn't do what it looks like it ought to do. Sometimes, and
especially when it comes to event procedures, Excel doesn't really "commit"
the changes (or at least that's my way of thinking about it <g) for some
reason until after the procedure has been allowed to come to a full stop.
The Application.OnTime Now() technique allows you to defer execution of
something else until just after that.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Mr.RockDoc" wrote in message
...
Rob,
That fixed the problem. Just in case someone else is interested in the
solution the last "Call init" line in the code below was changed to:
Application.OnTime Now(), "init"
As stated by Rob:
"What this does is allow the event procedure to finish executing
completely
before the init procedure is run again."
"Everything seems to work fine with this change, although like I said, I
don't know why it would matter. Dynamically adding controls to worksheets
has always had a bit of voodoo associated with it, so I'm just glad when I
can get it working correctly at all. :-)"
Thanks for your help Rob. This has bothered me for some time. What made
you think of this solution?
Many Thanks,
Rock
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer
Call init
' Clear Checkboxes if they exist
Call ClearCheckBoxes
' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub
"Rob Bovey" wrote in message
...
Hi Rock,
Go ahead and send me your workbook and I'll have a look at it:
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Mr.RockDoc" wrote in message
...
Rob,
Thanks for your patience in trying to help me. I have a 48k excel
file
that
I can send that illustrates the problem. Email me if you would like
it.
To
summaryize, I have data in sheet1 a1:b4. I have a chart sheet named
"DSPlot.ch". I have code on sheet1, a PublicDeclaration module and a
EventClassModule. The code on each sheet is listed below.
1) I can click the init button and the chart events work. 2) I
click
the
checkBoxes command button (which calls init before and after
execution)
and
the chart events don't work. 3) I click the init button and the
chart
events now work.
How can I get the chart events to work without pressing the init
button
after using the checkBoxes button?
I hope this is clear.
Rock
'Sheet1 code:
Option Explicit
Private Sub initCommandButton_Click()
Call init
End Sub
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer
Call init
' Clear Checkboxes if they exist
Call ClearCheckBoxes
' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub
Private Sub addCheckBox(rw As Integer, cl As Integer)
Dim myRng As Range, myCell As Range, myObj As OLEObject
With ActiveSheet
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = .OLEObjects.Add(ClassType:="Forms.Checkbox.1",
_
Link:=False, DisplayAsIcon:=False, _
Left:=myCell.Left + 0.5 * myCell.Width - 0.5 *
myCell.Height, _
Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing
End Sub
Private Sub ClearCheckBoxes()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub
'PublicDeclarations Module:
Option Explicit
Public myClassModule As New EventClassModule
Public Const dsPlot As String = "DSPlot.ch"
Public dsChart As Chart
Public Sub init()
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub
'EventsClassModule:
Option Explicit
Public WithEvents myChartClass As Chart
Private Sub myChartClass_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim ser As Series, xPt As Double, yPt As Double
Application.ScreenUpdating = False
Select Case ElementID
Case xlSeries
' Arg1 reports the index of the selected series in the
' series collection
Set ser = ActiveChart.SeriesCollection(Arg1)
' Arg2 reports -1 if the whole series was selected,
' otherwise it reports the index of the point selected
' in the series
If Arg2 = -1 Then
MsgBox "Click again to refine your selection to a
point."
Else
MsgBox "(X,Y) = " & "(" & ser.XValues(Arg2) & "," &
ser.Values(Arg2) & ")"
End If
End Select
End Sub
"Rob Bovey" wrote in message
...
"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for
initialization.
When
I put the debug statements in the init routine, they all print out
the
same
name which is "DSPlot.ch" which is correct. However, in looking
at
the
dsPlot object, it shows nothing and therefore the eventclassmodule
doesn't
work when I click on the "DSPlot.ch"
Hi Rock,
In the following chunk of code from the init procedu
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print
the
same name. This means at the point when the initi procedure was run,
the
dsChart object variable and the event handler were both set
correctly.
Then you say your event handler doesn't work when you click on
the
chart. That tells me that at some point between when the init
procedure
was
run and when you clicked on the chart you must have run code
somewhere
that
wiped out the public variables again (maybe in an event procedure?)
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *