Select-Case with several check box's instead of IF'S stats

This is a simply file so you can only see the VBA code as is.
There is data in the master file.

I already have the checkbox named: "Impressão , Confeção , Produção , Todas, Faturação"
I need to check which on of them are true. and then do something

Can someone help me?

I have to look for all combination within at least 5 checkboxs, and according to that selection export a specific range of a worksheet to PDF.
Let me know if you have any more hints or solutions please. it's important to get this working.

Originally Posted by mikerickson

There's no code in your attached .xlsx file.

As a general reply to your situation, consider a user form with three checkboxes.

Please Login or Register  to view this content.

If you have lots of checkboxes, code like this can be used

Please Login or Register  to view this content.

Hello Mr.

Can you please help me according to my situation and needs? Have a nice weekend

Originally Posted by jaslake

Hi brainzlp

No File attached...

Sorry, my bad.
Please check again. hope you can help me out it's really important

HELP-SELECT CASE PROBLEM.xlsm

Originally Posted by Norie

From what I can see/understand you can't use Select Case for this.

Perhaps if you explained further, in simple terms, what you are trying to do we could help further.

What i am trying to do is:

Each checkbox is associated witha range, according do checkboxs values i need to export to pdf. For example:

Checkbox1 = range(A1:D5)
Checkbox2 = range(A6:A9)
Checkbox3 = range(A10:D20)

If only checkbox value is true then export to pdf its range. If all are checked export from A1:D20.. Of 2 and 3 are checked then export A6:D20

If for example 1 and 3 i need to export (A1:D5,A10:D20)

I need for now 6 checkbox.

Please help me out. You can check in the attach this eexample with if statements. I want to put that depending on checkbox values and then if true put in export range.

Hope to hear from you asap

As you can see in the attach:
For each checkbox value TRUE, the early defined ranged is exported for a defined direction. Thats what i want to do

Originally Posted by jaslake

Hi brainzip

Seems to me you'll need cascading Select Case Statements as you have multiple If Statements. What's the gain?

I suggest select case cuz it seems better than if statements but what i really want is to simplify the code cuz i will have to increment more checkbox. Thats why i just wanted to check the checkboxs true values yhan export that multiarea range

Hello everyone.

Sorry for the duplicate posts, not on purpose, just wanted to reform my problem to explain better my point.
Which doesn't have to me in Select Case method, just wanted to run a loop for example to check which checkbox's are Checked and then export it's range area defined.

would like you to help me doing this code:
I want a code that:
1st: I define variable ranges for each checkbox
2nd:For each Checkbox Value true, then Get in a Multiarea range to Export
3rd: Export that area range to pdf

Check the excel file in the Userform code, with it statements but i would like to not use IF statmentes cuz i will have to introduce more checkbox's sooner or later.


* Sorry again, Not really my intention to bother you guys, just need some help from someone expert in this. Not going to happen Again.
Thanks in advance for all your help.

Originally Posted by jaslake

@Ford

This didn't get merged. I wish the OP to know the consequences of his/her actions.

Hi brainzip

