• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically change data source for X axis

Dan Kennedy

New Member
I have a spreadsheet that I am pulling data off a server and creating different graphs. I am trying to expand the functionality of the spreadsheet so that if the 1 day & 1 minute option is chosen all the graphs have a minute scale X axis. However when I go to monthly and annual time frames I need the X axis to switch to a date format. I have created named ranges for the minute scale and the day scale but I cannot figure out how to get excel to automatically change this axis format. Anybody have any ideas, I am not stellar with VBA but I am willing to try it.

Thank you,

Dan
 
Dan Kennedy
Without Your sample spreadsheet ...
You would check this possible.
There are two set of datas B2:C32 and D2:E32.
Select any of [B1:E1] and chart will change (if needed)
 

Attachments

  • DanKennedy.xlsb
    60.2 KB · Views: 5
vletm, thank you for the reply, I tried to attach a spreadsheet but I cannot make it small enough to conform to the site. If you can send me an email at gdkennedywork@gmail.com I will send you the file.

In this sheet I have two named ranges I would like to use for the X axis on all the graphs.

When the Period length is set for 1 day the X axis needs to use the named range MCTIME - and be formatted for time. When the period length is set for 30 or 365 days, the X axis needs to switch to T3DATE and be formatted for Date.

Thank you for the help.

dan
 
Dan Kennedy
Do You means those ... K- and C-columns?
... and same formats as used with 9th row of those columns?
Screen Shot 2017-05-01 at 20.09.09.png Screen Shot 2017-05-01 at 20.09.27.png
If so, then You got already answer
... did You checked my file?
 
Okay thank you. Can you tell me how you are switching them are you using visual basic? I am not following how you are making them switch I guess.


Thank you,

dan
 
Dan Kennedy
Maybe I found shorter way,
but because I cannot test it,
as You know,
You have to do it Yourself.

Add few lines of code to Your 'Sheet1' Code-page as below ..
( If ... EndIf )
and after that it should work as You want ...
Code:
        MsgBox ("Done")
    End If
   
    If Target.Address = "$J$17" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With Sheets("Index").ChartObjects(1).Chart
            With .SeriesCollection(1)
                Select Case Target.Value
                    Case 1
                        .XValues = "MCTIME"
                    Case Else
                        .XValues = "T3DATE"
                End Select
            End With
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If

End Sub
 
Dan Kennedy
You didn't save my previous version of code ... hmm?
It seems that those needs Workbook's name too ...
Use this part of code.
Code:
    If Target.Address = "$J$17" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With Sheets("Index").ChartObjects(1).Chart
            With .SeriesCollection(1)
                Select Case Target.Value
                    Case 1
                        xxx = "MCTIME"
                    Case Else
                        xxx = "T3DATE"
                End Select
                .XValues = "'" & ThisWorkbook.Name & "'!" & xxx
            End With
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
Ps. No data ...
 
vletm,

I really appreciate the help, not sure why the data isn't showing up, I thought I had it all copied over.

I will see what I can do. I changed the code as shown above, didn't change the axis source though.

Thank you,
 
Dan Kennedy
Data: I do not need data!
Xaxis: Xaxis source change as You wanted as You can see below!
That part of code change ONLY Xaxis as You wanted.
How did You check that 'didn't change the axis source though'?
Here it works!
Screen Shot 2017-05-02 at 22.41.36.png

if Period Length <> 1 then as below.
Screen Shot 2017-05-02 at 22.42.52.png
 
Back
Top