Formatting Text Fails In 97 But Works In 2003

  • Re: Formatting Text Fails In 97 But Works In 2003


    It's passworded...


    You're wasting my time.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    I've certainly not applied a password - nor is it passworded when I open it?


    I've checked the 'protect worksheet' option and it's off etc!?


    try nothing as a password (ie just click ok?)

  • Re: Formatting Text Fails In 97 But Works In 2003


    My apologies... :footinmo:


    I confused your app with someone elses I was looking at.
    Now I guess I'm obligated to try to figure it out... darn.


    OK, gimme a sec.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    He doesn't know... he said he didn't have 97.


    I'm suspecting it might have to do with the CreateObject vb script. What exactly was the purpose of the macro? Perhaps we can recode it without the vb script.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    Perhaps change the title to "Formatting Text Fails In 97 But Works In 2003".

    Brad.

  • Re: Formatting Text Fails In 97 But Works In 2003


    hi there - yeah I know the title could have been clearer - but the damn thing interferes with everything I write! ;)


    Basically in 2003 you click the button and it'll remove all the square characters, formatting etc you can see and essentially tidy it up.


    In 97 it doesn't even appear to ask if you want macros enabled like it normally does when it detects VBA attached and you click the button and it gives an error of 'function/sub not defined' (apologies but I'm trying to translate a non-technical user's translation of what they see!)

  • Re: Formatting Text Fails In 97 But Works In 2003


    I rewrote your code cleaner to perform a more manual filtering of the strings. I don't see why this wouldn't work in 97. I think the problem had to do with that CreateObject line, but I can't confirm it (I don't have 97 around either).


    PS... what's wrong with these people anyway? They're using a 10yr old app and they expect it'll just work forever?


    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    Quote from omnikron32

    hi there - yeah I know the title could have been clearer - but the damn thing interferes with everything I write! ;)


    Personally, I have two issues with it myself...


    1. It should probably allow the word "Excel"
    2. It should allow series of all caps

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    Aaron: heh! yeah I've got more than two issues with it! ;)


    hmm I tried you code and it doesn't appear to work - even in 2003?


    Doesn't appear to detect the characters - does this work for you?


    Yeah - well you think 97's old - you should see the people using 97! ;)

  • Re: Formatting Text Fails In 97 But Works In 2003


    Worked just fine on my XL2003


    Maybe you're not installing it correctly? I did notice you were attaching all your code to the worksheet objects.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Formatting Text Fails In 97 But Works In 2003


    yeah I figured I may well be being stupid!


    Would it be possible for you to upload your version so I can see exactly how you've formatted/dropped the code?

  • Re: Formatting Text Fails In 97 But Works In 2003


    argh!


    apparently on 97 it gives:


    Run-time error '1004' Unable to get the SpecialCells property of the Range class


    And on debug it highlights this line:
    Set ProcRange = ProcRange.SpecialCells(xlConstants)


    Did specialcells exist in 97? Is there another way to get this working - it seems like it's so close to working! :rolleyes:

  • Re: Formatting Text Fails In 97 But Works In 2003


    The problem with the original code is that xl97 (VB5) does not have the Replace function.


    You could use SUBSTITUTE instead, ie:
    change

    Code
    sText = Replace(sText, "<br><br>", Chr(10))


    to

    Code
    sText = Application.Substitute(sText, "<br><br>", Chr(10))


    Anyway your current problem is due to a bug in xl97 and CommandButtons TakeFocusOnClick property.
    See here:http://support.microsoft.com/d…n-us;177527&Product=xlw97
    Set the TakeFocusOnClick Property to False of the Command Button
    or put in as 1st line of code:

    Code
    Activecell.Activate

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!