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

 

[CrazedFox]

impact412,

Could you not just use one userform for the 25 checkboxes, and define a variable for the size of the package, then check how many boxes were selected when the user tries to click finish?
Two listboxes side by side might actually work better for your purposes, but maybe you have a good reason for checkboxes.

So to make those 25 checkboxes idea work, at a global level put something like this:

Dim packageSize as Integer 'this will be equal to either 5, 10 or 15 depending on the package picked.

When the user selects a package, declare this variable as the package size. (again, either 5,10, or 15)

then, in the userform declare:

Dim selectedSize as Integer

Then for each checkbox declare something like this:

Private Sub checkbox1_click()

If checkbox1.value=True Then 

    selectedSize =selectedSize+1

Else
    selectedSize =selectedSize-1

End Sub

etc... do this for each checkbox

Then, you can either put in the below code to trigger when the user attempts to click to the next form (my preference) or have it check on each change of the userform.

Private Sub finishButton_click()
'before anything else, check if the number selected and the package chosen are the same

If selectedSize <> packageSize Then Msgbox("You have not selected the right number of countries for this package. Please select " & packageSize & " countries")

'after, wipe selectedSize so errors don't occur if there are more than one package in an order

selectedSize = 0

'now exit the userform and put any other code here

End Sub

Hope this helps you some. Two listboxes side by side might actually work better for your purposes, but maybe you have a good reason for checkboxes.