Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an event for Chart_MouseUp (attached), and it works just fine.
    The Excel 2000 workbook has a chart (which I create by VBA code), and the user clicks on a data point and the event code does it's job.

    I'd like the event to be trapped whenever I create a new chart - at the user's whim, but that suggests to me that my VBA project would have to be self-modifying; that is, I would somehow have to add VBA code to the chart module itself, and in a locked project that is a no-no.

    A "Workbook_SheetBeforeDoubleClick" event in the ThisWorkbook module traps double-clicks in worksheets, but double-clicks in Chart sheets brings an invitation to modify the chart.

    Before I spend the traditional hours ferreting out information, does anyone know of a real objection to trying to do what I am trying to do?
    Of course, an existing solution would be right handy, but at this stage I'm not even sure if it is reasonable to expect to be able to create code on-the-fly to trap a user's click on a chart data point.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It is possible to add code to a worksheet or chart sheet using code, but it's tricky.
    As an alternative, you could place a hidden chart sheet with the existing code in the workbook, and whenever you need a new chart, copy this chart sheet (which will copy the code automatically), modify it as needed and make it visible.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV' post='798178 View Post
    ... a hidden chart sheet with the existing code ... copy this chart sheet
    Thank you, Hans
    We need a new tag, {crude code} to replace {code} (grin!)
    Code:
    Function BuildSpiderFromSelection(rngSel As Range, strTitle As String)
    	Dim cht As Chart
    	Set cht = Charts.Add
    	With cht
    		.ChartType = xlRadarMarkers
    		.SetSourceData Source:=rngSel, PlotBy:=xlColumns
    		.Location Where:=xlLocationAsNewSheet
    		.HasTitle = True
    		.ChartTitle.Characters.Text = strTitle
    		.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
    	End With
    	
    	ActiveChart.ChartArea.Select
    	ActiveChart.ChartArea.Copy
    	Sheets("chtTarget").Select
    	ActiveChart.ChartArea.Select
    	ActiveChart.Paste
    	Application.DisplayAlerts = False
    	Sheets(cht.Name).Delete
    	Application.DisplayAlerts = True
    End Function
    Sub TESTBuildSpiderFromSelection()
    	Call BuildSpiderFromSelection(Selection, "Spider003")
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='chrisgreaves' post='798212' date='15-Oct-2009 21:59']Thanks. I'm intrigued.[/quote]
    See Chip Pearson's Programming In The VBA Editor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •