macro to populate list/combo box, then delete selected sheet and rows on other sheets

[drewship]

Hello all. With a lot of help, I believe I have the necessary code to add new member sheets and rows on established sheets. Now I would like a macro to have a list/combo box automatically populate with the names (first and last) on the Scout totals sheet, then delete the selected member from the Counts sheet, the Scout Totals sheet, and finally delete the member's sheet from the workbook. This will make it very simple to reuse it every year when the older members move on and need to be removed from the workbook.

Thanks in advance for your assistance!!

Andrew

[jaslake]

Hi Andrew

I'm working on the logic of your workbook and have a question.

Why are there names in Scout Totals that don't appear in Counts? There are 18 names (including 0...by the way, what is 0) in Scout Totals and only 14 names in Counts.

John

[jaslake]

Hi Andrew

Try the attached. I believe it does as you described. Let me know of issues.

John

[drewship]

jaslake, to answer your first question, it is because those were failed entries. The code crashed before the names could be created in the Counts sheet. I will check out your code and let you know how it does.

Thanks!!
Andrew

[drewship]

How do I replicate the Remove Scout button on my sheet? Tried a couple things but I am not familiar with how you made and linked the button to the code.

Thanks,
Andrew

[jaslake]

Hi Andrew



I replaced your Form Control Button with an ActiveX Control Button. Assuming you're using Excel 2007 (as your profile indicates):

• Developer -> Insert -> ActiveX Control
• Right Click on the Button to see your options (Properties, View Code, etc.)
• Or, simply Copy -> Paste the button then Right Click

I assume this is what you're asking.

John

[drewship]

I tried the ActivX control before I posted but can not get it to link to the code in the Scout Totals sheet since

Please Login or Register  to view this content.

is not in my list of choices to add the macro. I saved, closed, reopened several times to no avail...but it now appears to have finally linked itself (after an hour or so of just sitting open in the background) and seems to be working.

Just a couple of things I noticed: You cannot copy this type of button. Neither right-click nor ctrl-c works. You cannot see the code either since you cannot right-click on it, that is what was so frustrating.

The other thing is the code will not find and delete the rows on the Counts sheet unless the groupings are expanded. I have the code to do this so I can fix it...just a note for those needing a similar solution.

Will test some more and post back.

Thanks again!!!
Andrew

[drewship]

Something else I need to fix is re-coloring the rows after a row is deleted...

[drewship]

Here is the code to expand the groups for the deletion then close them after the deletion:

Please Login or Register  to view this content.

Somewhere in here I need to match the DEN to the

Please Login or Register  to view this content.

so the rows can be recolored correctly.

[jaslake]

Hi Andrew

To do this

copy this type of button. Neither right-click nor ctrl-c works

make certain you're in Design Mode (Developer ->Design Mode). I didn't mention this earlier as, on my machine, I was automatically in Design Mode. When you're done fooling around, be sure to exit Design Mode.

There is no "link" to code in this type of button; the code is behind the button as you'll see if you can get the Right Click on button to work.

I'm color blind so I'll not be much help here

re-coloring the rows after a row is deleted

John

[drewship]

Thanks jaslake, I never needed to work in Design mode so it never occured to me to even try it...lol. Now I have another tool to use for deconstructing other workbooks.

As for re-coloring the rows, my initial attempt recolored all the rows outside the groups on the sheet, not within the DEN group as intended!!! Glad I have more than one test copy so it is ease to dump and re-open the workbook...

[jaslake]

Hi Andrew

After you mentioned re-coloring the rows, I revisited your workbook. Indeed, the rows ARE colored (every other one it appears). With my highly diminished ability to see colors, I didn't notice this until I was actually looking for it.

Nothing comes to immediate mind but I'll look at it. In the meantime, if you resolve the issue, please post back.

John

[jaslake]

Hi Andrew

Try this code. It's been tested.

Please Login or Register  to view this content.

One thing I believe you should think about is how to make your Den Named Ranges dynamic.

John

[jaslake]

Hi Andrew

I forgot to mention that Sheets("Counts") needs to be activated so the code should read as follows

Please Login or Register  to view this content.

John

[drewship]

Thanks!!...been fooling with it and could not find a way to identify the den from within the Find_First code to use for selecting the range to re-color. Will plug in your code and see what happens.

Andrew

[drewship]

jaslake, added your code and it is coloring all the rows between the DEN headers with

Please Login or Register  to view this content.

What do I need to do to have the first row under the DEN header clear with every other row between the DEN headers

Please Login or Register  to view this content.

? Basically I need to alternate the clear and light blue rows to make it easier to seperate them for viewing.

Thanks!!

Andrew

[jaslake]

Hi Andrew

On my machine this code does this

have the first row under the DEN header clear

and this

alternate the clear and light blue rows

Please Login or Register  to view this content.

Notice that I've changed this line of code

Please Login or Register  to view this content.

Are you saying ALL lines between the Den Headers are getting colored? I've attached a screen shot of the results (I used a darker color for this).

Let me know if you don't get the same results.

John

John

[jaslake]

Hi Andrew

I was able to duplicate this issue

ALL lines between the Den Headers are getting colored

I'll have a solution for you in or before the AM.

John

[jaslake]

Hi Andrew

As I was working on your issue I believe I found another potential "Fly In The Ointment". Let's say that twin brothers, Peter Jones and Paul Jones decide to join the troop in Den whatever. The procedure will attempt create a worksheet for each brother named "Jones". Of course, you can't have two sheets with the same name so a run time error will be displayed.

I don't know how complex you wish to get but I'd imagine a similar scenario is entirely possible in your real world. Want me to work on a solution to this potential issue or ignore it?

I THINK I have this issue resolved

ALL lines between the Den Headers are getting colored

John

Edit I MAY have this issue resolved also

how to make your Den Named Ranges dynamic

[drewship]

Wow...I REALLY appreciate all the effort you are putting into this!! I will be out of town today so will not be able to work on it until tomorrow (Wed), but I had thought of the duplicate sibling issue since we actually have 3 sets of siblings in the Scouts. I was going to try to grab the first 3 letters of the first name and add them to the Last name like so:

"John Test" would be "Test Joh"
"Mike Test" would be "Test Mik"

I was going to work on that after the row re-coloring was ironed out.

Thanks again!!
Andrew

[jaslake]

Hi Andrew


I believe these issues are resolved:

• Row re-coloring
• Den Named Ranges dynamic
• File Name consists of the Last Name and First Three Letters of First Name

Den Named Ranges are quasi Dynamic in that they're being recreated each time a Scout is added or deleted.

This was contributing to the re-coloring issue. Re-coloring is being done by Named Range but the Named Ranges were out of date after a Scout was added or deleted.

Please pay attention to the procedures I've added to the Workbook Open and Workbook Before Close Events.

The Open Event Procedure

Please Login or Register  to view this content.

turns off Inconsistent Formula markers. In your testing, comment out this procedure then save and restart Excel. I've checked the formulas and they appear to be correct. You know your data, I don't. The other procedure

Please Login or Register  to view this content.

turns the error checking back on.

I've also modified your cmdStart_Click sub somewhat to deal with what I saw as issues. I have backup's so, if the changes don't suit your needs, let me know.

I've attempted to upload the file several times this evening with no success. Perhaps the site is having issues. I'll try again tonight or in the morning.

This has been an interesting exercise. Hope we can make it work to your satisfaction.

John

[drewship]

John, looking forward to your updates!!!

Thanks,
Andrew

[jaslake]

Hi Andrew

I just attached the file to last evening's post.

John

[drewship]

John, I have been testing your changes and so far they are working great. I had to add:

Please Login or Register  to view this content.

to 2 places in the " 'apply formulas and formatting to Counts Sheet" section so the groupings would expand to be correctly sorted.

Please Login or Register  to view this content.

Another thing I noticed was that if I entered a new Scout and did not change the Den (left it as Den 1), the last name did not get capitalized. What would be the best way to automatically trigger the change before clicking the Start button?

Thanks for the great work!!
Andrew

[jaslake]

Hi Andrew

Try the attached. I believe it solves this issue

if I entered a new Scout and did not change the Den (left it as Den 1), the last name did not get capitalized

Let me know of issues.

John

[drewship]

Thanks John. I was trying "vbProperCase" in various locations of the cmdStart and txtLastName blocks with some strange results. I will put it all together with my workbook copy and continue testing.

Andrew

[drewship]

Everything seems to be working so I am going to close this thread. Thanks for all the help!!! Your code is spot-on and I very much appreciate it!!!

Andrew

[jaslake]

Hi Andrew

Glad it works for you and to have been of help.

A click of the scales is always appreciated.

John

[drewship]

John, I clicked the scales last week and it won't let me do it again.

Thanks!!

[jaslake]

Hi Andrew

I didn't notice. Thanks for that.

John