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?

Thanks.

Sub clearEmailHeaders()
Dim curLine As String
curLine = Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend


Do While (curLine <> "Stop")


    If curLine = "----" Then
        Selection.MoveDown Unit:=wdLine, Count:=4, Extend:=wdExtend
        Selection.EndKey Unit:=wdLine, Extend:=wdExtend
        Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.TypeText Text:="--------------------------------"
    End If
    
    Selection.TypeParagraph
    curLine = Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend
    
Loop


End Sub

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.

Re: Hide series of Columns Ranges

nope, that was my code. But for some reason yours does the job. Don't know why, but thanks.

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?
Thanks.

Sub HideSystem()




    Range("G:H,M:N,S:T,Y:Z,AE:AF").Select
    Selection.EntireColumn.Hidden = True
    Range("C8").Select
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 must be missing something here. I don't see a Worksheet module. Where should this be located? I see ThisWorkbook, lumped in with the sheets in the workbook, then seperately, the user modules.
I'm using Excel 2000. Is this a newer feature you're talking about?

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

In your code, should I be passing in some value or range?
If not, then shouldn't the following give me a MsgBox if I make any change?
Because it's not giving me anything right now.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "test"
End Sub

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

Hi,
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?

Thanks.

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.
Thanks.

For some reason right now, instead of moving the active cell, my arrow keys instead move the scroll bars on my sheet. How do I change this setting back to normal?

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.
Thanks.

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.

Thanks.

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:

Sub detachReview()


    Dim originalWB As String
    Dim newWB As String
    Dim newWBName As String
    
    originalWB = ActiveWorkbook.Name
    
    newWBName = "Costing Review - OI " & Range("'Start'!C5").Value


    Application.ScreenUpdating = False
    
    Workbooks.Add
    newWB = ActiveWorkbook.Name
    
    Application.WindowState = xlMinimized
    
    Windows(originalWB).Activate


    Sheets("Project Review").Activate
    Call formatReview
    
    Range("A1:J117").Select
    Selection.Copy
    Windows(newWB).Activate
    Sheets("Sheet1").Activate
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Name = "Costing Review"
        
    Sheets("Sheet2").Select
    Selection.Delete
    Sheets("Sheet3").Select
    Selection.Delete
    
    Windows(originalWB).Activate
    
    Application.CutCopyMode = False
    Call deFormatReview
    Range("A1").Select
    
    Windows(newWB).Activate
    
    Application.ScreenUpdating = True
End Sub

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?
Thanks.

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:

'---------------------------------------------------------------------------------------
 ' Module    : ThisWorkbook
 ' DateTime  : 19/10/2005 19:11
 ' Author    : Roy  Cox, edited to suit specific needs of this work book by Simon Silk
 ' Website   : www.excel-it.com
 ' Purpose   : stop print to run code. Then resume print
 '---------------------------------------------------------------------------------------
Option Explicit
 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True 'stop print
    
    Dim response As String
     
    response = MsgBox("Print just this sheet?" & Chr(10) & "Yes = print just current sheet" & Chr(10) & "No = Print entire workbook" & Chr(10) & _
"Click Cancel to cancel printing.", vbYesNoCancel, "Print What?")
    
    ' If only printing current page:
    If response = vbYes Then
        ' If printing Project Review:
        If (ActiveSheet.Name = "Project Review") Then
            Call unhighlightUserEntryCells
            Call FitPages
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            Call highlightUserEntryCells
        Else
        ' If printing any other sheet:
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        End If
    ' If printing entire workbook:
    ElseIf response = vbNo Then
        Call unhighlightUserEntryCells
        Call FitPages
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Call highlightUserEntryCells
    End If
    
    Application.EnableEvents = True
     
End Sub

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.

Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = 117 ' Sheet currently only extends to row 117. Modify this as sheet is modified.
    
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 10, 0)    'number of pages long To Do
    
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$117"
    
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = i
    End With
End Sub