Replies

Rajesh,

Can’t wait to see the macro for this. I’ve always seen this as a weak point in word.

My (very) old word processor (in Unix) also had one other feature that I really want that seems somewhat related (you mentioned several placements as “defaults”). Not sure if it’s in your macro but thought I’d ask.

For figures that you will NOT wrap text around (ie, wrapping is above/below), placement of the text box determines what text comes after the text box on the same page.

For example, suppose my text box, when all is said and done, is 4″ high. If the preceding text ends at 4″ down the page, then the text box and the subsequent text (1″ or so) will be on the same page.

But suppose my preceding text ends at 6″ down the page (or I insert text early in my doc that adds 2″ on the page with the text box). Then my text box gets bumped to a new page and the following text follows it on the new page – creating a space at the bottom of my previous page.

Is there some automated way to position the text box AND fill any (significant) white space left as a result of pushing the text box to a next page? Of course, this would only be an option since some might want to not re-order the text box and the following text.

Thks.

Fred

Rajesh,

2 other thoughts that might help with customizing the footnote separator:
– if you have a separator in the style you want (point size, specific character, etc) but want to position it higher or lower on the base line, you can insert an ADVANCE field before it to move the subsequent “text” (ie, the separator) up or down some number of points (the switches for the ADVANCE field)
– if you’re using tabs, as you thought about, it wasn’t clear if you were using the tab leader. I wasn’t sure if this is where you were going with this so I thought I’d mention it. To my knowledge, though, you’re stuck with a limited set of leader types.

Fred

Legare,

I too had a problem like this with Excel 97 on Win 98. In a project I was doing, I wrote a subroutine and included the Application.Screenupdating statements. That worked fine. I then wrote another routine and it didn’t work at all (lots of blinking as it went thru 7 items and blinked each time). When I brought this over to my client (Excel 2000 on Win 98), same results.

I may have to go back and look to see if anything on this thread solves the “blinking” problem.

Fred

In addition to Legare’s lesson on Proper functions (thanks), the way I approached this one time used Word.
1. from excel, copy the cells you want to convert to the clipboard.
2. paste them into Word; this will create a table with the number of rows and columns the same as your Excel selection
3. select the entire table.
4. either choose a or b depending on your rqmt for conversion:
a. use SHIFT+F3 to cycle thru a limited number of conversions
b. select Format | Change Case which will give you a larger set of conversions; choose the one you want and click OK
I think either of these give a little more flexibility than Excel’s PROPER function.
5. with the Word table still selected copy to the clipboard
6. back in Excel, select the upper left cell of your original selection and paste the clipboard. this will replace the entire range with the “table” from Word, which just happens to be the same size as your original selection from step 1.

proper use of proper tools – of course, if you don’t have Word…

Fred

Dave,

Hope the following is not too late. I don’t think you need VBA for this.

1. view header footer
2. click on word art on the drawing toolbar. follow the prompts to create “Draft” in whatever format you’d like.
3. when you’re finished in word art and back in Word header footer, copy the word art object to the clipboard
4. using paste special, paste the clipboard using the picture choice back into the header and footer and position it where you want on the page.
5. delete the first version (the word art version)
6. right click the result of #4 and select the Format Picture (last item). Select the tab for picture. Under Image control, click the drop down for color and select watermark. this will adjust the brightness and contrast controls. (you can play with these further if the item is still too dark). You may also have to select the tab for wrapping and select None.
7. go back to your text

the reason for deleting the version in Word Art is that word’s capability for formatting word art (try right clicking it to see in step 3) won’t let you adjust the contrast. But when you copy and paste it back as a picture, word doesn’t know where it started from so it’s treated like any other picture. Now you can adjust the color or the contrast/brightness.

You can use any picture and apply step 6. WordArt gives nice bold text if that’s what you want.

Hope this helps.

Fred

The other posts on this thread give some good advice on overprinting with the EQ o field and the list of symbols that can be achieved using CTRL+whatever.

However, I use another approach that is more general but a bit of work. That uses the ADVANCE field. This field allows you to move the insertion point left, right, up, down, etc. (see the switches when you Insert | Field). You move in units of points. For example, a switch of u7 would move the insertion point up 7 points above the current base line of type (a point is 1/72 of an inch).

