Setting x-axis crosses with VBA

I have a chart that at the moment has a vertical range from -70 to
-92. I'm trying to set the horizontal axis to cross at the minimum
value, in this case -92. Using

With ActiveChart.Axes(xlValue)

     .Crosses = xlMinimum

It crosses at -70.

If I use xlMaximum I get the same thing.

Using
            .Crosses = xlCustom
            .CrossesAt = -92

it crosses at -92. however, I need it to be dynamic so the x-axis
labels stay below the data field.

Am I using xlMinimum in a place it cannot be used?

Any help will be appreciated.
0
12/14/2008 4:01:03 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
496 Views

Similar Articles

[PageSpeed] 8

In the format axis dialog, there is no way to specify that you want the axis 
to cross at the minimum, only the maximum. I've long felt this to be an 
oversight.

I assumed there was no way to specify maximum via VBA as well. After reading 
of your dilemma, I looked it up in help, and sure enough, xlMinimum was 
listed as one of the choices. Wow, I'd never noticed that, in over 10 years 
of Excel VBA.

But when I tried it, it worked just like xlMaximum. You'll have to use the 
custom option. By the way, it's not necessary to set .Crosses = xlCustom, 
just use .CrossesAt = SomeValue, and the xlCustom thing happens for free.

What I usually do to ensure the axis will cross at the minimum no matter 
what, is enter a ridiculously negative number, in your case, -9.99E300.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Robert H" <robert.hatcher@l-3com.com> wrote in message 
news:a09e0d53-8eec-457e-b806-fccf0aab9f48@h5g2000yqh.googlegroups.com...
>I have a chart that at the moment has a vertical range from -70 to
> -92. I'm trying to set the horizontal axis to cross at the minimum
> value, in this case -92. Using
>
> With ActiveChart.Axes(xlValue)
>
>     .Crosses = xlMinimum
>
> It crosses at -70.
>
> If I use xlMaximum I get the same thing.
>
> Using
>            .Crosses = xlCustom
>            .CrossesAt = -92
>
> it crosses at -92. however, I need it to be dynamic so the x-axis
> labels stay below the data field.
>
> Am I using xlMinimum in a place it cannot be used?
>
> Any help will be appreciated. 


0
jonxlmvpNO (4558)
12/14/2008 5:52:37 AM
I thought something was odd about xlMinimum being available in the
object but not not an option in the chart scale options.  Thanks for
the workaround that is essentially the same thing.  the code generates
several charts based on different data types. Ill probably use IF THEN
to set the "ridiculous" minimum appropriately.
Thanks Again
Robert

On Dec 14, 12:52=A0am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> In the format axis dialog, there is no way to specify that you want the a=
xis
> to cross at the minimum, only the maximum. I've long felt this to be an
> oversight.
>
> I assumed there was no way to specify maximum via VBA as well. After read=
ing
> of your dilemma, I looked it up in help, and sure enough, xlMinimum was
> listed as one of the choices. Wow, I'd never noticed that, in over 10 yea=
rs
> of Excel VBA.
>
> But when I tried it, it worked just like xlMaximum. You'll have to use th=
e
> custom option. By the way, it's not necessary to set .Crosses =3D xlCusto=
m,
> just use .CrossesAt =3D SomeValue, and the xlCustom thing happens for fre=
e.
>
> What I usually do to ensure the axis will cross at the minimum no matter
> what, is enter a ridiculously negative number, in your case, -9.99E300.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/
> _______
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:a09e0d53-8eec-457e-b806-fccf0aab9f48@h5g2000yqh.googlegroups.com...
>
> >I have a chart that at the moment has a vertical range from -70 to
> > -92. I'm trying to set the horizontal axis to cross at the minimum
> > value, in this case -92. Using
>
> > With ActiveChart.Axes(xlValue)
>
> > =A0 =A0 .Crosses =3D xlMinimum
>
> > It crosses at -70.
>
> > If I use xlMaximum I get the same thing.
>
> > Using
> > =A0 =A0 =A0 =A0 =A0 =A0.Crosses =3D xlCustom
> > =A0 =A0 =A0 =A0 =A0 =A0.CrossesAt =3D -92
>
> > it crosses at -92. however, I need it to be dynamic so the x-axis
> > labels stay below the data field.
>
> > Am I using xlMinimum in a place it cannot be used?
>
> > Any help will be appreciated.

