Userform help

[impact412]

Let me start by saying I've never used userforms before.

Here's the situation:

I am creating a quote tool and have gotten pretty much everything completed except for the package deals that are offered.
We offer EU packages of 5, 10, or 15 countries to be selected. So, if a client selects one of the EU packages and generates their quote sheet. All it will have is the EU(Package Number) and the price for that package and I need for them to be able to select the countries they wish to search. Enter the userform. Each userform has a textbox, a "Finish" command button, and 25 checkboxes corresponding with the 27 nations within the EU (BeNeLux is an option that includes Belgium, the Netherlands, and Luxembourg). I've grouped each set of 25 checkboxes with the names EU5, EU10, and EU15.. each checkbox name within each group is EU#[Country] (ex. EU5Austria)

I've got the code written to initialize the necessary userform after the Quote sheet is generated.

Sub FindUF()
    Dim Qu As Worksheet
        Set Qu = Worksheets("Quote")
    Dim r As Integer
    Dim ColRef As Integer
        ColRef = 7


    Qu.Activate
    For r = 11 To 206
        If Cells(r, ColRef) = "EU5" Then
            EU5UF.Show
        ElseIf Cells(r, ColRef) = "EU10" Then
            EU10UF.Show
        ElseIf Cells(r, ColRef) = "EU15" Then
            EU15UF.Show
        Else: End If
    Next 
End Sub

But this is where I'm stuck. I think I understand that each checkbox will need a subroutine to change its value to True if checked. I know I can write a macro to simplify this.. I just wish there was a way to apply said macro to a group of checkboxes as I have grouped each set of 25 and it would be a lot easier to assign a macro to 3 groups than to 75 individual checkboxes, but I digress.

My needs:
- I need to be able to limit the number of selections in each userform. EU5 can only have 5 selections, EU10 only 10, and EU15 only 15. (I have no idea how to do this)
- I need the finish button in each userform to take the caption (country name) of each selection within the userform and paste it into a cell comment that corresponds with the package name on the Quote sheet (so if EU5 was the package selected, I need to find the cell that contains the text "EU5" and add a comment, then paste the names of the countries selected on the userform into the comment) and then format the comment to send that information when the quote is sent to my company.

Nice to have:
- I would like to be able to initiate a userform upon opening the workbook and then have that information (search criteria, organization name, contact name, email, and phone, and a description of search specifics) held in the form until the Quote sheet is generated and then paste that information into corresponding cells. I would like the description of search specifics pasted as a comment on the cell that has the initial search criteria.