Some friendly advice (besides, it's against Forum Rules).

Please do not post duplicate Threads. You have multiple Forum Contributors working on the same issue. The consequence is
you'll get some of us a bit upset and be put on our Ignore List.

Not a good practice if you wish help.

You're totally right, not my intention to up set any of you guys, and just wanted to re edit my post for my need. Which doesn't have to be with select case. Cuz That will seem messy as well.

Sorry for going against the rules, not going to happen again.

Originally Posted by Doc.AElstein

. There have been many Duplicate posts just recently, indeed I reported them and mentioned in several Threads already, but usually they are identical and are posted within a short space of time, almost immediately. The start of the two Threads were different and there was about a day between so , I am surprised that this was the case here. ( And he has just made a new one!! )

. Anyway I am struggling at this line in Mike's code

Please Login or Register  to view this content.

. As it fails to find any object i try…but OK leave it to Mike ( If he is notified of responses by E-Mail he should have a few from all over the place!!! As FDibbins is merging!! – And If E-mail notification is working – that too and many other things with the Excel Forum Software have not been just recently )
.
. Sorry if i confused the issue

Alan

What does your code do in my needs? It's not defining a multiarearange so then i can export to PDF

Am i misreading?

Originally Posted by jaslake

@ Alan

Nah, you didn't confuse the issue. These situations do get me a bit torked off when I've spent a bit of time on the OP's issue only to find that other Forum Members are working on the same issue in other, duplicate, Threads.

When I see people like Mike working on ANY issue I'm interested in I'll perhaps follow the Thread but I'll not interfere as I know the OP is in good hands and my time can be "wasted" elsewhere.

You're totally right, although the solution was good, it could get a little more variable, cuz selected case, it's like an if statements, all help and ideias will increment knowledge and will contribute for a better answer.

I just have to apologize for my duplicated posts, although that's a solved issue.
Hope we can all together find a solution for this unique problem, since i haven't found any solution in google neither excelforums

Originally Posted by Doc.AElstein

.1 ) It is NOT MY Code-. It is the code from idea from mikerickson. Here:
http://www.excelforum.com/excel-prog...ml#post4180491


. If I understand it , it should give some number out (checkSummary is a long number )
. You can then use this number as the selection criteria for you different Combined Ranges of interest.
. Unfortunately, I could not get it to work.
. I think you will have to wait and hope mikerickson will come back to help You. Possibly tomorrow. It may be late where he lives now.
. It is also late here by me now. I shall take a look tomorrow. Maybe you could try a PM to mikerickson to ask again for help. At the time that he gave You the code You had not managed to update the correct File.
. You must be patient, as we try to help you if and when we can. I will try again tomorrow.
. ( Maybe I PM mikerickson tomorrow for You – You not can… know how not to is )
Alan

Thanks for your help and patience, i am new in vba and i need to do some complex stuff for a company. Thanks again for your time

Hi there,

The attached workbook shows an approach that seems fairly similar to Mike's, but it decouples the worksheet layout from the VBA code used in the UserForm.

If you need to add extra CheckBoxes in the future, the following routines can be easily modified to accommodate them:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by mikerickson

I would not use a SelectCase structure for this

I would build a range and then export that range

Please Login or Register  to view this content.

Hello, thanks for your reply, your solution seems the best and understandable at least for me, i tried it but didn't work.

I made some adjusts, and it highlights:

Please Login or Register  to view this content.

[/CODE]

My atual code is:

Please Login or Register  to view this content.

You can check file in attach

Originally Posted by mikerickson

The reason that you were struggling with the line in the looping code is that your control names aren't indexed. (The non-indexed style is what I prefer).
To loop with generically named controls I would use a loop like

Please Login or Register  to view this content.

I can put in the checkbox's name as CheckBox1 2 3 4 5 etc instead of names if you prefer, then i comment the code to know which is each.
Hope you can help me with highlighted code

Originally Posted by Greg M

Hi there,

The attached workbook shows an approach that seems fairly similar to Mike's, but it decouples the worksheet layout from the VBA code used in the UserForm.

If you need to add extra CheckBoxes in the future, the following routines can be easily modified to accommodate them:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hello Mr,

I've checked your attach, i don't understand nothing of the code (or small stuff) ALTHOUGH, it works 200% perfectly. Thanks!

If i introduce a new CheckBox (Example Checkbox6) what do i need to do, i've done that but it highlights this:

Please Login or Register  to view this content.

Check File in attach please:

Hi again Mr. Greg,

It's great now, thanks.

I liked one thing in the previous file, which is: MsgBox Confirming what have been printed
I would like to have that showing:
"There have been printed: Impressão ; Confeção ; etc"

Is there a way to put that?

If you edit something, please do it in the attached file, since i have a new checkbox and adjusted the code to test this check box (Which btw works 100%)

So please, my last ask is if you can help me, putting a MsgBox showing which pages were printed.
All pages have the name in the top: saying: "Seccão Impressão" and so on