0
12/14/2008 3:55:47 PM
Reply:

Similar Artilces:

Y axis box size incorrect in Excel 2003
I am having a problem; the y-axis box size does not display the entire text. I cannot change the size of this box. This only happens for the y-axis, not for other axis labels or the chart title. I have tried adding spaces followed by a period to fool the box and this works in Excel, but when I copy the chart to a Word document, the full text, including the final period appears. Hi, Details and suggestions can be found on Jon's page. http://peltiertech.com/Excel/ChartsHowTo/AxisLabelCutOff.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "lcare...

Adding a Reference in VBA
In order to run the below code what is the Official exact name of the library I should have added/checked in my Reference table? TIA, Sub Tester1() Dim fso As Object Dim fldr As Object Set fso = CreateObject("Scripting.FileSystemObject") Set fldr = fso.GetFolder("C:\Documents and Settings\Jim\My Documents") MsgBox "You have " & fldr.Files.Count & " Files in the Excel Formulas Folder" End Sub microsoft scripting runtime But then you could change your code to: Sub Tester2() Dim FSO As Scripting.FileSystemObject Dim FLDR As Scripting.Folder ...

How do I set up my Hotmail account in Outlook when I have a DSL l.
By setting up a new account using most likely the POP settings. You will need server information from your DSL provider... in fact, they usually have very simple instructions to follow as to how to set up Outlook to check your email. It is different for each service provider as they will have different incoming and outgoing mail servers. "lahnpease@hotmail.com" <lahnpease@hotmail.com@discussions.microsoft.com> wrote in message news:9C26E59E-F2EF-45F8-BE9A-567ED99C4857@microsoft.com... > lahnpease@hotmail.com <lahnpease@hotmail.com@discussions.microsoft.com> ...

Converting to an efficient set of tables
I was handed down an Excel file consisting of 47,000 rows and 20+ columns of data. In each column, there is an enormous amount of repetition. The file is over 20 MB and runs slowly. Because of reasons beyond my control at work, I cannot use MS Access to handle this data. So, I would like to create several tables containing the unique values that repeat so frequently and essentially structure the data like I would in Access with various relationships between fields. How can I (at least somewhat) automate this task to replace the cell values with references to another cell on another s...

Trouble on Double Axis
Hi everyone ! I work on a chart which consists of two axis: Volume & Units. Excel allows me to show them together only if one is "Bar" chart type and the other is a line type. I need to show both of them with the "bar" chat type on the double axis. Any ideas? Thanks, Hande Hande - Don't let Excel dictate the type of chart you get, you can define any kind of combinations you want. In general you can double click on a series, and on the Axis tab, choose Secondary, and it will be plotted on the secondary axis (as long as you have at least one series per...

Formatting Axis
How do I format an axis on a chart so when choosing Auto it doesn't default to minimum 0 but actually a number closer to the minimum number in my selected range for the chart? Thanks in advance Hi, Auto means Excel decides, and for certain types of charts that will always be 0. So the answer is you can have it both ways Auto and not Auto at the same time. You can either manually enter the minimum or you can write VBA code to do it for you. If this helps, please click the Yes button Cheers, Shane Devenshire "jag" wrote: > How do I format an axis on a chart so when ch...

OPOS Printer
HI, I need to set the RecLetterQuality attribute to true for my OPOS receipt printer. I found the section for Receipt Formats in the Store Manager part of Microsoft POS, but there does not seem to be a LetterQuality selection. Is it here and I'm not seeing it? If not, there needs to be. The OPOS spec says RecLetterQuality is initialized to False after enabling. Our printer requires a darker print out for some applications (scanability), which it gives you when RecLetterQuality is True. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to th...

set a default value for a cell ?
I want to set a default value to a cell or set of cells which might be edited by the user? I have a column which is used by a person to enter values. But A default value should appear. Could you please help me? see help on data validation or debra degliesh's page http://www.contextures.com/tiptech.html "Jeethu" <Jeethu@discussions.microsoft.com> wrote in message news:5A34C3B6-EF14-40AB-9CC6-E590AED4B6D8@microsoft.com... >I want to set a default value to a cell or set of cells which might be >edited > by the user? > > I have a column which is used by...

How to set up multiple users with same email account
I currently have the following set up on my XP Home SP3 using Outlook Express 2 user accounts on XP (mine and my wife's) 3 email accounts - all from one ISP (pop3) - (one for me only, one for my wife only, one for both of us) The address book is shared out to all email accounts. As Outlook Express is not supported in Windows 7 and I am not too keen on Windows Live Mail, I was thinking of moving over to Outlook 2003 (which I also have). Is it possible to set up the same construct I have with OE using Outlook 2003 and how is such a thing possible. I have seen articles ...

How do I cancel email redirect when there was no rule set up?
My emails are automatically redirected form my office email to my home, both computers use Office 2003. I need to cancel this redirect, but there is no rule in my rules folder. I did this years ago so I can't be certain how it was set up. Any suggestions? "Carla" <Carla@discussions.microsoft.com> wrote in message news:EA58A8FC-1E12-47A3-AF1A-ADFB2765867D@microsoft.com... > My emails are automatically redirected form my office email to my home, both > computers use Office 2003. I need to cancel this redirect, but there is no > rule in my rules fold...

Object reference not set to an instance of an object
Hi, I have a problem with my Progressbar Form. the following code runs correctly. But... progressForm = new fclsProgress(); progressForm.percent = 0; Thread doSomethingThread = new Thread(delegate() { for (int i=0; i<100; i++) { if (progressForm.cancel == true) break; progressForm.percent = i; // ... do something } MyNamespace.MyClass.ActiveForm.Invoke((MethodInvoker)delegate { this.progressForm.Close(); }); } doSomethingThread.Start(); progressForm.ShowDialog(); But when I minimize my program, It is stoped by the following error me...

How would I set up a machine work log, to keep track of repair?
...

how do I set up an annual calendar on one worksheet
I need a tempplate to set up on one worksheet an annual calendar with days of the week, months and date numbers. I havae to set up a calendar that has recurring dates e.g. evry 3rd Weds in Feb, 4th Tues in may etc ANy help would be appreciated on all or some of the above start here: http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=calendar -- Gary''s Student "Ian Dee" wrote: > I need a tempplate to set up on one worksheet an annual calendar with days of > the week, months and date numbers. > I havae to set up a calendar that has recurring ...

Transfer files and settings from Ultimate 64 to Pro 32 fails
I wanted to transfer my files and setting from my W7 Ultimate 64 bit to my Black Friday Netbook running 32 bit Pro with Easy Transfer. Seems that doesn't work. Are there any work arounds or must I manually transfer what I need? -- Sprocket in Texas Hi, Sprocket. It should work. It worked for me. HOW are you using WET? With a transfer cable? Or a thumb drive? A network? RC -- R. C. White, CPA San Marcos, TX rc@grandecom.net Microsoft Windows MVP Windows Live Mail 2009 (14.0.8089.0726) in Win7 Ultimate x64 "Sprocket" <Sprocket@discussions.mi...

Can you set up another email account in an early v of Outlook?
I need to set up an second email account in Outlook. This would be fine if they had the latest version but its not and on a Win95 machine. So can this be done? I can't even put Outlook Express v6 on as it doesn't like 95. Thanks Jen wrote: > I need to set up an second email account in Outlook. This would be > fine if they had the latest version but its not and on a Win95 > machine. So can this be done? I can't even put Outlook Express v6 on > as it doesn't like 95. > > Thanks What version of Outlook? ...

Having 3 .set files with different ISV's and custom form modifications
Currently we have 2 seperate SQL instances for 2 different environments. We want to be able to create one environment that has 3 companies, with one .set file with customizations, and alot of ISV's; one company with customizations and 2 ISV's; and create one new company with a plain vanilla GP only 2 ISV's no customizations; We currently have the 2 instances with the first 2 set up that way and now looking to consolidate into one environment with the company dropdowns together. Will this be a pain to manage or is it even possible?? ...

.NET 2.0 XML Validation
I am wondering if I am alone with this problem. Using VS 2005, I must validate an XML file via a Schema and it works well. When I get the schema exception and check the LineNumber and LinePosition properties, they are always set to 0 <=== Here are 2 ways I used to validate the XML always getting LineNumber and LinePosition = 0. Any hints would be greatly appreciated. ================================================================== XmlReaderSettings settings = new XmlReaderSettings(); settings.IgnoreWhitespace = false; settings.Schemas.A...

"OleObjectBlob" & "Unknown COM" errors when importing VBA package
I created a VBA package in Dynamics GP 10, which opens and reads from an Excel file. When I try to import the package on another computer, I get "OleObjectBlob" and "Unknown COM" errors referrring to my user form I created. If anyone knows how to get around these errors, please let me know thanks, Lorraine It would be good to know what versions of GP are you running both in your dev environment and the environment you are trying to import the package into. It would also be beneficial to know the *exact* error messages you are receiving. Best regards...

Personal setting files for Home and Student?
Where can I locate the personal setting files for MS Home and Student? I am formatting and reinstalling MS H&S. Will need the same for Excel and Powerpoint also. See http://www.word.mvps.org/FAQs/General/FilesToBackup.htm. In addition, if you're using Office 2007, you may have customized files in these folders: %appdata%\Microsoft\Document Building Blocks and subfolders %appdata%\Microsoft\UProof %appdata%\Microsoft\Word\Startup There may be similar advice for the other Office programs, so ask in their forums too. -- Regards, Jay Freedman Microsoft Word MVP ...

Best practice for setting default font settings in Word 2007
Could anyone tell me what Microsoft's "Best Practice" is for a company to change the default font settings in Word 2007? For example, if I wanted to change the default font to Times New Roman for all new documents and push this out to the company - does best practice remain the same as in Word 2003? Or has it changed with the inclusion of the default document settings and themes? Thanks so much! I am not sure how you did it in Word 2003, but in either case, I would not rely on customizing the Normal.dot template. You should create separate templates for your...

x.400 address showing up in GAL
In Outlook when you look up contacts in the Global Address List the email field shows x.400 addresses instead of SMTP addresses. Is there a way we can change it? These are internal users but we do not want to show x.400 addresses. ...

Guide to setting up Resources and using Auto Accept Agent
I am setting up an exchange 2003 system and was looking for guidance on how to create resources that can be booked - like conference rooms as part of meetings. Also, do I need to use Auto Accept Agents to complete the process of people booking these resources? Thanks Please could someone advise on this? I am hoping to find someone with information ASAP. "Makor" wrote: > I am setting up an exchange 2003 system and was looking for guidance on how > to create resources that can be booked - like conference rooms as part of > meetings. Also, do I need to use Auto Accept...

Frequent file corruption in the documents and setting area
Since a month or so, I have frequent messages 'corrupted file, run chkdsk' after booting. The files concerned are ALWAYS in the 'documents and setting' directory, often temp files, or Norton 360 files. I am quite positive that the disk itself is sane (only 1 raw sector is reallocated in SMART). Any idea from what area could the problem come ? I do have units up to K (2 physical units), and only this directory ever get corrupted on G (system XP pro) Thanks -- Jean Paulo "Jean-Paulo" <jean.paulo@free.fr> responded in News item ...

6 items line-column chart on 2 axis
I would like a bar chart to show (in 2 bars) the forecast dollars and actual percentage, where the forecast $$ use the right axis and the actual % use the left axis. The final 4 indicies will be charted using a line chart. When I create this chart my bars stack instead displaying beside each other presumable because each bar uses a different axis. Is there anyway to show the bars in separate columns? Thank you. http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "...

Setting company-wide holidays on exchange/outlook calendar
Is it possible to set holidays globally on exchange so that it will appear on everyone's Outlook calendar, instead of setting them for each user? Thanks, John ...