VBA Borders in Excel 2007

The code below is designed to put medium outline and thin inside borders on 
each page of a mulitpage excel worksheet.  It used the BeforePrint event to 
do this.  It worked in Excel 2003 but in 2007 on a 2 page worksheet when it 
calls Sub MixedBorders the bottom medium border gets erased on the first 
page.  What do I need to change?  Or is this a bug in 2007?

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   FormatBeforePrint
End Sub

Public Sub FormatBeforePrint()
   Dim startRow As Long
   Dim endRow As Long
   Dim startColumn As Integer
   Dim endColumn As Integer
   Dim hpb As HPageBreak

   'set up the starting conditions
   startRow = 2
   startColumn = 1
   endColumn = Range("A1").CurrentRegion.Columns.Count

   Range("A1").Select
   Selection.End(xlDown).Select
   Range("A1").Select
   For Each hpb In ActiveSheet.HPageBreaks
      If hpb.Type = xlPageBreakManual Then hpb.Delete
   Next hpb
   For Each hpb In ActiveSheet.HPageBreaks
      endRow = hpb.Location.Row - 1
      MixedBorders Range(Cells(startRow, startColumn), Cells(endRow, 
endColumn))
      startRow = endRow + 1
   Next
   endRow = Range("A1").CurrentRegion.Rows.Count
   MixedBorders Range(Cells(startRow, startColumn), Cells(endRow, 
endColumn))
End Sub

Public Sub MixedBorders(ByRef rng As Range)
   Dim edge As Integer

   rng.Borders(xlDiagonalDown).LineStyle = xlNone
   rng.Borders(xlDiagonalUp).LineStyle = xlNone
   'It fails here on 2nd pass
   For edge = xlEdgeLeft To xlEdgeRight
      With rng.Borders(edge)
         .LineStyle = xlContinuous
         .Weight = xlMedium
         .ColorIndex = 0
      End With
   Next edge
   If rng.Columns.Count > 1 Then
      With rng.Borders(xlInsideVertical)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = 15
      End With
   End If
   If rng.Rows.Count > 1 Then
      With rng.Borders(xlInsideHorizontal)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = 15
      End With
   End If
End Sub 

0
Leo
11/18/2009 8:06:21 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1901 Views

Similar Articles

[PageSpeed] 42

Don't know why your bottom border is not applied with your 2 print pages, 
but try this instead

Public Sub MixedBordersNEW(ByRef rng As Range, _
                Optional bDelAllBdrs As Boolean)
Dim i As Long

' If rng is single row/col skip error doing xlInsideHoriz/Vertical
   On Error Resume Next

    If bDelAllBdrs Then
        With rng.Parent.UsedRange.Borders
            For i = 5 To 12
                .Item(i).LineStyle = xlNone
            Next
        End With
    End If

    rng.BorderAround xlContinuous, xlMedium, , vbBlack

    For i = xlInsideVertical To xlInsideHorizontal
        With rng.Borders(i)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = RGB(192, 192, 192)  'grey
        End With
    Next

End Sub

Sub Test()

MixedBordersNEW Range("B2:H10"), True '.CurrentRegion

End Sub


In Excel 2007 probably better not to use colorIndex (above also good for 
2003)

In passing, no need to do all that select suff in your code as posted

Regards,
Peter T



