+ Reply to Thread
Results 1 to 2 of 2

Using check boxes to hide and un-hide rows with drop down lists within rows

  1. 06-24-2012, 06:00 AM #1
    Sparky_Chris
    Sparky_Chris is offline
    Registered User
    Join Date
    06-24-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    1

    Using check boxes to hide and un-hide rows with drop down lists within rows

    Hi everyone I really hoping someone can help me here. I have never done any coding before and started reading up on machos about a month ago so please excuse me if I have made real dumb errors I am a Electrcian not a programmer but I love to do this stuff.
    I have been trying to develop a quoteing spread sheet and have several sheets doing different tasks, on sheet 2 I have a series of check boxes at the top in which when checked un hides rows that are for each room.
    ie. at the top i have garage, hall, bedroom1, toilet etc. when you tick bedroom for example it then un hides a row titled bedroom with a series of check boxes to tick for required items in that room. When you tick the item a row relaivent to that item un hides with a drop down list to select a certain condition.

    I have this all working with the code example below but when i un tick a room it hides, then when i re tick it all the drop down boxes re apear lumped on top of one anther in bottom of the row, when they should only come back when I tick the next tick box item for that room that should contain the drop down.
    I beleive the code i have is contradicting because in module 4 i have:

    'Room selections for sheet 2, each tick box will bring up coresponding lines with item selections'
    'start of garage'

    Sub garage230vdownlight()
    If Range("AA17").Value = False Then
    Rows("17:17").EntireRow.Hidden = True
    Else
    Rows("17:17").EntireRow.Hidden = False
    End If
    If Range("AA17").Value = False Then
    ActiveSheet.Shapes("drop down 21").Visible = False
    Else

    ActiveSheet.Shapes("drop down 21").Visible = True
    End If
    End Sub
    Sub garage12vrecessedlight()
    If Range("AA18").Value = False Then
    Rows("18:18").EntireRow.Hidden = True
    Else
    Rows("18:18").EntireRow.Hidden = False
    End If
    If Range("AA18").Value = False Then
    ActiveSheet.Shapes("drop down 25").Visible = False
    Else

    ActiveSheet.Shapes("drop down 25").Visible = True
    End If
    End Sub
    Sub garagesurfacelights()
    If Range("AA19").Value = False Then
    Rows("19:19").EntireRow.Hidden = True
    Else
    Rows("19:19").EntireRow.Hidden = False
    End If
    If Range("AA19").Value = False Then
    ActiveSheet.Shapes("drop down 26").Visible = False
    Else

    ActiveSheet.Shapes("drop down 26").Visible = True
    End If
    End Sub
    Sub garagepowerpoints1()
    If Range("AA20").Value = False Then
    Rows("20:20").EntireRow.Hidden = True
    Else
    Rows("20:20").EntireRow.Hidden = False
    End If
    If Range("AA20").Value = False Then
    ActiveSheet.Shapes("drop down 36").Visible = False
    Else

    ActiveSheet.Shapes("drop down 36").Visible = True
    End If
    End Sub
    Sub garagepowerpoints2()
    If Range("AA21").Value = False Then
    Rows("21:21").EntireRow.Hidden = True
    Else
    Rows("21:21").EntireRow.Hidden = False
    End If
    If Range("AA21").Value = False Then
    ActiveSheet.Shapes("drop down 37").Visible = False
    Else

    ActiveSheet.Shapes("drop down 37").Visible = True
    End If
    End Sub
    Sub garagesensor1()
    If Range("AA22").Value = False Then
    Rows("22:22").EntireRow.Hidden = True
    Else
    Rows("22:22").EntireRow.Hidden = False
    End If
    If Range("AA22").Value = False Then
    ActiveSheet.Shapes("drop down 39").Visible = False
    Else

    ActiveSheet.Shapes("drop down 39").Visible = True
    End If
    End Sub
    Sub garage234waylighting()
    If Range("AA23").Value = False Then
    Rows("23:23").EntireRow.Hidden = True
    Else
    Rows("23:23").EntireRow.Hidden = False
    End If
    If Range("AA23").Value = False Then
    ActiveSheet.Shapes("drop down 43").Visible = False
    Else

    ActiveSheet.Shapes("drop down 43").Visible = True
    End If
    End Sub
    Sub garagewalllights()
    If Range("AA24").Value = False Then
    Rows("24:24").EntireRow.Hidden = True
    Else
    Rows("24:24").EntireRow.Hidden = False
    End If

    End Sub
    Sub garagephoneoutlet()
    If Range("AA25").Value = False Then
    Rows("25:25").EntireRow.Hidden = True
    Else
    Rows("25:25").EntireRow.Hidden = False
    End If
    End Sub
    Sub garagetvoutlet()
    If Range("AA26").Value = False Then
    Rows("26:26").EntireRow.Hidden = True
    Else
    Rows("26:26").EntireRow.Hidden = False
    End If
    End Sub

    Sub garagespare123()
    If Range("AA27").Value = False Then
    Rows("27:28").EntireRow.Hidden = True
    Else
    Rows("27:28").EntireRow.Hidden = False
    End If
    End Sub
    'end of garage'

    'start of hall 1'


    Sub hall1230vdownlights()
    If Range("AA33").Value = False Then
    Rows("33:33").EntireRow.Hidden = True
    Else
    Rows("33:33").EntireRow.Hidden = False
    End If
    If Range("AA33").Value = False Then
    ActiveSheet.Shapes("drop down 70").Visible = False
    Else

    ActiveSheet.Shapes("drop down 70").Visible = True
    End If
    End Sub

    Sub hall112vrecesseddlight()
    If Range("AA34").Value = False Then
    Rows("34:34").EntireRow.Hidden = True
    Else
    Rows("34:34").EntireRow.Hidden = False
    End If
    If Range("AA34").Value = False Then
    ActiveSheet.Shapes("drop down 71").Visible = False
    Else

    ActiveSheet.Shapes("drop down 71").Visible = True
    End If
    End Sub

    Sub hall1surfacelights()
    If Range("AA35").Value = False Then
    Rows("35:35").EntireRow.Hidden = True
    Else
    Rows("35:35").EntireRow.Hidden = False
    End If
    If Range("AA35").Value = False Then
    ActiveSheet.Shapes("drop down 72").Visible = False
    Else

    ActiveSheet.Shapes("drop down 72").Visible = True
    End If
    End Sub

    Sub hall1powerpoints1()
    If Range("AA36").Value = False Then
    Rows("36:36").EntireRow.Hidden = True
    Else
    Rows("36:36").EntireRow.Hidden = False
    End If
    If Range("AA36").Value = False Then
    ActiveSheet.Shapes("drop down 73").Visible = False
    Else

    ActiveSheet.Shapes("drop down 73").Visible = True
    End If
    End Sub

    Sub hall1powerpoints2()
    If Range("AA37").Value = False Then
    Rows("37:37").EntireRow.Hidden = True
    Else
    Rows("37:37").EntireRow.Hidden = False
    End If
    If Range("AA37").Value = False Then
    ActiveSheet.Shapes("drop down 74").Visible = False
    Else

    ActiveSheet.Shapes("drop down 74").Visible = True
    End If
    End Sub

    Sub hall1sensorinternal()
    If Range("AA38").Value = False Then
    Rows("38:38").EntireRow.Hidden = True
    Else
    Rows("38:38").EntireRow.Hidden = False
    End If
    If Range("AA38").Value = False Then
    ActiveSheet.Shapes("drop down 75").Visible = False
    Else

    ActiveSheet.Shapes("drop down 75").Visible = True
    End If
    End Sub

    Sub hall12waylighting()
    If Range("AA39").Value = False Then
    Rows("39:39").EntireRow.Hidden = True
    Else
    Rows("39:39").EntireRow.Hidden = False
    End If
    If Range("AA39").Value = False Then
    ActiveSheet.Shapes("drop down 76").Visible = False
    Else

    ActiveSheet.Shapes("drop down 76").Visible = True
    End If
    End Sub

    Sub hall1walllight()
    If Range("AA40").Value = False Then
    Rows("40:40").EntireRow.Hidden = True
    Else
    Rows("40:40").EntireRow.Hidden = False
    End If

    End Sub
    Sub hall1smoke()
    If Range("AA41").Value = False Then
    Rows("41:41").EntireRow.Hidden = True
    Else
    Rows("41:41").EntireRow.Hidden = False
    End If

    End Sub
    Sub hall1footlights()
    If Range("AA42").Value = False Then
    Rows("42:42").EntireRow.Hidden = True
    Else
    Rows("42:42").EntireRow.Hidden = False
    End If

    End Sub
    Sub hall1spare123()
    If Range("AA43").Value = False Then
    Rows("43:44").EntireRow.Hidden = True
    Else
    Rows("43:44").EntireRow.Hidden = False
    End If
    'end of hall 1'


    End Sub



    And in module 3 i have also:
    Sub roomgarage()
    'when garage tick box is selected rows 15 and 16 are un hidden'
    If Range("Ah7").Value = False Then
    Rows("15:16").EntireRow.Hidden = True
    Rows("29:29").EntireRow.Hidden = True
    Else
    Rows("15:16").EntireRow.Hidden = False
    Rows("29:29").EntireRow.Hidden = False
    End If
    'when garage tick box is un selected drop down menu boxes are hidden'
    If Range("Ah7").Value = False Then
    ActiveSheet.Shapes("drop down 21").Visible = False
    ActiveSheet.Shapes("drop down 25").Visible = False
    ActiveSheet.Shapes("drop down 26").Visible = False
    ActiveSheet.Shapes("drop down 37").Visible = False
    ActiveSheet.Shapes("drop down 36").Visible = False
    ActiveSheet.Shapes("drop down 39").Visible = False
    ActiveSheet.Shapes("drop down 43").Visible = False
    Else
    ActiveSheet.Shapes("drop down 21").Visible = True
    ActiveSheet.Shapes("drop down 25").Visible = True
    ActiveSheet.Shapes("drop down 26").Visible = True
    ActiveSheet.Shapes("drop down 37").Visible = True
    ActiveSheet.Shapes("drop down 36").Visible = True
    ActiveSheet.Shapes("drop down 39").Visible = True
    ActiveSheet.Shapes("drop down 43").Visible = True

    End If
    'when garage tick box is unselected tick box items are hidden'
    If Range("Ah7").Value = False Then
    ActiveSheet.Shapes("check box 27").Visible = False
    ActiveSheet.Shapes("check box 32").Visible = False
    ActiveSheet.Shapes("check box 33").Visible = False
    ActiveSheet.Shapes("check box 35").Visible = False
    ActiveSheet.Shapes("check box 38").Visible = False
    ActiveSheet.Shapes("check box 40").Visible = False
    ActiveSheet.Shapes("check box 42").Visible = False
    ActiveSheet.Shapes("check box 44").Visible = False
    ActiveSheet.Shapes("check box 45").Visible = False
    ActiveSheet.Shapes("check box 46").Visible = False
    ActiveSheet.Shapes("check box 47").Visible = False
    Else
    ActiveSheet.Shapes("check box 27").Visible = True
    ActiveSheet.Shapes("check box 32").Visible = True
    ActiveSheet.Shapes("check box 33").Visible = True
    ActiveSheet.Shapes("check box 35").Visible = True
    ActiveSheet.Shapes("check box 38").Visible = True
    ActiveSheet.Shapes("check box 40").Visible = True
    ActiveSheet.Shapes("check box 42").Visible = True
    ActiveSheet.Shapes("check box 44").Visible = True
    ActiveSheet.Shapes("check box 45").Visible = True
    ActiveSheet.Shapes("check box 46").Visible = True
    ActiveSheet.Shapes("check box 47").Visible = True
    End If
    End Sub

    I have about 9 rooms yet to create so if someone can show me an easier way I would be very appreciative.
    Reply With QuoteRegister To Reply
  2. 06-24-2012, 07:22 AM #2
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using check boxes to hide and un-hide rows with drop down lists within rows

    Hello Sparky,

    Guess when you were reading the forum rules ( you did read them, right?), you must have skipped over this one.

    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    ... and appear like this when posted:

    Please Login or Register  to view this content.
    You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.

    Please make an immediate edit to your post to add the missing code tags.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1