Jump to content
Can't remember your login details? Read more... ×
Sign in to follow this  
nodnerb

Changing min/max scale - VBA Excel 2007

Recommended Posts

Yo.

 

I'm trying to write some code that will change the Minimum and Maximum values on the x-axis on a chart to values located in cells on a worksheet.

 

The x-axis is a date / timeline.

 

Here's where I'm at:

 

Option Explicit


Private Sub axis_value()
	With ActiveSheet.ChartObjects("Chart 1").Chart


		With ActiveSheet.ChartObjects("Chart 1").Activate
		   ActiveChart.Axes(xlValue).Select
		   ActiveChart.Axes(xlValue).MinimumScale = Sheets("ProjectInformation").Select
		   Range("C2").Select
		   ActiveChart.Axes(xlValue).MaximumScale = Sheets("ProjectInformation").Select
		   Range("C3").Select
		   ActiveChart.Axes(xlValue).MajorUnitIsAuto = True
		End With

	End With
End Sub

The chart is on its own sheet called "Gantt".

 

The 2 x number (date) values are on a sheet called "ProjectInformation" in cells C2 (.MinimumScale) and C3 (.MaximumScale)

 

I keep getting a runtime error "The item with the specified name was not found"

 

The debugger stops on this line

With ActiveSheet.ChartObjects("Chart 1").Chart

Any ideas why?

 

Cheers

 

:)~

Edited by nodnerb

Share this post


Link to post
Share on other sites

Some validation wouldn't go astray, like checking that ActiveSheet isn't nothing. That routine will only function as you'd expect if Chart 1 is on the currently active sheet. If it is not, then the call to ActiveSheet.ChartObjects("Chart 1").Chart would fail.

 

You could do the validation like:

If ActiveSheet Is Nothing Then 
  Debug.Print "Whoops.  No Sheet!"
  Exit Sub 
End If 

If ActiveSheet.Name <> "Gantt" Then 
  Debug.Print "Whoops, wront active sheet."
  Exit Sub
End If 

If ActiveSheet.ChartObjects("Chart 1") Is Nothing Then 
  Debug.Print "I can't find my chart :("
  Exit Sub 
End If

You could call

Sheets("Gantt").ChartObjects("Chart 1")

Not sure why you have that outer With block.

 

Hopefully that made sense. Never done any Excel automation!

Edited by kikz

Share this post


Link to post
Share on other sites

Option Explicit


Private Sub axis_value()
	With ActiveSheet.ChartObjects("Chart 1").Chart


		With ActiveSheet.ChartObjects("Chart 1").Activate
		   ActiveChart.Axes(xlValue).Select
		   ActiveChart.Axes(xlValue).MinimumScale = Sheets("ProjectInformation").Select
		   Range("C2").Select
		   ActiveChart.Axes(xlValue).MaximumScale = Sheets("ProjectInformation").Select
		   Range("C3").Select
		   ActiveChart.Axes(xlValue).MajorUnitIsAuto = True
		End With

	End With
End Sub
Well you're trying to assign a range object to a simple (pressumably long integer) type. The .Select method only tells Excel to move the cursor to the object you're naming. You actually more interested in assigning a property, in this case a cell .Value.

I assume that you recorded the macro and are trying to work out how the program did it, which is generally the quickest way to solve a problem you haven't yet come across.

 

Is the chart embedded on the same sheet as the cells?

I haven't worked much with charts programatically however the way I would tackle the problem is to write the code into the subroutine

Worksheet_Change
this way whenever you change the value in those cells the chart will update.

 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
	If Target.Address = "$C$2" Then
		ActiveSheet.ChartObjects("chart 1").Select
		ActiveChart.Axes(xlCategory).MinimumScale = Target.Value
	End If
	If Target.Address = "$C$3" Then
		ActiveSheet.ChartObjects("chart 1").Select
		ActiveChart.Axes(xlCategory).MaximumScale = Target.Value
	End If
End Sub

If you like I can send you the test workbook i just mocked up.

Edited by galdarian

Share this post


Link to post
Share on other sites

Hey thanks galdarian.

 

I was hoping to have the cell values on a different worksheet. That would be better I assume (so users can just enter the information on a different page to the chart).

 

I'll PM you my email addy.

 

Cheers

 

:)~

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×