"Leo" <news-account@cox.net> wrote in message 
news:%23RALFYCaKHA.5976@TK2MSFTNGP05.phx.gbl...
> The code below is designed to put medium outline and thin inside borders 
> on each page of a mulitpage excel worksheet.  It used the BeforePrint 
> event to do this.  It worked in Excel 2003 but in 2007 on a 2 page 
> worksheet when it calls Sub MixedBorders the bottom medium border gets 
> erased on the first page.  What do I need to change?  Or is this a bug in 
> 2007?
>
> Option Explicit
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>   FormatBeforePrint
> End Sub
>
> Public Sub FormatBeforePrint()
>   Dim startRow As Long
>   Dim endRow As Long
>   Dim startColumn As Integer
>   Dim endColumn As Integer
>   Dim hpb As HPageBreak
>
>   'set up the starting conditions
>   startRow = 2
>   startColumn = 1
>   endColumn = Range("A1").CurrentRegion.Columns.Count
>
>   Range("A1").Select
>   Selection.End(xlDown).Select
>   Range("A1").Select
>   For Each hpb In ActiveSheet.HPageBreaks
>      If hpb.Type = xlPageBreakManual Then hpb.Delete
>   Next hpb
>   For Each hpb In ActiveSheet.HPageBreaks
>      endRow = hpb.Location.Row - 1
>      MixedBorders Range(Cells(startRow, startColumn), Cells(endRow, 
> endColumn))
>      startRow = endRow + 1
>   Next
>   endRow = Range("A1").CurrentRegion.Rows.Count
>   MixedBorders Range(Cells(startRow, startColumn), Cells(endRow, 
> endColumn))
> End Sub
>
> Public Sub MixedBorders(ByRef rng As Range)
>   Dim edge As Integer
>
>   rng.Borders(xlDiagonalDown).LineStyle = xlNone
>   rng.Borders(xlDiagonalUp).LineStyle = xlNone
>   'It fails here on 2nd pass
>   For edge = xlEdgeLeft To xlEdgeRight
>      With rng.Borders(edge)
>         .LineStyle = xlContinuous
>         .Weight = xlMedium
>         .ColorIndex = 0
>      End With
>   Next edge
>   If rng.Columns.Count > 1 Then
>      With rng.Borders(xlInsideVertical)
>         .LineStyle = xlContinuous
>         .Weight = xlThin
>         .ColorIndex = 15
>      End With
>   End If
>   If rng.Rows.Count > 1 Then
>      With rng.Borders(xlInsideHorizontal)
>         .LineStyle = xlContinuous
>         .Weight = xlThin
>         .ColorIndex = 15
>      End With
>   End If
> End Sub 


0
Peter
11/18/2009 10:06:51 AM
Reply:

Similar Artilces:

Strange Excel Problem.....
Hi there, We are facing one very strange excel problem in our organization.We have purchased new IBM thinkcenter 851 desktops for our Managers.We have installed Windows 2000 professional as operating system and Office 97 as office in that pcs.Now we are facing one very strange problem....Normally when you click on "New" shortcut( I mean right click anywhere and select "new" option),you will find "Microsoft Excel Worksheet" as shortcut listed in that option and when you select it new excel worksheet will be opened.But here,whenever i click on "new"...

Excel remembers! How?
I type in "FRENCH"(no quatos) in cell A1 The I type in ENGLISH in cell b1 When I get back to cell a2 and type in the letter F the etxt is completed and it says FRENCH How does this happen/what is it called/how do you turn it OFF Thanks for the help Ian, It is called "AutoCompletion". To turn it off, go to the Tools menu, choose Options, then the Edit tab. There, uncheck the "Enable AutoComplete for cell values" options. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "ian&qu...

Global Footers in Excel
Does anyone know how to take an Excel workbook that has multiple worksheets and create a footer (say one that lists the page number, the tab name, and the file name), and make that footer a "global footer" such that I don't have to set the footer for some 10 to 20 worksheets when they're practically identical? Thanks. DTP David Right-click on one worksheet tab and "select all sheets". Set up your footer on the active sheet and these settings will be replicated on all sheets. DO NOT FORGET to right-click and "ungroup sheets" when done. What you ...

Read-receipts in Outlook 2007 with shared imap folders
Hi, I have a info@domain.com email address that goes to an imap folder. I have 2 users who have access to that folder. If user 1moves the email to another folder and that email has a read-receipt set on it, then user 2 sends out an automatic email saying that the email was deleted without being read. This happens without the user 2 doing anything. Both users are using Outlook 2007. Is there any way to get outlook to not send read-receipts for certain folders? The user still wants to send read-receipts for her private emails. Any ideas? Is there a better way of setting this up with Outloo...

Excel 2007 Chart SeriesCollection(1).Interior.ColorIndex
I thought I had posted this already, but can't find it. Sorry if it's a duplicate. I have the following code that was written in Excel 2003. Every time I run it in Excel 2007 it crashes. I can't find anything about changing the colors of a series in an Excel 2007 chart. Also, when I try to record a macro to change the chart colors to gain insight, nothing gets written to the macro regarding the color change. Help! Sheets("Chart1").SeriesCollection(1).Interior.ColorIndex = 5 ...

