Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Location
    OKC, OK
    Posts
    7

    Unanswered: Check boxes - formula use

    I have a a cell that has several check boxes in it, these can be independently checked so that one or several could be checked at one time. I am trying to determine a way to create a formula that will see what is checked and based on those that are checked bring up another set of check boxes.

    In other words, I will have a Main Category leading to a Sub Category which could lead to a Sub Sub Category. So that once the user selects the main Categories, they will be led to chose from the Sub Category and if need be, then the Sub Sub Category. Then once they are done, they spreadsheet can be sent to me and I will see all the selections made for each product row and update our website accordingly.

    I am not overly proficient with Excel as I mainly work in Crystal, so please be kind and with a simplified explanation.

    thanks much!

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Hi, do you actually have a form control check box on your page, or are you using a cell as a check box?

    I don't know if this is exactly what you want but if you haven't got a solution yet this might help you. This method would rely on you using macroa within your excel sheet. On some of the older versions of Excel it means every time that you open the document you get a message saying that macros are within and do you want to disable them...

    If it's a proper form control check box you first need to know the name of the check boxes. This can be found by right clicking on the check box, and then looking in the Name Box in the top left (Just above cell A1).

    Then, right click on the check box, Select Asign Macro, Press New

    The code within this module would then be something along the lines of;

    Code:
    Sub CheckBox1_Click()
    
    If Me.CheckBox1 = TRUE Then 
    
    ' If it's checked, make box 2 and 3 visible and hide 4 and 5
    
       ActiveSheet.Shapes("Check Box 2").Visible = TRUE
       ActiveSheet.Shapes("Check Box 3").Visible = TRUE
       ActiveSheet.Shapes("Check Box 4").Visible = FALSE
       ActiveSheet.Shapes("Check Box 5").Visible = FALSE
    
    Else
    
    ' If it's not checked, hide box 2 and 3 and make 4 and 5 visible
    
       ActiveSheet.Shapes("Check Box 2").Visible = FALSE
       ActiveSheet.Shapes("Check Box 3").Visible = FALSE
       ActiveSheet.Shapes("Check Box 4").Visible = TRUE
       ActiveSheet.Shapes("Check Box 5").Visible = TRUE
    
    End If
    
    End Sub
    The only problem with the above is that initally every check box would be visible, so create a second macro which hides all of the check boxes and ensure that you run this macro before you give out your sheet to anyone. (You might even want a second to routine to make them all visible again)

    Code:
    Sub RunAdminState()
    
       ActiveSheet.Shapes("Check Box 2").Visible = FALSE
       ActiveSheet.Shapes("Check Box 3").Visible = FALSE
       ActiveSheet.Shapes("Check Box 4").Visible = FALSE
       ActiveSheet.Shapes("Check Box 5").Visible = FALSE
    
    End Sub
    Last edited by christyxo; 01-11-12 at 09:57. Reason: Mistyped code

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I've also just discovered that if you are using Office 2010, pressing ALT and F10 will enable you to show or hide any controls.

Tags for this Thread

Posting Permissions

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