A few suggestions when using this approach:
– try working on a “character” (defined here as the combination of symbols plus the ADVANCE fields) in a separate document by itself
– increase the zoom so it’s easier to see what you’re doing
– make sure that you include the compensating motions. For example, if you move the insertion point left and down, you have to move it back right and up. Unfortunately, the compensating motions are not equal to the original motions. For example, if you moved left 7 points to get a symbol under a letter, then that symbol took up space and you don’t just move right 7 points. However, the up/down compensating motions are probably the same.
– when all is said and done, save the entire creation as an autotext entry or an autocorrect entry so it can be used elsewhere. You definitely don’t want to have to do this twice for the same combination.

I always thought about trying to create a library of such creations but there were 2 obstacles to this:
– time
– no need

Have fun. Curious to know how you make out.

Fred

Andrew,

We came to the same conclusion about not needing to convert to numeric values. I mentioned that as an after-thought at the end of my previous email.

But being able to do mathematical operations to, for example, find the margins of 2 successive times is useful. Not part of the original request but useful.

I’m not much of a swimmer and haven’t swam in some time. I jog. In 34 seconds, on a good day and with the wind at my back, I can jog .1 mile. Don’t ask how long I can keep that up for (not much). I like the long slow runs.

fred

I would just add 1 thing to what Andrew said (or perhaps it’s a slight modification of what he said).

After getting rid of the “Y” with Replace and adding the “0:” to the sub-minute times to get actual numeric time values, you can still have the “Y” appear in the cell with the times if that’s needed for some reason. The key word is “appear”. It would NOT be part of the value of the cell or else, as stated, the value is a string and not a number.

After you’ve done what has been suggested, select the resulting cells. Format them by Format | Cells with the number tab. Select the last item which is custom format. A custom format will be preselected (General) but you want to define a new one. Enter the following format in the box at the top where you can type as follows:
mm:ss.0″Y”

Here you do want to include the quotes. What this does is keep the value but add a Y to what appears in the cell. Think of it as clicking the $ formatting on the toolbar when you want a number to appear as dollars.

But let me also suggest something else. There is no reason why cells have to be numeric to be sorted as has been implied. Excel will sort text strings using the order associated with the code of the character. So you would still need to add the “0:” as was suggested to the sub-minute times. This is so that a 36.24 (36.24 seconds) sorts in ascending order ahead of a 1:00.07 (1+ minute) time. But with that task done, Excel will be perfectly happy to sort the list as strings with “0:36.24Y” sorting ahead of “1:00.07Y”. As strings, you can not do mathematical operations like adding 2 of these cells but you can sort them.

Enough of sorting. It’s Sunday morning and time to go to breakfast.

Fred

Hi,

As suggested, you can delete a custom format by going to the Format | Cells | Number tab and selecting Custom. Then select the format you no longer need. This works in Excel 97 too.

Note there are about 36 custom formats that come with Excel. You can’t delete these. If you select 1 of these, the Delete button will remain inactive.

Depending on the length of the custom formats you’ve already defined, Excel will store about 180-260 custom formats. It seems like the space for these formats is governed by the number of characters taken up by them (some fixed length area somewhere), not the number of formats.

fred

Sherry,

One of Excel’s math operators is the exponentiation operator. Simply put, it raises a number to a power. Even more simply put, it multiplies a number by itself until you’ve done it the number of times given by the power.

For example, 2 to the power of 3 is 2x2x2=8.

In Excel, you’d write a formula as 2^3 (the symbol between the 2 and the 3 is shift on the 6 key). This operator has even higher precedence than multiply * and divide /.

Now applying this to your problem:
– $D$10:$D$11 are the sum of what you want raised to the power
– $D$9 is the constant adder
– put the percent growth in some cell, as suggested – call it $E$1