Excel Jululian 04-28-10
Good morning please help can i create an index sheet for all my excels files (XLS) regrads yes george, if thats what you want to do. RegMigrant "George A. Jululian" wrote: > Good morning > > please help > > can i create an index sheet for all my excels files (XLS) > > regrads oh, go on then two ways - build an excel macro what gets the information from windows so can (kind of) keep itself up to date. I am guessing you have no macro background so i offer the much cheaper way:- got to Start, run type "cmd" wi...

excel 2007 installation
Hello: I used to have Excel XP and I install Excel 2007. Problem: I want to ger rid of the personalized features I have in XP and install Excell 2077 with the default parameters as it come from the manufacturer. Please help. Elitor Did you try just uninstalling the Excel XP version first? If so, did that not help? I've not tried this so I'd like to know! "Elitor" <Elitor@discussions.microsoft.com> wrote in message news:8C1B1E5E-0EAA-42EB-8CC2-5A228ADF8578@microsoft.com... > Hello: > I used to have Excel XP and I install Excel 2007. > Problem: I want to ...

Excel Caused Error in VBE6.dll. Excel will Close
When trying to open Excel 2000 (Office Professional) / Windows ME the above message appeared and Excel would not open. Renaming the VBE6.DLL file and then repair and then reinstal Office did not work as the original dated VBE6.DLL file reappeared Has anyone had the sme problem? If so did you find a workable solution? ... and what was it? ...

money 2007 vs. providian ? please help
money wont retrieve info from providian. if i look up providian in the ms money compatibility link as posted by others I get 'direct services' what gives any help? In microsoft.public.money, newby wrote: >money wont retrieve info from providian. > >if i look up providian in the ms money compatibility link as posted by >others I get 'direct services' I don't think so. It looks like "third party". > >what gives > >any help? ...

Cursor highlights the wrong rows in excel?
I find that I have to select 3 rows above the row I want to highlight in excel. This carries forward when selecting multiple rows. My mouse is always about 3 lines above the cells being acted upon. ...

How do I copy a chart made in Excel to Powerpoint?
I have created a number of charts in Excel that I need to export to Powerpoint for a presentation. I also want to turn each graph into a jpeg or pdf file for publication. How do I do this? For sharing charts in Office 2003 or earlier: Using Excel with Other Office Applications http://peltiertech.com/Excel/XL_PPT.html Office 2007 throws some wrenches into the gears. To export charts as image files: Enhanced Export Chart Procedurehttp://peltiertech.com/WordPress/2008/06/09/enhanced-export-chart-procedure/ Use PDF or GIF, which are optimized for line art, not JPG, which is optimized fo...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

Money 2007 ofx inport not working anymore afer MS Acounting Instal
Hi I just installed the 2008 version of Microsoft Accounting and now my Money 2007 bankstatements ( ofx files) don't import into Money 2007 anymore :(. Has anyone seen this problem? Thanks, Matt I know nothing about Microsoft Accounting and the file types it uses but you can see what program is registered for ofx files by checking the folder options under Windows explorer. You can even point it back to "Microsoft Money import handler" by clicking change. Art "guenthmn" <guenthmn@discussions.microsoft.com> wrote in message news:630CEF3D-41AD-48FF-9D7A-26...

download Word file into visio 2007
Anyone know how I can import a normal .doc WORD file which has our schools plan into a trial copy of visio 2007. Was going to us Smartdraw but thought as WORD already in use it would be easy to import into another Microsoft programme. Any assistance would be much appreciated. Thanks Alan Speer Since Word is a text tool and Visio is a diagramming tool, what would be the purpose. You might consider just hyperlinking to the word document instead. al "Alan Speer" <Alan Speer@discussions.microsoft.com> wrote in message news:7EA0022B-9B77-4764-AD35-5D342039D2E7@microsoft...

Pie Chart (excel 2003)
How do I hide the 0% of the pie chart so that they only show the relevant %? If you mean on the pie then double click the data labels and choose the Number tab, choose Custom and enter the following code on the Type line on the right: 0%;[=0]""; -- Cheers, Shane Devenshire "Xueling" wrote: > How do I hide the 0% of the pie chart so that they only show the relevant %? Thanks! It does look better. But is there a way to not show the data lable of those 0% as well? "ShaneDevenshire" wrote: > If you mean on the pie then double click the data labels ...

