Userform help

impact412

New Member
Joined
Sep 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Attachments

  • Userform.JPG
    Userform.JPG
    118.4 KB · Views: 11
  • QuoteSheet.JPG
    QuoteSheet.JPG
    83.4 KB · Views: 11

CrazedFox

New Member
Joined
Nov 4, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

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

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top