Then I’d set up the following – I’ll use col A and B, starting in row 1, but you can use whatever you want;
YEAR AMOUNT
1 =(SUM($D$10:$D$11)*((1+$E$1)^A2)) + SUM($D$9)
2 = [just drag the formula from B2]
Note the only thing that changes in the above formula as you drag it is A2 changes to A3 to A4 to … Since A2 references the year, the year then becomes the “power” in the formula, which means you are multiplying the % up to the power given by the year.

Hope this helps (and hope I didn’t screw up on the parens).

Fred

Geoff,

did a search on “Change source” and found my first post on this. Here’s the id# info for my post:
Re: removing links [Post#: 9285 / re: 696 ]

Not sure if this is the one you were referring to (there was the “Hidden links” thread and some other – maybe it was the above).

Fred

Hi,

I’ve had that problem also. You were referred to another thread that discussed this. In that thread, there was referral to yet an earlier thread.

I had posted a very simple solution to the problem. It may have been in the earlier of the above 2 threads but that no longer seems to be available (I got a page not found error).

If this is truly a link problem (as opposed to a Named Range), then go to the Edit Link dialog. Select the link that you want to get rid of. Click on the Change Source button. Navigate to the workbook you’re in – yes, the one that’s giving you the problem. Select that workbook as the one you want to change the source to. The link will disappear from the set of links.

Worked for me in Excel 97.

Fred

KT:

You’re absolutely right – sorry about that. I am so used to working in ppt on this. In ppt, you can just start typing once you’ve selected the shape. That’s what I tried to do in Word and, of course, could not. But the “Add Text” item is on the context-sensitive menu, as you stated. You still can’t define the text anchor point as you can in ppt so the original issue of alignment still remains.

You could still adapt the idea of adding a 1 cell table to the shape, once you’ve clicked Add Text, and do the alignment in the table. Of course, the visual alignment will depend on how closely you can fit the table to the shape.

Fred

Hi,

there are several solutions to your question. Not sure if any are “perfect” but here goes.

1. If your shape is an Text box, then add a 1-cell table into your Text box and make it as big as the box. Your text will go in the table but you can turn off the borders of the table so you don’t see it. You may also have to adjust the internal margins of the text box to almost 0 so that you can stretch the table to be as big as the box. For horizontal centering, use the toolbar as suggested. For vertical centering, open the Tables and Borders Toolbar (it has a button on the Std Toolbar) and choose the vertical centering.

2. If your shape is other than a text box, you’re in a bit of a pickle. Word does not (in 97; enhanced in 2000?) allow you to type in such a shape. On the otherhand, powerpoint 97 does. So here’s 2 alternatives based on ppt97:
– if Word 2000 has the same capabilities as ppt97 as far as other non-text box shapes, then you can type in the shape when it’s selected. R-click the shape and choose Format Autoshape. Choose the TextBox tab (yeah, this is not a text box but ppt treats the shape as having a virtual text box). At the top, there is a drop-down for Text Anchor point. This will allow you to do what you want in terms of alignment.
– If Word 2000 doesn’t have ppt97 capabilities, use ppt97 and select the shape and do all the things just mentioned. Then copy the shape to the clipbd and paste it into word. Be careful. You need to do a paste special and select either of the picture options. If you do a regular paste or paste special with MS Drawing Object, the text in the shape gets moved around (and even changed font on my PC). The drawback to this is that you can not edit the text in Word.

Hope some of this helps

Fred

Geoff,

Thks for the quick response.

As far as the misplaced $ sign, that’s bcs my Excel formula didn’t have $ in all the places (only in front of numbers) and I just added them in during the posting. Obviously a typo.

As I said in my original post, I not totally up on array formulas. I think I was using CTRL+Enter to get the extra col of 1’s, with 6 cells in that col selected. So the formula was evaluated for each of the 6 rows. However, I only entered the formula once but CTRL+Enter propogated the formula to all the selected cells.

With your proposal, I only had 1 cell selected. When I hit SHIFT+CTRL+enter, I get the sum as I wanted. With only CTRL+Enter and the 6 cols selected, I get 1 or 0. If I don’t use the sum, but with 6 cols selected, I also get six 1’s or 0’s with CTRL+Enter. With just plain Enter, I get only 1 number.

So I’m a little confused about the differences. Help!

Thks.

Fred