Results 1 to 2 of 2

VBA Button to Hide/Unhide Rows on Protected Sheet

Threaded View

  1. #1
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    VBA Button to Hide/Unhide Rows on Protected Sheet

    Hey all,

    This is my first crack at VBA, so bear with me.

    I'm trying to create a VBA button to hide or unhide specific rows at the user's discretion, on a protected sheet.

    I've seen a lot about UserInterfaceOnly mode, but there seems to be some compatibility issues between it and the button I created.

    This is what I have for the UserInterfaceOnly, applied to the This Sheet module, which is a direct copy/paste:

    Private Sub Workbook_Open()
    Dim wSheet As Worksheet
        For Each wSheet In Worksheets
            wSheet.Protect Password:="relyt", _
            UserInterFaceOnly:=True
    Next wSheet
    End Sub
    My button uses this:

    Sub AllergenAlert_Click()
    With Worksheets("Label Claim").Rows("6:10")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    With Worksheets("Formula").Rows("9:13")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    With Worksheets("Process").Rows("8:12")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    With Worksheets("WGT").Rows("9:13")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    With Worksheets("Yield").Rows("9:13")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    With Worksheets("Pkg Record").Rows("9:13")
            If .Hidden = True Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    End Sub
    I'm sure that I butchered it, and there's an easier way to write it, but it's what I could manage. This isn't something that would occur on every sheet, or on the same rows of each sheet. I'll likely be using similar code for single rows on an "Organic Alert" button, as well as adding a few sheets to it. The code functions (albeit a little slowly) when the sheets aren't protected.

    I had thought that maybe the Allergen Alert code needs to be on the This Workbook module with the UserInterfaceOnly mode, but that just rendered my button unusable, and I know that the UserInterfaceOnly code needs to be in This Workbook, so I'm not sure where I'm going wrong. The error occurs right out of the gate, at the first .EntireRow.Hidden = False, and it hits me with an error that says: Run Time Error '1004': Unable to set the Hidden property of the Range class.

    Thank you in advance.
    Last edited by hindotmo12; 07-25-2019 at 06:10 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hide/Unhide rows using If funtion on protected sheet
    By jennis7242 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2018, 01:39 PM
  2. Enabling hide/unhide rows in protected sheet
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2014, 05:16 AM
  3. [SOLVED] allow hide/unhide rows&columns when sheet protected (macro)
    By jw01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-15-2013, 03:18 AM
  4. complicated hide/unhide rows on protected sheet
    By achohan in forum Excel General
    Replies: 15
    Last Post: 06-21-2012, 02:27 PM
  5. [SOLVED] VBA Hide/Unhide Rows when Sheet protected
    By Kinez101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2012, 01:34 PM
  6. Replies: 1
    Last Post: 08-19-2011, 10:25 AM
  7. Password protected command button to protect/unprotect and hide/unhide
    By cpercival in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2011, 03:27 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1