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.
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
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.
VBA Code:
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