Replies

Thanks Legare.

I stepped through the code (which doesn’t take long). It is definitely beeping on the line Application.DisplayFullScreen = True (and the line that sets it to false for that matter).

Ok, I’ve worked it out. While I was typing this I ran a few tests. It turned out that the beep is the Maximize Window sound that I have defined under Sounds in control panel. If I turn that off, there is no beep. If users have sounds on, they will get beeps. Not a lot I can do about that.

Thanks for your time.

Jon

You could use Datedif to determine the number of months and divide by 12, rather than calling it twice.

Jon

Rather than doing this in code, you do have the option (at least you do in 97, I have not caught up to 2000 yet) of defining the range name with a dynamic formula (am I right in thinking that we’ve had threads on this before?).

eg in the range name definition you have a formula something like
=offset($a$20,0,0,counta($a$20:$a$100),1)
which returns a range that is as big as the number of filled cells in a20:a100. This works fine as long as you have contiguous data.

Jon

My 2 cents (Australian = 1 cent US) worth:

The following is a random wishlist of things that I try to pass on to the people I work with when answering queries:

Beginners (once you got past This is a row etc):
– how to edit a formula in the formula bar
– absolute vs relative and what happens when you copy a formula
– strings vs numbers and why they can sometimes be hard to tell apart
– Paste Special options (eg Values, formats, multiply)
– spreadsheet design, eg don’t hard code constants put them in a cell, think about the layout before you start, label everything in sight, put titles on all columns, add notes as you go (be nice if I followed my own advice)
– range names and why you should use them
– the selection total in the bottom right hand corner
– basic formatting (font, number especially custom formats)
– why merged cells are a pain in the *** and center across selection may be a better idea
– don’t be afraid to customize your toolbars
– printing, including page break preview and how to move page breaks.
– inserting and deleting rows and columns

Advanced:
– array formulae and what they are good for
– subtotalling
– conditional formatting
– data validation
– how to learn VBA by recording macros (you can teach VBA until you’re blue in the face, but unless people are prepared to get their hands dirty you’re wasting your breath)
– where the VBA help is
– what an object is, plus properties and methods
– the little black square on the bottom right corner of the selection rectangle (no, I don’t know the proper name for it) and what it is good for
– charts

Hope that is of some use

Jon

I’m not sure you can tell the pie graph to hide labels for blank/zero amounts.

However, could you change your worksheet so that the data runs vertically rather than horizontally, ie one row per furniture suite rather than column? Then you could use filtering to hide the zero rows. Apologies if I’m stating the bleeding obvious.

Jon

It looks like you have a worksheet that was copied a large number of times, and has a ‘codename’ that has got bigger than Excel 2000 can handle. I tried this in Excel 97 (which is all I have) and couldn’t get an error, despite having a sheet codename with 22 ones in it. Looks like a ‘feature’ of 2000.

Question: Does the file still open ok in 97?
Suggestion: If so (assuming you still have 97 available somewhere), open it in there, and then go to the Visual Basic Editor and change the code name. I don’t know how familiar you are with VBA and the editor, so let me know if you need some further instructions.

HTH

Jon

The best way I know of sharing macros is to put them in an add-in. Then anybody can read them from a shared drive.

To create an add-in from your personal.xls is easy. Open it up and then do SaveAs, and select the file type Microsoft Excel Add-in (*.xla) (It’s right down the bottom of the list, of course).

If you save that to a shared drive, then users can access it by going to the Tools menu, selecting Add-ins and then Browse. Once they have found your add-in and selected it, Excel will ask something like “Copy to Add-in Library?”. If you say no, then they will always access the copy of the add-in on the shared drive, which makes it easy to update with new macros. If you say yes, then the add-in gets copied to C:program filesmicrosoft officeofficelibrary, and updating with new versions becomes a pain.

All macros in the add-in will be available to all workbooks that are open. However, the add-in macros will not appear in the Tools>Macros list. Generally with add-ins being distributed around the office/country, we get the add-ins to create their own menus for the macros available to users.

Does that help?

Jon

One question, if I may. Where are the connections stored, and how did you delete them (ok, 2 questions)?

Thanks

Jon

John

You get the display alert because it occurs after this event code has been processed (put a msgbox at the end of your code and see the order that the messages come up in).

When the user says no, what about setting
thisworkbook.saved=true
and then the pesky message should go away.

Jon

PS How’re things in Utah?

Is it just you who has this problem, or does it occur on other machines? If it’s just you, it may be a setup problem on your machine.

If it’s everybody, then have you looked under Tools>References in the Visual Basic Editor. Is xlquery.xla listed under there? If so, why and do you need it?

Questions, questions, questions.

Jon

I’m working in 97 SR2, so 2000 might have fixed this but…

I don’t know about linking a text box to a point, but you can modify the data labels for a series. It is a bit laborious as you have to change all of them.

What you do is right click on the particular series, and select Format Data Series.
Click on the Data Labels tab, and click on Show label.

This will show the x-axis value for each point next to it. You can then click on a point, pause and then click again (ie two left clicks as opposed to a double-click). This will let you edit each data label. The formatting is non-existent and you have no control over the line wrapping, but it is linked to the data point.

HTH

Jon

About the only way that I could think of is to add some code to the userform’s resize event that sets Application.height=userform.height, application.left=userform.lett, etc.

If you want to move the application, you might have to put the same code in the userform mouseup event, which is the only way I can think of to test for the userform having been moved.

Hope that helps

Jon

Not sure I understand what you are trying to do. Can you give some more details, please?

Jon

Having had a look at this, I think you’re going to have to go with an API call (though I have no idea which one).

You seem to be trying to work out the length of the text in the text box in points, which will depend on the font used and the characters in the text box.

You could do it roughly by working out the number of 0’s that will fit in the text box, and checking whether TextLength is greater than that number. Apart from that, I’m afraid I have no better suggestions.

Jon

If you already had Excel open, try closing it before doing the merge. If you’ve already tried that, or that doesn’t work, it then starts getting tricky.

Jon