+ Reply to Thread
Results 1 to 2 of 2

Grouping and Form Controls - Can I keep form controls from bunching when collapsed?

  1. #1
    Registered User
    Join Date
    04-30-2014
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    1

    Question Grouping and Form Controls - Can I keep form controls from bunching when collapsed?

    Hello,

    I cannot figure out a way to use form controls within grouped sections without the controls bunching up when the group is collapsed. I saw a post from 2011 where someone seemed to be asking the same question but there were no responses, so hopefully someone is able to help or offer an alternative solution. I can share the file if someone thinks they have a solution.

    A little background on why this is needed:

    I'm trying to develop an new audit form for my work of which it's ultimate purpose is to be printed out and hand-marked by the auditor. The form is a supplement indicating specific project features that the auditor needs to look for within the audit package documents. Projects vary between a few main categories of scope and each category has sub-sections that may or may not apply to that project. The current form is a Word Doc table (not designed by me) and its major flaw is that any category not applicable to that project is just left blank and is a waste of paper/ink. This is especially true when only a small category applies and then there are several pages that are just wasted.

    So my solution to this is to use the grouping feature which I've found to be mostly successful. My excel form is about 4 pages long when fully expanded, but when collapsed to one small section, fits on 1-2 pages. However, there is a major snag - all of my form controls bunch on top of eachother when I collapse the areas. If need be, I'll just have to take out all of the form controls I guess, but I'd really rather keep them since most of the form is standard options to select from and just a few fill-in-the-blanks.

    I appreciate any suggestions! Thank you!

    - Jillian

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Grouping and Form Controls - Can I keep form controls from bunching when collapsed?

    When you say "collapsed" and "expanded", I assume you mean that you are hiding and unhiding rows and/or columns that contain the objects. I had the same problem. My worksheet is a loan collateral form. Originally, all the rows were hidden except for row 1 which contained a button for each type of collateral. The user clicked the one they want and it unhid the relevant rows & objects (combo boxes, checkboxes). If I remember correctly (I first developed it years ago), it would work fine until I saved and closed the workbook. Then when I reopened it, the problem occurred.

    My first resolution was to reverse the design -- I started out with all the rows (and objects) unhidden instead of hidden. Then, when they click a button it hides all the other rows, leaving only the desired rows unhidden. This works fine as long as you keep your original document with the rows unhidden. This may or may not work for you.

    I recently found a better solution, which also may or may not work for you, but hopefully you can adjust the code for your situation. It requires a consistant naming convention for your objects, and also requires that each object is linked to a cell, and that it's position corresponds to the cell position. This particular code runs when they click a "Reset" button, which first unhides all the rows, so you may want to adapt it to only expand the object if the linked cell height is greater than 0. Let me know if you have questions.
    Please Login or Register  to view this content.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Form Controls bunching
    By SonOfOdin in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-10-2012, 08:34 AM
  2. Replies: 0
    Last Post: 01-27-2011, 01:43 PM
  3. Form controls vs. ActiveX controls
    By wmorrison49 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2008, 10:59 AM
  4. Form Controls vs VBA controls
    By cmpcwil2 in forum Excel General
    Replies: 1
    Last Post: 04-19-2006, 10:55 AM
  5. ActiveX Controls vs Form Controls
    By Alex in forum Excel General
    Replies: 1
    Last Post: 01-11-2006, 04:50 AM

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