Control Button Macro to Expand a Cell and Add New Rows
Hi All,
In Table 1. I have a lot of duplicate data. I would like to be able to insert a control button with a macro that adds rows onto Course Name, Start Date and End Date and expands Name, Staff No. and Staff Group each time Joe Bloggs completes a new course.
Table 2. is the desired outcome which I completed by merging.
Can you help me with the VBA code to branch one cell into multiple cells that are all linked.
Any help is greatly appreciated.
D.
Last edited by Data2022; 10-17-2022 at 11:05 AM.
Hi Data2022. Welcome.
Why not configure a pivot table to summarize based on table 1 that has the new course data and values in it?? A refresh or table 2 should populate with the new courses.
Pete
Hi Pete
Thank you for the welcome and your response.
The spreadsheet will be a working document capturing 18+ courses for 100+ staff members as they complete courses throughout the year.
For ease of recording, to keep course and staff information aligned and to avoid duplication.
I was hoping to add control buttons with macros attached to add new course, undo and delete as shown in the image attached.
I have a limited knowledge of VBA.
Thanks for your help.
D.
Better than merging cells would be to set Conditional Formatting to mask the duplicate Name/Staff No.
Putting a CF formula like =(A1=A2) on cell A2 (and downward) to make the letter font white (or my preferenced light grey) would hide the duplicates and avoid the problems that merged cells create.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Hi MickOriginally Posted by mikerickson
Thanks for the response I would like to set up this feature for users to input data. Rather than cleaning it afterwards to remove duplicates. That way if a user is recording courses for 100 staff they only have to enter their name once along with their staff no and staff grouping. Otherwise they would have to repeat this information for each course.
Control Button Macro to Expand a Cell and Add New Rows
Hi All
This is my first time using the forum this week. Can anyone help with replicating the control buttons used in the second image - a new row is added by selecting an active cell and then clicking the new control button.
I would like to replicate this in the first image by expanding Column A, B and C rows and adding new linked rows to Columns D,E,F and G so that name, staff number and staff group do not have to be duplicated. In other words each time Joe Bloggs completes a new course I could select the last inserted course and an add course button. The name, staff number and staff grouping would then expand accordingly and a new linked row would be created. Note conditional formatting and drop down cells are applied to some of the columns.
This file will be an active working document for the user I wont be cleaning it by merging afterwards.
Thank for your help in advance.
Please do NOT open duplicate threads here (please have a read of the rules you agreed to).
If you need to add clarification, you must do so in the original thread.
I've copied your post from the duplicate thread across for you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks