Posts by slickity

    I have a very long email here that resulted from a series of Replies between me and a colleague. I now need to keep it for future reference. However, I wanted to remove all the email header blocks. Each one starts with "----" and is 5 lines long. Is there some way to do this?

    Below is the code I have so far, but I don't know how to assign a string from the document, so the lines starting with "curLine = " won't let it compile.

    How is assignment done?


    Re: Transfer Word string to XL

    Selection.HomeKey Unit:=wdStory

    Here's a tip for figuring this stuff out yourself. Start recording a macro, do whatever it is you want to do, then stop recording and look at the resulting code. In your case, I'm sure you knew that Ctrl+Home brings you to the beginning of a document, so record yourself hitting that key combo, then look at the code and you should see the above line. That's what I get in Word 2000 anyway, assuming it's the same in other versions.

    I'm trying to hide a selection of several column ranges with a macro. When I run the following code, instead of hiding the ranges indicated, like it would if you did this manually, it instead hide all columns right from B to AG, which are not even the outside limits of my selections. I have panes frozen on this sheet. Could that be affecting this?

    Sub HideSystem()
        Selection.EntireColumn.Hidden = True
    End Sub

    Re: Can't change text format

    Well, I've fixed some problems, and found others.
    The problems I'm having right now are that my Outline Numbered list, which I set up using Styles, doesn't advance numbering automatically.

    So, if I have two Headin1 entries in a row, they both number as "1." instead of "1." followed by "2."

    Can anyone tell me why this is?

    Re: Can't change text format

    The problem overall seems to be with getting my numbering styles to actually work. I don't know if these are just glitchy in Word 2000, but they just seem to ignore when you make changes to them.

    I have a 10-page document where I've used a lot of headings and sub-headings in a cascading manner. The first few lines, however, I can't change the format of. They're stuck in Heading9, but should be Heading 1. However, now matter what I do to them, they just always say Heading9. Anyone encountered anything like this before?

    I'm using Word 2000.

    Re: Simplified direction to Automatically run Macros when a cell changes

    I just tried your code and it still doesn't seem to be working. Nothing happens when I change that cell.

    Is it right to put the code you just provided in ThisWorkbook? Do I need any other code anywhere, besides the ConstructionHours sub code in a user module?

    As for starting a new thread, I searched for threads on this problem, found this one, tried out the code that was posted, and had a question about a modified version of that code. In cases like that, I generally post within the original topic for the sake of keeping forums organized. Seems to make more sense to me. If I started a new thread, I'd want to link back to the original anyway to explain where I got the code. Does OzGrid have a rule about situations like that?


    Re: Simplified direction to Automatically run Macros when a cell changes

    Can this be used to monitor changes to a named range, and run a macro if it changes?

    Here's what I have, and it's not working.

    In the "ThisWorkbook" module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        With Target.Cells(1, 1)
            If .Address(0, 0) <> "BudgetDollars" Then Exit Sub
            Call ConstructionHours
        End With
    End Sub

    And in a normal module:

    Sub ConstructionHours()
    MsgBox "Hello"
    End Sub

    I don't see the "Hello" when I make a change to the named range BudgetDollars.

    Re: Arrow buttons move scroll bars

    Correct you were. I rarely use Scroll lock and Num Lock, so I never think of them. In fact, I think this is the first time I've actually seen what Scroll lock is for.

    Re: Deleting Sheets w/o Confirm

    Works perfectly. Thanks.
    I have another question. Is it possible to create the new workbook and have it have a name already? As opposed to just "Book1"?

    I tied ActiveWorkbook.Name = thisName, but it gave an error saying it couldn't change the read only property.

    I want to delete sheets using a macro without getting the sheet confirmation box. The reason is that I have a macro that is run from one book and creates a new book and copies one sheet from the original to the new.
    So, I don't want the standard Sheet1, Sheet2, and Sheet3 in the new book.
    Right now I just have my macro set up to delete these extra sheets, but I get the sheet delete confirmation.
    Is there any way to either not get this confirmation, or to create a new completely blank workbook?

    I don't think this question goes against the forum rules because I don't think this is actual security feature as such. However, if it does, just let me know, and in that case my question is just can you create a new workbook without the standard three blank sheets.


    Re: Paste special method failed...

    I have the same error message with some code of mine. The line that gets highlighed is

    Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

    Here's the complete code:

    Re: Print code in color

    I deffinitely could do that, but I just printed the project, and it topped 20 pages of code, which I don't want to have to go through and manually recolor.

    This is not a question directly on VBA I guess, but I'd like to print a bunch of code in color. This is because I have a workbook with probably 50 or so Subs in 12 modules, and I've made some many changes over the past month or so that I've lost all sense of organization to the project, so I'd like to get it all in front of me.

    Anyway, when I print to a color printer from VBA, I still get black and white.
    Is there any way to get color?

    Re: Best fit print area

    Nevermind. The problem with the formatting code was the line

    s = s + Rows(i).Width

    Should be

    s = s + Rows(i).Height

    I think that solves all my problems for now, at least related to the original question of this thread.

    Thanks everyone for your help.

    Re: Best fit print area

    OK, I've realized this thread has gotten way off topic from where I started initially. The code I have now to do the conditional printing is as follows, and was provided here by user royUK.

    Following is a modified version of his code:

    This works fine for my needs, I'm just posting it so others can see.
    Now I want to return to my original question about formatting for printing.

    The following is the code I have, which is supposed to give a best-fit number of pages for the height of the document. However, it is unpredictable. Sometimes it will make good calls, like three pages or so for a document that realistically is around 3 pages long. Right now however, it attempts to cram about a 5-pager onto one page. Can anyone see a problem with this code?
    Some of the individual rows in my sheet grow in height as the user fills in info, but I don't think that explains the problem here.