Replies

If the first cell is in row 2

=”TCL/” & TEXT(ROW()-1,”000″)&”/05″

will do it – drag down as needed

Charlotte
Thanks for the reply. Yes the button is on the form I’m trying to close.
The Dialog form simply asks for a date, and a couple of mutually exclusive radio buttons.
The OK button has a Click event defined that set a couple of global variables and then wanted to “exit” from this form – triggering the dillema I described.
That is, if I do a Close in the click event, I trigger a lockup later. If I do a .visible=false I seem to be OK.

Do I need to close the form via a later piece of code, or can I leave it to be automatically cleaned up at quit time?

Charlotte
Thanks for the reply. Yes the button is on the form I’m trying to close.
The Dialog form simply asks for a date, and a couple of mutually exclusive radio buttons.
The OK button has a Click event defined that set a couple of global variables and then wanted to “exit” from this form – triggering the dillema I described.
That is, if I do a Close in the click event, I trigger a lockup later. If I do a .visible=false I seem to be OK.

Do I need to close the form via a later piece of code, or can I leave it to be automatically cleaned up at quit time?

Thanks Phil – no it doesn’t hurt more.

I already knew how to do text replacement and paragraph marker replacements (although I always use the replacement string XYZZY in memory of the game ‘adventure’)

I guess my confusion was that my post was an attempt to solve the posted problem – I don’t normally use a font change either – but it was the only way I could see to make it work to flag apostrophies – if Alan has another method for this case, I’m keen to learn it – I’m guessing there is some wildcard variation.

Thanks Phil – no it doesn’t hurt more.

I already knew how to do text replacement and paragraph marker replacements (although I always use the replacement string XYZZY in memory of the game ‘adventure’)

I guess my confusion was that my post was an attempt to solve the posted problem – I don’t normally use a font change either – but it was the only way I could see to make it work to flag apostrophies – if Alan has another method for this case, I’m keen to learn it – I’m guessing there is some wildcard variation.

My head hurts. I tried the find string you indicated and didn’t find anything. I had a look at wildcards in help.
On a first read I thought that the syntax should have been (^$)'(^$) for the find string – but on a test, couldn’t make the find work unless I set wildcards, and then the ^$ wouldn’t work.
My head hurts even more . Canya give me a dummies guide to this please.

My head hurts. I tried the find string you indicated and didn’t find anything. I had a look at wildcards in help.
On a first read I thought that the syntax should have been (^$)'(^$) for the find string – but on a test, couldn’t make the find work unless I set wildcards, and then the ^$ wouldn’t work.
My head hurts even more . Canya give me a dummies guide to this please.

There is a convoluted method that will work 99% of the time.

Step 1 – identify all apostrophies and convert their font colour e.g. to blue

Step 2 – identify and replace all single quotes with double quotes where font colour is still automatic

Step 3 – change font colour blue back to automatic

In step one, to search for apostrophies look for {Any letter}'{any letter} – this gets most apostrophy forms other than leading and training ones.
Note that these will then appear as spelling errors = e.g. “tis will be identified as an error.

I think you’re only dealing with the registry for this data. A quick review of its contents didn’t make me confident that it would be an easy swap.

So long as your accounts are only limited to a few dozen, I’d suggest the long way may be the quick way.

I think you’re only dealing with the registry for this data. A quick review of its contents didn’t make me confident that it would be an easy swap.

So long as your accounts are only limited to a few dozen, I’d suggest the long way may be the quick way.

Hans – a short-cut

After changing any field as you describe – just press {F9} – it does the toggle for you directly and recalculates for good measure.

It is working (to get the cell at the top).

I’d pick your cell simply doesn’t contain the tested for upper case letters

Try using Msgbox activecell.value rather than the IFs

I thought about it some more

I had a reason to use deactivate, and refresh all caches, rather than activate and refresh the workbook – it works more generally in the case I had

Sheet A had the data
Sheet B had a pivot table off A
Sheet C had more data
Sheet D had a pivot table of C
Sheet E had a summary extract of pivot data from Sheets B and D

In the instance where I’d updated data in Sheet A and clicked straight to sheet E, no pivot tables were involved on the sheets I was accessing.

I thought about it some more

I had a reason to use deactivate, and refresh all caches, rather than activate and refresh the workbook – it works more generally in the case I had

Sheet A had the data
Sheet B had a pivot table off A
Sheet C had more data
Sheet D had a pivot table of C
Sheet E had a summary extract of pivot data from Sheets B and D

In the instance where I’d updated data in Sheet A and clicked straight to sheet E, no pivot tables were involved on the sheets I was accessing.

I like your code so much better than mine
I’d always planned to get around to coding it that way but never did because what I’d coded in my early days worked as it was.
Thanks – Andrew