+ Reply to Thread
Results 1 to 4 of 4

Automatically populating forms via VBA, need help breaking up headings into categories

  1. #1
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Question Automatically populating forms via VBA, need help breaking up headings into categories

    I'm using a table to dynamically populate a userform. It's intended to function basically the same as Excel's built-in DataForm tool, but modeless and allowing for more than 32 columns. I 'm using modified code cribbed from elsewhere so can't claim ownership nor complete understanding, but I know enough to follow the logic and understand what each piece is doing.

    The table currently it looks like this (simplified):
    Screenshot 2022-08-04 101031.png

    Which generates this form:
    Screenshot 2022-08-04 100556.png

    The real table contains dozens of columns, however, and only a handful need to be edited at any given time. It's hard for my end users to scroll through the whole thing to find just the pieces they need, so I want to add categories/headings that break up the form and make it more accessible. So something like this for the table,
    Screenshot 2022-08-04 101233.png

    which would ideally generate a form like this (the horizontal lines are empty labels with a height of 1):
    Screenshot 2022-08-04 104922.png

    The current code pulls header titles from the first row of the sheet. If it makes things difficult to put the categories above the headings like in the picture, however, I'm not at all attached to it. If a lookup table or something is easier that's fine.

    Sample file is attached, or the [relevant portion of] the current code is below:
    Please Login or Register  to view this content.
    Thank you in advance for any advice you're able to provide.
    Attached Files Attached Files
    Last edited by Rabk; 08-04-2022 at 01:02 PM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,302

    Re: Automatically populating forms via VBA, need help breaking up headings into categories

    upload your workbook with de-sensitised information (10 or 12 rows is sufficient)
    rather than using the default form you would probably better using a bespoke UserForm with a multipage on it to tab to categories.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Re: Automatically populating forms via VBA, need help breaking up headings into categories

    Quote Originally Posted by torachan View Post
    upload your workbook with de-sensitised information (10 or 12 rows is sufficient)
    rather than using the default form you would probably better using a bespoke UserForm with a multipage on it to tab to categories.
    Wups! I said I'd attach the file and then forgot to. It's attached to the original post now.

    I could build a bespoke form (and originally had it that way), but neither the categories nor the headers are static. There are multiple versions of this basic form and different users have different needs. So I was trying to create something that would dynamically adapt to the underlying table(s) to save me the trouble of rebuilding it every time.

  4. #4
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Thumbs up Re: Automatically populating forms via VBA, need help breaking up headings into categories

    I solved it with a workaround. I inserted an empty column at the start of each category, prefixing each category name with "xx". Then I added an If... Else... statement to change the textbox of any category labels to imitate the appearance of a line.

    Modified Table:
    Screenshot 2022-08-04 135619.png

    Result:
    Screenshot 2022-08-04 135521.png

    Please Login or Register  to view this content.

+ 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. [SOLVED] Counting duplicates within two arrays and then breaking down by categories
    By DButtgen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2021, 10:30 AM
  2. Populating multiple forms from combobox choice and updating!
    By luargee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2012, 02:49 PM
  3. Entering headings automatically
    By LPPL in forum Excel General
    Replies: 0
    Last Post: 07-07-2010, 07:18 AM
  4. Adding columns and categories automatically
    By br015535 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2009, 03:04 AM
  5. Breaking down data using multiple categories
    By EI-ALEX in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2008, 09:39 AM
  6. Populating Lists in forms
    By marksince1984 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2006, 09:00 PM
  7. [SOLVED] Creating Column Headings from XML Categories When Linking Data Wit
    By JimR in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2005, 01:10 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