Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Protecting a worksheet (VBA/Excel/2000)

    The following snippet of code was working well; then I inserted the two .VPageBreaks lines. The next time I attempted to run the code it failed at the first .Protect line. The message was
    <hr>Run-time error '1004':
    Protect method of Worksheet class failed<hr>
    The Project compiles properly; yet the code fails even after:
    <UL><LI>Remming out the .VPageBreaks line immediately in front of the .Protect line which failed; and
    <LI>after a reboot of the system with the .VPageBreaks line still remmed out[/list]<pre>Option Explicit
    Const pwd = "i581a"
    Const MyTitle = "MHP IRB Reporting Tool"

    Public Sub Auto_Open()
    Dim Contractor As String
    Dim myFile As String
    Dim mySuff As String
    Dim Reject As Boolean
    Dim Ctr As Long
    ThisWorkbook.Activate

    With ThisWorkbook
    If Left(.Name, 3) = "ISS" Then
    .Sheets("T27-28").Visible = False
    For Ctr = 21 To 26
    With Sheets("T" & Ctr)
    .Unprotect pwd
    .Columns("M:V").Hidden = False
    .VPageBreaks.Add .Range("M1")
    .Protect pwd
    End With
    Next Ctr
    Else
    .Sheets("T27-28").Visible = True
    For Ctr = 21 To 26
    With Sheets("T" & Ctr)
    .Unprotect pwd
    .Columns("M:V").Hidden = True
    .VPageBreaks.Delete .Range("M1")
    .Protect pwd
    End With
    Next Ctr
    End If
    End With
    </pre>


    Any insight that can be provided will be greatly appreciated.
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    The line

    .VPageBreaks.Delete .Range("M1")

    is incorrect, it should be something like

    .VPageBreaks(1).Delete

    but I can't delete individual page breaks at all in code in Excel 2002. The only thing that works for me is the ResetAllPageBreaks method of the worksheet:

    .ResetAllPageBreaks

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    Hi Hans,

    Two examples straight from the Excel 2000 Help file:

    Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNone
    Worksheets("Sheet1").Columns("J").PageBreak = xlNone

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    Thanks for that!

    I wonder why the macro recorder results in code like Activesheet.VPagebreaks(2).Delete, which is valid according to the online help, but doesn't work.

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Location
    York, Virginia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    Hello Don,

    It looks like you are trying to remove the vertical page break after you have hid the columns.
    What happens when you put the .VPageBreaks.Delete .Range("M1") before the .Columns("M:V").Hidden = True

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protecting a worksheet (VBA/Excel/2000)

    Thank you Hans; MacroPod and Paul
    All of your comments were beneficial.
    Regarding Hans' comment,
    <hr>The only thing that works for me is the ResetAllPageBreaks method of the worksheet:

    .ResetAllPageBreaks
    <hr>
    I find that one can remove just the vertical breaks from a range using the following code.
    <pre>Sub Macro6()
    Dim i As Long
    For i = 2 To 25
    Columns(i).PageBreak = xlNone
    Next
    End Sub
    </pre>


    My original problem was a failure with the .Protect command. I now realize this failure was caused by the sheet being grouped with others.
    Again; Thank you all for your support.
    Regards
    Don

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    The macro recorder sometimes records invalid or nonworking code; this has been noted with inserting watermarks in Word, if I recall correctly. Perhaps the recorder is representing some kind of internal notation that Microsoft never added to the object model? While there often is another way to do it, it sure is annoying when it happens.

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting a worksheet (VBA/Excel/2000)

    Not sure whether I'm on exactly the same wavelength, but

    xlPageBreakPreview.VPageBreaks(1).DragOff xlToRight, 1

    works for me.
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •