+ Reply to Thread
Results 1 to 4 of 4

Extracting worksheet name from formula or chartsheet name

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Extracting worksheet name from formula or chartsheet name

    My question is about removing data from the source sheet using the data points from a chart (chart sheet) using vba. I've looked at J. Peltier's code and found something of use from SO. The code does what is is supposed to; however, I'm stuck at one point since the OP was dealing with an embedded chart; and my charts are sheets. My problem is setting the worksheet as the source sheet.
    Please Login or Register  to view this content.
    I've attempted to create a function, but the error thrown says argument not optional:
    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: Extracting worksheet name from formula or chartsheet name

    Per usual, I was able to solve the primary issue after looking at the code a little more and by simply using another variable to extract from the defined array. Not pretty, but it works:
    Please Login or Register  to view this content.
    The only issue I need to confirm is that the actual point I'm selecting is the data source I want removed. It appears it is very close but I will report back tomorrow.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: Extracting worksheet name from formula or chartsheet name

    ..
    This works and is very simple. This is a qualitative way to remove outliers or bad data through chart selection.

    For background on my purpose, some data outliers are legitimate points, so using statistical methods yield improper results within final calculation. By graphing xy and visually inspecting on a macro scale (>15000 data) where suspect data resides, points are visually located rapidly within the chart. Any option could work on the suspect points from here. Inherent data gaps created (through clearing) within the range provide quick option to parse whole sections (based on threshold criteria) or fill series (interpolated).

    One can change the axis variable easily, but the variable I needed to indicate was the independent. The code below goes within a class module. For detailed scripting on the procedure, look up J. Peltier's information on chart events. This is also coded for chart sheets, not embedded charts. It should also be noted, that once data is deleted, it is permanent, so keep that in mind.
    Please Login or Register  to view this content.
    Last edited by terriertrip; 02-06-2020 at 02:11 PM.

  4. #4
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: Extracting worksheet name from formula or chartsheet name

    There were a couple of bugs worked out. Here's the revised code cleaned up a bit with an option to delete the selected point or zoom into the graph to get a closer look. Resetting of the scales is done through a userform command button. My last piece is to have a dynamic variable that determines the "zoom level" (e.g. 500, 1000, 1500, etc.). Funky but does what i need it to do.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Scroll on a chartsheet excel vba
    By sourabhg98 in forum Excel General
    Replies: 2
    Last Post: 11-17-2016, 12:56 PM
  2. [SOLVED] Select a chart from a chartsheet in VBA
    By BillDoor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-16-2015, 11:38 PM
  3. [SOLVED] Delete Chartsheet
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2014, 06:27 PM
  4. Replies: 4
    Last Post: 04-15-2014, 02:22 AM
  5. Replies: 0
    Last Post: 04-14-2014, 04:02 AM
  6. RangeFromPoint ... on a chartsheet ?
    By MrT in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2006, 09:35 AM
  7. [SOLVED] 1 ChartSheet, 10 worksheets
    By Martin Wheeler in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-22-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1