How can a set an alarm in Excel?
I don't know if this is possible, but I figured I would ask: I have a spreadsheet that I work with that shows the schedules of my students; each different place my student goes has its own row, so a student may have many rows if they have many activities. currently, i auto filter the day column to the current day so i can see what students have activities on that day, and when/where they are. i was wondering if i could set an alarm on the column labeled 'start time' that would pop a window up when any value in that cell matched the system time... is this possible?? thanks, -...

Excel 2007 sorting problem
In Excel 2007, every time I change ranges, and even sometimes when I move away from a range and then come back to an old range, I have to re-enter the sort parameters every time. In Excel 2003, and for some versions before, if I sorted a selected range (say A4:Q54) by Col A Asc, Col G Asc, and Col C Desc then Excel would remember the sort parameters so that if I changed ranges to say A60:Q100 it would keep those same parameters loaded and I could just simply sort my data without having to re-enter the sort parameters all over again. Please direct me to where I can get some help on this issue...

Graphic features of EXCEL..........
Hi, I want to display the table data on Pivot GRAPH from two different sheets. How should we do that? Any advice will be great. Bill ...

Extra prompts from Excel 2007 after saving a CSV file
Here's the scenario: 1. I open a .CSV file in Excel 2007. 2. I make some data change to the file. 3. I save the file. Excel prompts me to use the CSV format again. This is a little annoying, but I answer Yes, I want to keep this format. 4. I try to close Excel. 5. Excel prompts me with "Do you want to save the changes you made to '(filename)?' Yes/No/Cancel. This is INFURIATING. I just saved the darn file. So I click No, thereby training myself to click no when prompted by Excel to save changes, guaranteeing I'll lose work in the future. Does anyone know a way to get E...

Excel text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

saving excel file with it's add in
Hi, I have an Excel file that uses a formula from an add in installed in m computer (*.xla). How can I save the project including the add in in order to use it i another computer that doesn't have the add in. thank -- Message posted from http://www.ExcelForum.com Do you really mean that your workbook uses a function that's in the addin? If yes, I think you have two choices. #1. Take the addin with you (and install it on the other pc). #2. Extract the code/procedure for that function from the addin and put it in your workbook. (If the addin's project is protected, then thi...

Re: Excel Problem
This is a multi-part message in MIME format. ------=_NextPart_000_0046_01C5D5BA.E1DDCCD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have some sheets 1;2;3;4; ETC................. I would like the new page list release with one botton: A1-Nome de sheet(1;2;3;4; ETC) A2-Date(C50:C80) A3-Import (D50:D90) Best regards Marcello "Bob Phillips" <bob.phillips@notheretiscali.co.uk> ha scritto nel = messaggio=20 news:uk86X7v0FHA.3188@TK2MSFTNGP14.phx.gbl... > Marcello, > > I am not absolutely clear as to what you...

Connecting two series in a scatter chart
Hello - I have a user who created a scatter chart. The chart contains two series: "HO" and "PD" - the HO data point has a related PD data point. She would like to have Excel add a connector line from the HO data point to the corresponding PD data point. Is this possible? I apologize that I am not familiar with this type of chart, or the lingo, so please let me know what other information you would need to answer this question. We are using Excel 2007. Thanks very much! Hi, Double-click one of the series, in the format series dialog box choose the Options tab, and ch...

Excel Calculations Open in Manual Occasionally
MSOffice 2003, WinXP. When I open Excel, the Calculation Option defaults to Manual instead of remaining on Automatic. Is this a bug in Excel? I never set it to Manual. Thoughts can be posted or sent rich32822(Remove this)@hotmail.com. Regards, If the first workbook you open is set to manual it will change the setting for other workbooks as well meaning that if someone is sending you a workbook with this set, you open it by opening the attachment and Excel opens. Then you close this particular workbook and you open a new workbook, this workbook will now be set to manual -- Regards,...

Creating a Publisher 2007 Document in Arabic
Hi, I currently have a business card designed in Publisher 2007. I want to add a reverse side to it and put the same details on the card in Arabic. I'm going to use a Translation Service to translate the text but could someone tell me what configuration changes I need to make in Publisher and/or Windows in order to be able to paste and style the translated text? Thanks Dominic Help, Type Right to Left... The first entry has the requirements. -- Mary Sauer http://msauer.mvps.org/ <dominic.martin@dtrack.com> wrote in message news:5bb99815-f0f8-4aa3-8a76-b18b6...