In Worksheets.Cells("L1") ; L74 And on

Thanks in advance

* In the previous file you had the Address Printed,
I would like to know if there is a way to put the correct Section which is in the Cells L1L74 etc , accordingly to the printed area

Hi again,

In the previous file you had the Address Printed,
I would like to know if there is a way to put the correct Section which is in the Cells L1L74 etc , accordingly to the printed area

I can restore the address message if you want - it was there just for my information while debugging.

I don't really understand what you mean in your second point.

Have to go out now - hope to be able to reply later.

Regards,

Greg M

Originally Posted by Greg M

Hi again,



I can restore the address message if you want - it was there just for my information while debugging.

I don't really understand what you mean in your second point.

Have to go out now - hope to be able to reply later.

Regards,

Greg M

Hi again,

I mean:

Please Login or Register  to view this content.

At the end of the macro, when you had the Adress Message, i would like to put which sections were printed.

Like:
If i select srange2 and srange3 a message appears saying: "Sections printed: _
Confeção & Embalagem "

Is there a way to do so?

Hi again,

The attached version of the workbook displays a message stating which sections have been included in the PDF file. It uses a two-dimensional array to store the Values and the Captions of the various CheckBoxes on the UserForm, so the text values used in the message for identifying the sections will be the same as those used in the CheckBox Captions.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi again,

The attached version of the workbook displays a message stating which sections have been included in the PDF file. It uses a two-dimensional array to store the Values and the Captions of the various CheckBoxes on the UserForm, so the text values used in the message for identifying the sections will be the same as those used in the CheckBox Captions.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hello Mr. Greg M,

It's PERFECT! That's 100% what i need and want.
I just Have to modify here to insert a new checkbox correct? With this new message, do i need to insert in another place?

Please Login or Register  to view this content.

Hi again,

Many thanks for all of your feedback and also for the Reputation increase

That's correct - you need to make the change/additions only in the three places indicated. The text for the print message associated with the new Checkbox will be taken automatically from whatever Caption value you assign on the UserForm.

Glad to have been able to help.

Best regards,

Greg M

Originally Posted by Greg M

Hi again,

Many thanks for all of your feedback and also for the Reputation increase

That's correct - you need to make the change/additions only in the three places indicated. The text for the print message associated with the new Checkbox will be taken automatically from whatever Caption value you assign on the UserForm.

Glad to have been able to help.

Best regards,

Greg M

You're welcome, Thank you very much. Have a great day .

You helped me a lot really. it's better than i could ever ask.
Continue with the good work.

Hi again,

Take a look at the attached workbook and see if it does what you want.

The code gets a bit more complicated because you want to have future CheckBoxes linked to the "All" Checkbox - it's necessary to use the "Class plus Collection" approach to achieve this. The attached workbook uses this approach, so you should be able to add as many extra CheckBoxes as you need - just follow the same steps as you used before to add CheckBox6. One point - the code expects to find one Checkbox which has the Caption value of "Todas", so you must ensure that one (and only one) Checkbox has this Caption value.

The Print Message now includes the descriptions of the individual sections, even if the "Todas" Checkbox has been selected.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Hi again,

See if the attached workbook does what you need.

It uses a new routine which you can adapt to suit your requirements:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi again,

See if the attached workbook does what you need.

It uses a new routine which you can adapt to suit your requirements:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hello Greg,

That is a perfect solution for my project #1 which has to link with the checkbox's and then just Validate the preselected checkbox's. I just have to suit as required the cases and the other 3 things correct? (The value that is changed 1/2/3 is the checkbox value correct?

For the second project i told you:
I don't want to link to check box, just have to link the printranges to the F33 Cell
Then it export to pdf and shows the message of the printedranges without showing any checkbox's values or Userform.

It's like only the Select-Case issue but defining the print-ranges for all sections.

I don't know if you can get what i mean, it's like the same project, but without the Userform and checkbox's. Just depend on that Cell Value, and if the has X value then print some ranges, Y value print another ranges and print all.

