+ Reply to Thread
Results 1 to 9 of 9

Control range in a line chart by hiding/unhiding data columns using data validation lists

Hybrid View

dgibney Control range in a line chart... 01-20-2015, 08:41 PM
natefarm Re: Control range in a line... 01-21-2015, 05:43 PM
dgibney Re: Control range in a line... 01-21-2015, 05:46 PM
dgibney Re: Control range in a line... 01-21-2015, 05:50 PM
natefarm Re: Control range in a line... 01-21-2015, 06:25 PM
dgibney Re: Control range in a line... 01-21-2015, 07:19 PM
natefarm Re: Control range in a line... 01-22-2015, 10:27 AM
dgibney Re: Control range in a line... 01-23-2015, 02:29 PM
dgibney Re: Control range in a line... 01-23-2015, 02:32 PM
  1. #1
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Control range in a line chart by hiding/unhiding data columns using data validation li

    It still wouldn't open, but I was able to save it, so I went that route. Paste the following code into the code module of the Dashboard sheet. To make it work you'll need to adjust the headings on the Data sheet (which are qq - yyyy) to match the validation values (which are yyyy - qq).
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long
    
        If Target.Address = Range("START").Address Or Target.Address = Range("STOP").Address Then
            If Range("START").Value >= Range("STOP").Value Then
                MsgBox "Start value must be < Stop value"
            Else
                Application.ScreenUpdating = False
                col = 9
                With Sheets("DATA")
                    Do Until .Cells(1, col).Value = Range("START").Value Or .Cells(1, col).Value = ""
                        .Columns(col).Hidden = True
                        col = col + 1
                    Loop
                    Do Until .Cells(1, col).Value > Range("STOP").Value Or .Cells(1, col).Value = ""
                        .Columns(col).Hidden = False
                        col = col + 1
                    Loop
                    Do Until .Cells(1, col).Value = ""
                        .Columns(col).Hidden = True
                        col = col + 1
                    Loop
                End With
            End If
        End If
    End Sub
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  2. #2
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Control range in a line chart by hiding/unhiding data columns using data validation li

    Hi Nate, it's not quite working. I changed the headers of the "Data" columns to the "YYYY - QQ" format and also the table to the left and they match exactly. I pasted your code into the VBA "worksheet" module. When I change either Start or Stop lists I receive this error message: Run-time error '1004: Method 'Range' of object'_Worksheet' failed". And the title line of the code is highlighted when debugging. Any suggestions to fix it? Thanks again. David

+ 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. Replies: 2
    Last Post: 10-16-2014, 08:57 PM
  2. Hiding 'zero' values from the chart data range
    By dmbroekhuysen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-08-2014, 07:58 AM
  3. Replies: 2
    Last Post: 02-04-2014, 03:33 PM
  4. [SOLVED] Chart does not update when hiding/unhiding columns
    By Snoopy2003 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2012, 07:51 AM
  5. help with hiding and unhiding a chart based on data
    By elquesito in forum Excel General
    Replies: 0
    Last Post: 05-18-2011, 02:44 PM

Tags for this Thread

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