Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: [vba] cannot hide/unhide rows when sheet is protected

  1. #1
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation [vba] cannot hide/unhide rows when sheet is protected

    Hi,

    I have this code in my module

    Code:
    Private Sub Boys_Click()If Boys.Value = True Then
    Rows("13:57").EntireRow.Hidden = True
    Else
     Rows("13:57").EntireRow.Hidden = False
      End If
    End Sub
    However, my problem is whenever i protect the sheet it doesnt allow me to proceed(debug mode) since i am fully aware that some cells on the rows are protected to.

    (I already tried modified the default cell locking(allowing rows) and still wont work)

    Are there any work arounds here that would let excel allow the hiding/unhiding feature?

    Thank you in advance.

  2. #2
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Hi jpbonono,

    im fairly sure if format rows and format columns are ticked when you choose the cell protection rights it should work regardless. If for some reason it still won't work you can add a couple of lines into your macro that will unprotected the sheet, hide / unhide rows, then re-protect the sheet.

    i'm currently posting from my iPad but when I get back to my pc I will try and give you some example code to work with

  3. #3
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Code:
    Private Sub Boys_Click()If Boys.Value = True Then 
    ActiveSheet.Unprotect ("password")
    Rows("13:57").EntireRow.Hidden = True 
    Else  
    Rows("13:57").EntireRow.Hidden = False   
    End If 
    ActiveSheet.Protect ("password")
    End Sub
    Where the bold password can be amended to whatever your password is.

  4. #4
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Thank you for a quick reply fishboy, I've already did your suggestion. Your second option however, when I do the unprotecting codes it will also reveal the formulas on some cells that are part of the rows and that will be a big problem.

  5. #5
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    okay im gonna try that first.

  6. #6
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    The codes wont work.

  7. #7
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Quote Originally Posted by jpbonono View Post
    Thank you for a quick reply fishboy, I've already did your suggestion. Your second option however, when I do the unprotecting codes it will also reveal the formulas on some cells that are part of the rows and that will be a big problem.
    The process of unprotecting the sheet, hiding the rows and reprotecting the sheet is pretty much instantaneous. You shouldn't really see your formulas on screen. You can always add this to the start of the macro to prevent screen flicker etc
    Application.ScreenUpdating = False

  8. #8
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Quote Originally Posted by jpbonono View Post
    The codes wont work.
    Can you elaborate a bit? Does it error? Does it do nothing? Does it start and then fail part way through?

  9. #9
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    ...

  10. #10
    Board Regular
    Join Date
    Aug 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] cannot hide/unhide rows when sheet is protected

    Quote Originally Posted by Fishboy View Post
    Can you elaborate a bit? Does it error? Does it do nothing? Does it start and then fail part way through?
    It opens the module and requesting for a debug.

Some videos you may like

User Tag List

Tags for this Thread

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
  •