Hi again,

I just have to suit as required the cases and the other 3 things correct? (The value that is changed 1/2/3 is the checkbox value correct?

Yes, your understanding is correct


To achieve what you want for your project which does not require a UserForm, just make a copy of the current workbook and delete or "comment out" the highlighted lines in the following routine:

Please Login or Register  to view this content.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi again,



Yes, your understanding is correct


To achieve what you want for your project which does not require a UserForm, just make a copy of the current workbook and delete or "comment out" the highlighted lines in the following routine:

Please Login or Register  to view this content.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Ok i shall delete the:
frm.show
if frm.Ok = True then
End if

Although let the call printselected ranges be there right?
Unload frm should be deleted no?

And all this, will execute the printranges and export in pdf as well form the default selection right?

Hi again,

Although let the call printselected ranges be there right?

Yes. This is correct.

Unload frm should be deleted no?

This should NOT be deleted.

The UserForm continues to operate "in the background" - the appropriate CheckBoxes are "prepopulated" on the basis of what is contained in the "InitialiseCheckBoxes" routine. The UserForm is NOT displayed to the User, but functions as if the "Validar" button had been pressed by the User.

Regards,

Greg M

Ok, thats it. It pressed the "Validar" button to continue. That's ok.(I shall not delete the Userform as help, since it will run in background)
For this project i don't think i will need more help.
Thanks you very much for all your help!

Hi again,

You're welcome - glad to have helped.

Best regards,

Greg M

Originally Posted by Greg M

Hi again,

You're welcome - glad to have helped.

Best regards,

Greg M

Hello Mr. Greg,

Firstly thank you for the time you invested which workes perfectly.

I would like to know if there is a easy way to do a thing:
Now, what we have is according to checkbox's selected we have printed ranges.

I need two things, for importance order:
1) According to checkbox's selected, put in the same column Name in Worksheets("Tapasois") the word "SIM"
Let me better explain:

In Sheet2 i have a table, and in somecolumns i have the production sections ("Bolha, Embalagem , Serigrafia, etc etc") - Which i have too in the Checkbox's Caption
What i need is, according to checkbox's selected put the word "SIM" in the last filled row in Sheet2 columns of the section.

Probably i can do this with IF Checkbox1.Value = True then put in Cell X the word "SIM" put let me know if there is a better way.

My 2nd need would be to change a little bit the Msgbox of the checkbox's select

The Checkbox's are according to Master Sections like, "Production , expedition , packaging"
Checkbox1 , 2 , 4 are from Production
Check 3 , 5,8 are from expedition
Checkbox 6,7 are from packaging

Display a Msgbox saying according to checking box value:
"The following sections were printout"

From Production:
Checkbox1.caption
Checkbox2.caption

Expedition:
None

Packaging:
Checkbox7

This is an exemple for Check1,2 and 7 pressed.

Thank you very much for your help in advance.

Can someone help me with my problem?

Thanks in advance

Originally Posted by brainzlp

Hello Mr. Greg,

Just want to let you know, that the project is running smoothly, i've introduced some more codes and everything is ok.

Thanks for your precious help.

Hello Mr. Greg,

Sorry to bother you again, is there a way you can give me some help for my problems?

Thanks for your time

Originally Posted by protonLeah

Please start a new thread with the current problem. 69 posts is to much to go through to understand what the current problem is. Please, also, attach the lastest workbook.

I think it will be hard to understand since the code was almost all done by Greg, but i will reduce my workbook to that macros working so someone can have a look. I will try to get a thread name which suggests the problem. " Place X in correct column according to checkbox selects on form"

Originally Posted by protonLeah

Please start a new thread with the current problem. 69 posts is to much to go through to understand what the current problem is. Please, also, attach the lastest workbook.

New thread created with file in attach.
Hope you can help me out as well

http://www.excelforum.com/excel-prog...ml#post4241187

I need help with my new thread, which is linked to this one.

Hope someone can help me out.