Replies

Once again WOPR comes through

I was unclear of whether your post was a question seeking understanding, or a plea for help to stop it.

I’ll not add any value on the understanding front but have spent a lot of time stopping strangeness in Word.

I find that the first time a wierd problem happens it has usually been caused by an unexpected real-time event that will never repeat. Understanding the problem is not really necessary or cost-effective (I only track down an issue if it repeats for a different document)

Easy fixes are usually one of the following methods of shedding meta data or Word data structures:
– CUT whole content and PASTE to a new document
– save file as RTF file and reload
– save file as old word version (e.g. Word 95) and reload

I struggled to see how your hyperlinks were being set. Could I just clarify my understanding

Your combo box contains a list of names (e.g. “Ankle”) which are text strings (not hyperlinks)
Based on the choice you wish to link to some other place
You can identify the choice – but are now wanting to jump to elsewhere.

Your problem seems to be to link using a hyperlink. If one existed in a cell such as A1 then
range(“a1”).hyperlinks(1).follow
statement would have worked.

To create a hyperlink programatically you’d need a
ActiveSheet.Hyperlinks.Add
statement to have created it in the first place.

I didn’t see one in your code. It would be easy enough to add via VBA (if you knew the target address), or, depending on your application, in advance.

If it were added in advance, it has to live in a cell, which your combo box routine would then have to ‘follow’

I’m getting into assumption space here but, I assume you’re thinking of the the names in the combo box as links – at best they will be the link’s “TextToDisplay” value and will need to be used in a lookup of the hyperlinks object for a match, then followed by a follow.

I wasn’t sure whether you were aiming to achieve it via a linked cell, or directly in VBA.

For a linked cell, just use the ListRange & LinkedCell properties – they seemed to work fine

Alternatively, in VBA I used Listbox.Value which worked fine also.

I’ve had similar problems — I agree – somthing is messing with it.

I found that setting all anchors visible helped a little (ocassionally).

Another trick is to position the character cursor prior to where you think the image is, and then move it forward one right-arrow at a time. The cursor moves past one character at a time. You eventually have the cursor jump over the graphic (which Word thinks of as a character) and gain insights from that.

Last gasp is simply to delete the graphic, AND the offending paragraph (at this point a phantom picture often appears).
Reenter the paragraph, position the cursor within the paragraph and re-insert the picture.

It rather depends on what you’ve already checked, and your level of expertise with Word as to where to start with a diagnosis.
I’ll start with the obvious Word things – it may be telling Grannie how to suck eggs – apologies in advance.

I assume that this is extra white space, rather than embedded extra space creating characters such as tabs or spaces? [spacing characters become visible if you select Tools > Options> View > All]

I’d check paragraph formats after that. Format > Paragraph and look for space before and space after.

After that – Styles and character fonts

Lastly the actual printer definition. Is the problem printer dependent (i.e. were these OK documents with another printer?

This could be checked by configuring a different generic text printer. At that point, I’d be going back to the manufacturers Web site for upgraded drivers or specific support.

Andrew

Folks

I’ve appreciated all the help. The problem is now fixed!

I discovered that after the re-install Office wanted to do some strange ‘installing’ every time it ran.
To cut a long story short, I eventually discovered that it only happend for my login code, and not for the rest of the family’s.

Accordingly, I created a new user code, and blew away the old one.

Peter

Thanx – a really useful set of information. I can report some interesting facts but no solution yet.

Running Excel in /safe mode fixes the problem but /automation doesn’t. I removed all XLB’s on my system and that hasn’t helped either.

Peter

I looked at your sheet, and hope I’m understanding it right.

I wondered why what you’re doing is not much more simply achieved by a bit of restructuring and a Pivot table.

I run a spreadsheet with transactions and transaction dates (organised as a list). To make it produce reports similar to the ones you seem to be asking for I create a new column or two of simple of ‘tag’ fields (e.g. QUARTER, and LEDGER) which can be easily calculated off the source data.

The resulting structure can then easily be summarized as a Pivot Table. I’d assume that a data table a bit like that would work for you too.

Steve

Thanx for the interest.

Yes – it happens for all spreadsheets {new; old; small;big}, after all restarts.

Since I posted, I totalled removed the whole of the Office XP application and reinstalled it.
Fascinatingly, it remembered all my old settings (why am I unsurprised) and is still exhibiting the problem.
My home machine is an XP multi-user machine and the problem always occurs on my account – not others.
My current theory it is some registry Key related to my profile. System restore is my next planned trick.

Andrew O

ken
I suspect that it has become way too complex for easy analysis.
My assumption is that VLOOKUP decides that (even with array formulae) its job is to find the first item and then stop.

I had difficulty understanding why it had to be done in quite that way.
What was wrong with doing the VLOOKUPS in cells B8:B13 and then using the resulting single answers in a simple construct? It would certainly be easier to debug.

I enjoyed discovering your method of rangenaming CCs – nice one (I always wondered how to make robust definitions)

Andrew O

Yes you can do that.
TOC uses styles for each of the heading levels.
Accordingly, you just modify the style – “TOC 1” to be a capatilized font form
Andrew O

I found, with sheet design, you could usually get away with using a sheet activate event rather than a change event.
For instance, I use this technique to ‘auto-update’ all pivot tables as users go to view them.

I suggest that so long as the filter is another sheet, an activate event is fairly clean and tidy – unprotect, refresh filter, protect

Andrew Ollivier