If I can provide any additional information please let me know.
I have attached pictures of one of the userforms (they're all identical except for the verbiage 5, 10, and 15) and the Quote sheet where the selections would need to be put in a comment.

TYIA

 

[My Aswer Is This]

I may be able to help.
You said:
"think I understand that each checkbox will need a subroutine to change its value to True if checked. I know I can write a macro to simplify this.. I just wish there was a way to apply said macro to a group of checkboxes as I have grouped each set of 25 and it would be a lot easier to assign a macro to 3 groups than to 75 individual checkboxes, but I digress."

If you were to put your checkboxes into a Frame
There is a frame control in the Excel Toolbox

Put a Frame on your userform and then put your Check boxes in a frame.

Now you can use a script like this:

Put this script in a Button

Private Sub CommandButton1_Click()
'Modified  9/26/2021  5:29:42 PM  EDT
Dim ctrl As MSForms.Control
 
    For Each ctrl In Frame1.Controls
        Select Case True
            Case TypeOf ctrl Is MSForms.CheckBox
                ctrl.Value = True
        End Select
    Next ctrl
End Sub

Now just create as may Frames as you want and add as many checkboxes as you want,

 

[My Aswer Is This]

You said:
My needs:
- I need to be able to limit the number of selections in each userform. EU5 can only have 5 selections, EU10 only 10, and EU15 only 15. (I have no idea how to do this)

Not sure what this means: "limit the number of selections"
What is a selection? This is not Vba Term I'm familiar with.

And why do you need more then One Userform.
Have you ever used a Mutipage on your Userform.

A Multipage is like having pages on a Userform

A excel Workbook has many sheets. And a Userform can have a Multipage which can have tabs which are sort of like a Workbook sheets. So you can have what looks like 10 Userforms on the same Userform by using a Multipage which can have many Tabs where you can have many controls on different Multipage Tabs

Just to let you know.
But you may have your own reasons for needing more then one Userform.

So I have helped you some here. Try my script see how it works and we can then move forward to helping with the rest of your needs.

 

[impact412]

You said:
My needs:
- I need to be able to limit the number of selections in each userform. EU5 can only have 5 selections, EU10 only 10, and EU15 only 15. (I have no idea how to do this)

Not sure what this means: "limit the number of selections"
What is a selection? This is not Vba Term I'm familiar with.

And why do you need more then One Userform.
Have you ever used a Mutipage on your Userform.

A Multipage is like having pages on a Userform

A excel Workbook has many sheets. And a Userform can have a Multipage which can have tabs which are sort of like a Workbook sheets. So you can have what looks like 10 Userforms on the same Userform by using a Multipage which can have many Tabs where you can have many controls on different Multipage Tabs

Just to let you know.
But you may have your own reasons for needing more then one Userform.

So I have helped you some here. Try my script see how it works and we can then move forward to helping with the rest of your needs.

I apologize for any confusion. Allow me to clarify...

For the first part:

I need the EU5 userform to only allow 5 checkboxes to be selected. For the EU10 userform, 10 checkboxes. And for the EU15 userform, 15 checkboxes. A checkbox checked is a "selection".

For the multipage:

If it could be the same userform but limited based on what the initial selection was (EU5, EU10, EU15) then I'd be totally okay with that. I'm not familiar with a multipage as I'm not even familiar with userforms outside of creating them. I'm totally in the dark when it comes to coding them and then referencing them later. Or even how to extract the data the userform collects.

As far as the number of sheets.. I'm using one and creating another.

I will be trying your code in the morning, but wanted to give you a response this evening.

 

[My Aswer Is This]

Ok you said:
I need the EU5 userform to only allow 5 checkboxes to be selected. For the EU10 userform, 10 checkboxes. And for the EU15 userform, 15 checkboxes. A checkbox checked is a "selection".

You said I need the EU5 userform to only allow 5 checkboxes to be selected
So why not only have 5 checkboxes on the EU5 Userform?

If you have more then 5 what are they for?

Other wise we would need to know the name of the 5 checkboxes and if someone selected one that they were not suppose to check they would get a popup message saying "Your not allowed to check checkbox named Six.

 

[impact412]

Ok you said:
I need the EU5 userform to only allow 5 checkboxes to be selected. For the EU10 userform, 10 checkboxes. And for the EU15 userform, 15 checkboxes. A checkbox checked is a "selection".

You said I need the EU5 userform to only allow 5 checkboxes to be selected
So why not only have 5 checkboxes on the EU5 Userform?

If you have more then 5 what are they for?

Other wise we would need to know the name of the 5 checkboxes and if someone selected one that they were not suppose to check they would get a popup message saying "Your not allowed to check checkbox named Six.

The reason for more than 5 checkboxes is that there are 27 total nations that are part of the European Union(EU) and EU5 corresponds to 5 countries to be selected from within the European Union. Same goes for EU10 and EU15.

 

[My Aswer Is This]

The reason for more than 5 checkboxes is that there are 27 total nations that are part of the European Union(EU) and EU5 corresponds to 5 countries to be selected from within the European Union. Same goes for EU10 and EU15.

But I thought you said you were going to use like 5 different Userforms.
So how does a user know what checkboxes he can click on?

And what will happen when the user clicks on the checkbox? what will be the result of doing this.

I guess I really do not know what the end result is your trying to accomplish.

 

[impact412]

But I thought you said you were going to use like 5 different Userforms.
So how does a user know what checkboxes he can click on?

And what will happen when the user clicks on the checkbox? what will be the result of doing this.

I guess I really do not know what the end result is your trying to accomplish.

No.. I only indicated that there would be 3. 1 for each package option (EU5, EU10, and EU15). I'd like to have a fourth when the workbook launches to gather information such as search criteria, organization name, contact name/email/phone.. but that's later.

 

[impact412]

But I thought you said you were going to use like 5 different Userforms.
So how does a user know what checkboxes he can click on?

And what will happen when the user clicks on the checkbox? what will be the result of doing this.

I guess I really do not know what the end result is your trying to accomplish.

What I'm trying to accomplish is to determine which countries within the European Union a client wants to search when they select an EU Package.. whether that be the EU5 package, the EU10 package, or the EU15 package. Each package comes with the corresponding number of countries to be searched.

So, if a user was to see the form per the picture I provided.. they would be inclined to select, based on the EUx package selected, nation selections equal to the number within the package they selected (5 for EU5, 10 for EU10, and 15 for EU15).

 

[impact412]

The reason for more than 5 checkboxes is that there are 27 total nations that are part of the European Union(EU) and EU5 corresponds to 5 countries to be selected from within the European Union. Same goes for EU10 and EU15.

Start to finish.. the process looks like this.

Users are presented with the "Quote Tool" worksheet.
There are 196 different selections that can be made based on checkboxes in column B.
The client is expected to go through the list of options and make a selection based on their needs for search.
After making their selection, the client then clicks on a command button that generates the Quote sheet.
The Quote sheet includes the priority of the order, the necessity of opinion, the countries selected, and the pricing data for each country (that is dependent upon the priority and whether opinion is needed).
Totals are calculated and delivery times are determined.

All of this is handled. Coding complete.

The problem is that a few of the 196 different options are packages and within those packages I need countries to be searched defined by the client making the decision. There are total of 25 options.. and depending on the package, I need the number of selections from the options limited.

Please let me know if I can provide any more clarity.