Results 1 to 10 of 10

Hiding/Unhiding Rows Based on Dropdown Box

Threaded View

SubParLlama Hiding/Unhiding Rows Based on... 11-13-2012, 01:38 AM
Naveed Raza Re: Hiding/Unhiding Rows... 11-13-2012, 02:04 AM
Winon Re: Hiding/Unhiding Rows... 11-13-2012, 02:10 AM
SubParLlama Re: Hiding/Unhiding Rows... 11-13-2012, 02:26 AM
Winon Re: Hiding/Unhiding Rows... 11-13-2012, 02:30 AM
SubParLlama Re: Hiding/Unhiding Rows... 11-13-2012, 02:34 AM
SubParLlama Re: Hiding/Unhiding Rows... 11-13-2012, 02:40 PM
Winon Re: Hiding/Unhiding Rows... 11-14-2012, 01:42 AM
SubParLlama Re: Hiding/Unhiding Rows... 11-14-2012, 03:09 AM
Winon Re: Hiding/Unhiding Rows... 11-14-2012, 12:29 PM
  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Hiding/Unhiding Rows Based on Dropdown Box

    Hello all, this issue has been stumping me for the longest time and I would greatly appreciate your collective help.

    Situation:
    I have a worksheet that needs to hide/unhide rows based on a dropdown box (form control). The dropdown box has 3 choices. Depending on the value of the dropdown box, it will hide certain rows. However, the worksheet must be protected because there are formulas within Excel throughout the sheet and as these are being designed for another group, I'd like to have them protected to prevent accidental errors.

    Error:
    When the sheet is protected and I change the dropdown box (for example from selection 1 to selection 3), I receive a run-time error: 1004. Unable to set the Hidden property of the Range class.

    Code:
    'Select number of issues
    Sub DropDown_Number_Issue()
    
        Select Case Range("J4").Value
        
            '1 investigation
            Case 1
                Call Investigation_2_Close
                Call Investigation_3_Close
    
            '2 investigations
            Case 2
                Call Investigation_2_Open
                Call Investigation_3_Close
            
            '3 investigations
            Case 3
                Call Investigation_2_Open
                Call Investigation_3_Open
    End Select
    End Sub
    
    
    'Open investigation 2
    Sub Investigation_2_Open()
    
        Rows("57:74").Hidden = False
        Range("H57").Value = "=SUM(H58:H61)"
        Range("I57").Value = "=SUM(I58:I61)"
        Range("H64").Value = "=SUM(H65:H72)"
        Range("I64").Value = "=SUM(I65:I72)"
    End Sub
    This is the partial code used. The bolded line is highlighted after clicking on debug.

    Any help would be great.

    Attachment included to help. The error is encountered any time the dropdown box is changed.
    Attached Files Attached Files
    Last edited by SubParLlama; 11-13-2012 at 02:28 AM. Reason: Attachment added

Thread Information

Users Browsing this Thread

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

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