Macro has 2 problems
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro has 2 problems

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have the following macro for which I am getting two different problems.

    The first problem is "Run-time error 1004: Unable to set the Hidden Property of the Range class". Here is my code. My workbook is shared and protected:

    Private Sub CommandButton1_Click()
    [A1].Activate
    Call UnprotectSharing
    Call PrintWorksheet
    Call ProtectSharing
    End Sub

    Sub UnprotectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.UnprotectSharing Password
    End Sub

    Sub ProtectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.ProtectSharing Password
    End Sub

    Sub PrintWorksheet()
    '
    ' PrintWorksheet Macro
    ' Macro recorded 2/18/2002 by Anita Grimes
    '

    '
    [A3].Activate
    Columns("A:B").Select
    Selection.EntireColumn.Hidden = True
    Columns("F:G").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=5
    Columns("R:R").Select
    Selection.EntireColumn.Hidden = True
    Columns("U:U").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=6
    ActiveWindow.ScrollColumn = 1
    Range("C1:AB51").Select
    ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Cells.Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = False
    ActiveWindow.SmallScroll ToRight:=10
    Columns("V:V").Select
    Selection.EntireColumn.Hidden = True
    End Sub

    My other problem is when the workbook goes through the "Protect Sharing" function it re-saves the workbook as "bp02acg.xls" as the filename instead of just saving over the existing file.

    Please help, this is a verycritical piece of my project that is due at 5 pm today.

    Thank you everyone!!!

    Anita

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try changing your code to this:
    Code:
    Public CurrentFilename As String
    Private Sub CommandButton1_Click()
    [A1].Activate
    Call UnprotectSharing
    Call PrintWorksheet
    Call ProtectSharing
    End Sub
    
    Sub UnprotectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.UnprotectSharing Password
    End Sub
    
    Sub ProtectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.ProtectSharing Filename:=CurrentFilename, Password:=Password
    End Sub
    
    Sub PrintWorksheet()
    '
    ' PrintWorksheet Macro
    ' Macro recorded 2/18/2002 by Anita Grimes
    '
    
    '
    
    On Error Resume Next
    CurrentFilename = ActiveWorkbook.FullName
    [A3].Activate
    Columns("A:B").EntireColumn.Hidden = True
    Columns("F:G").EntireColumn.Hidden = True
    Columns("R:R").EntireColumn.Hidden = True
    Columns("U:U").EntireColumn.Hidden = True
    ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Cells.Select
    Range("C1").EntireColumn.Hidden = False
    Columns("V:V").EntireColumn.Hidden = True
    End Sub
    Hope this is what you need
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This appears to have worked - thanks so much for your expertise!

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was able to get this to work in order to print the worksheet; however, when you click on the button for PrintWorksheet it brings you up a pop-up that says "This will remove the workbook from shared use" that works fine if you say OK or Yes; however, if you hit cancel, you get a runtime error. I will have multiple users using this workbook, I would like for it NOT to create a runtime error - is there any VB code I could put in to handle if someone hits cancel to just revert back to the spreadsheet and NOT attempt to Unprotect the document?

    Thanks!
    Anita

  5. #5
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-18 11:12, ACG wrote:
    I was able to get this to work in order to print the worksheet; however, when you click on the button for PrintWorksheet it brings you up a pop-up that says "This will remove the workbook from shared use" that works fine if you say OK or Yes; however, if you hit cancel, you get a runtime error. I will have multiple users using this workbook, I would like for it NOT to create a runtime error - is there any VB code I could put in to handle if someone hits cancel to just revert back to the spreadsheet and NOT attempt to Unprotect the document?

    Thanks!
    Anita
    Do you want to disable this message box. If yes, change your code to read:
    Code:
    Public CurrentFilename As String
    Private Sub CommandButton1_Click()
    [A1].Activate
    Application.DisplayAlerts = False
    Call UnprotectSharing
    Call PrintWorksheet
    Call ProtectSharing
    Application.DisplayAlerts = True
    End Sub
    
    Sub UnprotectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.UnprotectSharing Password
    End Sub
    
    Sub ProtectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.ProtectSharing Filename:=CurrentFilename, Password:=Password
    End Sub
    
    Sub PrintWorksheet()
    '
    ' PrintWorksheet Macro
    ' Macro recorded 2/18/2002 by Anita Grimes
    '
    
    '
    
    On Error Resume Next
    CurrentFilename = ActiveWorkbook.FullName
    [A3].Activate
    Columns("A:B").EntireColumn.Hidden = True
    Columns("F:G").EntireColumn.Hidden = True
    Columns("R:R").EntireColumn.Hidden = True
    Columns("U:U").EntireColumn.Hidden = True
    ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Cells.Select
    Range("C1").EntireColumn.Hidden = False
    Columns("V:V").EntireColumn.Hidden = True
    End Sub
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com