Sequencing on selection Macro

[christian2016]

Hi Guys,

Basically I have check boxes in an input sheet tab and whatever selection is ticked it will list them in the current order on the output tab.

Formula im using is:
=IFERROR(INDEX(Input!$B$10:$C$22,SMALL(IF(Input!$B$10:$C$22=TRUE,ROW(Input!$B$10:$B$22)-ROW(Input!$B$10)+1),ROWS(Input!$A$1:$A1)),2),"")

Now what i need if possible a macro set up determining the sequence of the selection. There can be a number of different sequences based on the selection so I just need a code im able to edit.

Example if 2, 5, and 1 is selected. Sequence should state 5,1 and 2 in the output tab.

Please find attached spreadsheet.

Any help is greatly appreciated.

Thanks

[jaslake]

Hi christian2016

Please notice the Change(s) in your Check Box(s) Code and that the "Create Work Button" is gone.
Simply check the Check Box(s) in any sequence...Sheet "Output" will be populated in that sequence.

Also notice I've changed this Sheet Sheets("Workflow") name to Sheets("Output") to conform with your Sample Data.

Let me know of issues.

Please Login or Register  to view this content.

[christian2016]

Thanks Jaslake

Works great.

Is there a way to limit the sequence to the range of B5 to B9 in the output sheet

Also if possible list first, second,third, fourth and fifth in column D in input tab next to each selection.

And if a mistake is made for example you tick the wrong checkbox then untick it this should be removed from the output tab. At the moment you need to clear the form and start again.

Thanks again for your help.

[jaslake]

Hi christian2016

Are you suggesting a maximum of 5 Check Boxes can be selected?

Is there a way to limit the sequence to the range of B5 to B9 in the output sheet

This is pretty straightforward.

Also if possible list first, second, third, fourth and fifth in column D in input tab next to each selection.

Regarding this...

if a mistake is made for example you tick the wrong checkbox then untick it this should be removed from the output tab

Assume the User has selected 5 Check Boxes and decides he/she doesn't like sequence #3 selection.

• What happens to the sequencing in Column D of Input Tab?
• What happens to the sequencing in Column B of Output Tab?

[christian2016]

Are you suggesting a maximum of 5 Check Boxes can be selected?

Yes. Although still be able to alter the macro to max out greater than 5 if required.
I assume this will be easy as I’m not that bad with macros.

Assume the User has selected 5 Check Boxes and decides he/she doesn't like sequence #3 selections.
What happens to the sequencing in Column D of Input Tab?
What happens to the sequencing in Column B of Output Tab?

Example

Input
Section 1 - First
Section 2 – Third Unselect
Section 4 - Second
Section 5 –Fourth

Output
Section 1
Section 4
Section 2 – Will be removed
Section 5

Input tab the third will be removed in column D and in the output tab section 2 will be removed and the cell will be empty. Next selection will replace the third slot. Example selected section 9. Section 9 in input column d will have third and will be listed in the 3rd place in the output tab. Maybe difficult to do this.

Another option which probably suits better is that when you select a section in the input tab, first, second and third etc will appear in column D (Macro used for this). An index match formula can be used to read first, second, third text and match it with the section and display in the output sheet tab.

If a wrong selection is made example third selection then you are able to untick the selection, select the correct selection and third will appear in column d and the output tab will match third and display the section in the output tab. Also having the ability to manually change the first second, third options in column D. The first,second, third etc will automatically appear on selection but can be manually changed (I believe this can be done by just typing first or third in the cell in column D). For Example wrong order change column D Section 1 to third and section 2 to second.

Thanks again really appreciate your time

[jaslake]

Hi christian2016

I'm going to have to think on this...the mental gymnastics boggle the mind at the moment. Get back to you.

[christian2016]

Thanks Jaslake

[jaslake]

Hi Christian

It's approaching 6PM here and am about to fix dinner for my Daughter and Grandson...will probably not get back into this tonight.

[christian2016]

Thats fine Jaslake.
whenever you get some free time.

[jaslake]

Hi Christian

You'll have to play with this. Each of the Check Box Codes has been changed to something similar to this...the only differences are the Cell Address and the Rows to Hide and Unhide (I've not modified these items).

Please Login or Register  to view this content.

[christian2016]

Thanks Jaslkae.

Works great.

[jaslake]

Well Christian, you've got a lotta stuff going on in the Code...it's going to break...the Code does not anticipate everything a User may do.

I tried to break it (and did) and haven't fixed it for how it broke except for

you need to clear the form and start again

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

[christian2016]

Yer i think you are right clearing the code is the only way to go.

[jaslake]

Hi Christian

I think this is prudent as it does not demand a lot of effort by the User (unless I'm missing something).

Yer i think you are right clearing the code is the only way to go.

Thanks for the Rep.