adjust x-axis according to value in dropmenu.

September 27, 2013 at 13:01:33
Specs: Windows 7
How im thinking.
Hey.

Im trying to make a more useful diagram for work.

In the y-axis i have a price list and in the x axis i want a percentage of revenue. And i want this to be "scale-able".

I.e. from a dropdown bar you can choose 40-80%. Then i want the diagram to only show from 40-80%

How can i do this?


See More: adjust x-axis according to value in dropmenu.

Report •


#1
September 29, 2013 at 15:23:01
I'm not exactly sure what you are doing with your graph, so I created my own and then came up with a solution that you might be able to adapt to your needs.

This suggestion needs some work, but I'm tossing it out there just to see if it comes close to what you are looking for.

I started with a simple line graph with this data so that I got a curved graph like yours. Then in C2 and C3 I added Data Validation Drop Down lists containing the months from A2:A13.

	  A	        B                C (Drop Downs)	
1       Month      Running Total     Month Range    
2    January	        23              July   	
3    February	        53             October
4    March	        90	
5    April	       135	
6    May	       187	
7    June              247	
8    July	       314	
9    August	       388	
10   September         470	
11   October           559	
12   November          656	
13   December          760	

Using a Worksheet_Change macro, the graph can be changed to use the months chosen by the Drop Downs. This is pretty basic, since it doesn't check to see if the End Month (C3) is earlier than the Start Month (C2), but that's easy to fix if this is the sort of what you are looking for.

In addition, in my case, I know the graph name is Chart 6, so that is hard coded into the macro.

How it works is that the code uses the .Find method to determine what Rows the Months chosen in the Drop Down are in. It then uses those Rows to build the SourceData range for the chart.

If you really want to use a text string like 40% - 80% in your Drop Down, I supposed the code could extract the values (40 and 80) using some text functions (LEFT, MID, RIGHT) and then use .Find to find those values in your data table.

Let me know what you think of this so far.


Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$C$3" Then
'Get Start Month Row From Drop Down in C2
      With Range("A2:A13")
       Set startMonth = .Find(Range("C2"))
        startRow = startMonth.Row
'Get End Month Row From Drop Down in C3
       Set endMonth = .Find(Range("C3"))
        endRow = endMonth.Row
      End With
'Use Row numbers to build SourceData Range for Chart 6
    ActiveSheet.ChartObjects("Chart 6").Select
       ActiveChart.SetSourceData _
         Source:=Range("$A$" & startRow _
                    & ":$B$" & endRow)
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 30, 2013 at 06:15:27
I, think you are on the right track. But unfortunately my excel skills doesn't go so deep. I have attached a link to my spreadsheet. Could you please give me detailed instructions?

As to you question about the text string 40 - 80 % its no problem to have those isolated in two different drop downs if that simplify it.

https://www.dropbox.com/s/y69hhqqge...


Report •

#3
September 30, 2013 at 06:55:31
I will not be able to attempt access to your file until this evening.

In the meantime, what is it about my post that you don't understand? Is there a specific task that I can help you with so that you can try a few things today?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 1, 2013 at 04:28:57
Well basicly i dont know where to start. Do you know about any tutorial files maybe?


Report •

#5
October 1, 2013 at 07:55:54
I looked at your spreadsheet last night but it was really late and I didn't get a chance to do much with it.

I was able to click on the Series in your chart (the curved line) and modify the formula that appears in the formula bar, so I think I understand what you are trying to do. By changing the cell references in the formula, I can modify the graph to show only a portion of the range from the Ark2 sheet.

However, there is something that I don't quite understand...

In your workbook, the graph charts the data from Ark2!B2:C87, showing 0% to 85%. If I change that range in the formula bar to B42:C82, the graph will start at 40% and end at 80% which is what you used in your example. However, in the Screen Capture video from your OP, you select L2:S2. I don't see how that range is related to what you are trying to do. What is the significance of that range?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 1, 2013 at 12:12:35
I want to be able to scale graph as i want based on the input in dropdown menu (basicallly h7:h14).

Column L to S is just something i tried on the way that didnt work.

When i for example choose 20-40% on the x-axis the y axis has to only include the values attached to 20-40%. Now that would be C22:C42 on x-axis and B22:B42 on y-axis.


Report •

#7
October 1, 2013 at 12:49:26
Right...that is what I need to figure out how to do.

I tried recording a macro while changing the formula that creates the graph, but it didn't record the new formula. I'll work on it tonight.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 1, 2013 at 17:20:08
Try the workbook found here:

https://www.dropbox.com/s/0rbldg06a...

There's a Drop Down next to the chart. Select a percentage range to change the chart.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
October 2, 2013 at 00:15:17

I get this error when trying to change value.

Link to screenshot

message edited by Gunders


Report •

#10
October 2, 2013 at 03:57:11

It looks like the string FullSeriesCollection was added in the 2013 edition, im currently working on 2010. So i changed it to only SeriesCollection and that worked.

What if i want to change the place where the dropdown menu is, i want it on i.e. =Side1!B44 what do i change?

And when i for example choose the x-axis to scale from 0-40, the autoscale function on the y-axis is very bad. Can we improve this? See link for screenshot.

https://www.dropbox.com/s/ozdqc6o5z...


Thank you very much for the help :)


Report •

#11
October 2, 2013 at 04:11:49
There's no workbook at that Dropbox link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#12
October 2, 2013 at 05:23:42
try this.

https://www.dropbox.com/s/ozdqc6o5z...


Report •

#13
October 2, 2013 at 11:15:12
I'll have to play with that tonight.

I don't do a lot of chart related macros, so this'll be a "learn as I go" process.

If you are willing to try some stuff on your own, it couldn't hurt. Basically what I would do is record a macro as I create a chart and/or edit the chart settings (like the y axis limits) and see what code is produced by the recorder.

Then I modify the code that recorder produces to get it to do what I want. That method is a great way to learn to write macros in general. Let the recorder create the shell (which is typically very bloated) then clean it up and customize it. At least you don't have to learn any VBA language before you can even get started...the recorder writes a lot of it for you.

This VBA Tutorial might help also:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
October 2, 2013 at 16:00:57
Try this version.

The drop down is in Side1!B44

Since we are using a Worksheet_Change macro, the code had to be moved to the Side1 sheet module.

I also added code to scale the Y axis based on the Low End Percentage chosen. The code will find the value associated with the Low End Percentage and round it down to the nearest 1000.

Let me know what you think.

https://www.dropbox.com/s/40w1igtf0...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question