We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Dymanic Mutliple List Selection - VBA Excel - Userform Query

Medium Priority
31 Views
Last Modified: 2020-06-21
Hello Team,

I request your kind support for my below example related with Vba Query on Userform. I have the below attached example , where i have made a list ( which will increase accordingly in future) .
Currently the list name is called Category in List tab . This "Category" list , i have three items added which is server, storage and switch . As mentioned earlier, this list will be added more .

I have created a form, where i need to add multiselect list , so from left side, i have the full form and right side i only select one or two what is needed . Then i click next to go next tab.

Here i want to add the prices or discount on left side, based on the dropdown list which i have selected from the previous tab . So in my example , if i select Server and Storage, next tab , i need t find in pull down list menu and enter its corresponding values in the left side , which will get reflected in excel sheet .

is this possible in excel vba user form as my objective is to check , if i can add more lists in future or do i need to code manually for it .

Sid
Tlist.xlsm
Comment
Watch Question

Author

Commented:
Dears,
Kindly request your support

Sid 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but I don't understand your question.

Author

Commented:
hi ,
if you notice in excel sheet , there is home , list and database tab .
In home tab - open form link
list ( i have made a list under Category as three items - Server, Storage , Switch )
- In form as in picture A shown below , under choice , i need to select Category , which will be Server, Storage, Switch on left side and whatever i select goes to right ( it can be one of them or two of them or all ) 
Picture A

In Picture B below , which is the second Tab
if you notice selected choice , which is pull down choice, this is basically from the selected list on the right hand side of picture A . What i require is as follow .

1. for whatever i items it shows in pull down menu , which came from picture A on the right, i need to add the corresponding entries of list price, discount , and total price. For eg: if i select Server on Pic A , right side , it will be show same as pull down list on Pic B , hence entries will be made . If i select Server and Switch on Pic A, right side, it will show two lists on pull down menu as on Pic B , hence entries will be made for 3 each .

i require your help on how this coding can be done on VBA .

highly appreciate your support

Sid 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If for example I just select 'Server'. If I then go to the 'Price' page do I then enter amounts in List Price, Discount and Discounted Price? If so, where does the data go (which columns) after I click OK?

What should be in 'Selected Choice' on the 'Price' page'?

Author

Commented:
hi,

1.data will go to excel tab - database , there is three column created for server section .

2 selected choice is same as lstRight page on picture A ( right side) , where items are added 

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
2 selected choice is same as lstRight page on picture A ( right side) , where items are added
Since you already made selections on the 'Choice' page, what is the purpose of showing the choices again?

After looking at the Database sheet, it seems to me that you are going through a lot of trouble developing a userform when you could simply add/change the Database data manually.

Author

Commented:
objective for me is for easy data entry and to be dynamic .
for eg: as i have selected for eg : two items such as Server and Storage, i need to fill 6 items ( 3 each * 2) . As form is small , i was looking to make it dynamic .

as the excel sheet gets filled, i could easily filter using pivot , but hard to fill data cell by cell.

is there a better option to suggest me please 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I think that the design of the Database sheet could be improved because right now you have headings that match the List sheet's categories, and you will need to add, change, or delete the headings when the categories are added to, changed or deleted. I also don't understand why you have a Category column on the Database sheet when you also have columns for List Price, Discount and Discounted Price for each category. The Category column seems redundant to me. Also I assume that Sales ID goes in the ID column, but what goes in the S. No column?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Maybe something like this?

2020-06-18_12-01-37.png
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This includes some changes to the the userform. It's not complete, but you can test the data entry.
29185709.xlsm

Author

Commented:
hi,
S.No is an automated series starts with 1, 2 ..etc ( thats not needed )
new format seems fine, as expected , but when i move to price tab , pull down menu doesnt show the ones selected in the lstRight tab.
also on lstLeft tab , can i add more list or i need to go excel inorder to add more list categories . 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay I've fixed that but I don't understand what you want to do with that combobox. I also changed lstLeft to multiselect so you can move more than 1 at a time to lstRight.
29185709a.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
also on lstLeft tab , can i add more list or i need to go excel inorder to add more list categories .
The best thing to do is to add them to the List sheet as I did.

Author

Commented:
Hi Martin,

Highly appreciate your support in providing help in vba coding, based on my requirement.

I have certain clarifications here


1. As per layout above from Database 2 tab, i would need ID to be included for all rows, so i can filter it individually using pivot later .

when i was trying to add a sample, it is showing this message and not reflecting in excel sheet
3. Could you please show me the reset button to make lstRight to Null on what has been selected . Cancel is not needed . 
Sid 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In the workbook you have I added validations that make sure that fields in the userform aren't left blank. In other words I assume that the user needs to enter them all. Is that assumption correct? There should also be other validations like for Discount percent. For example it should be a positive numeric value, but is there a reasonable maximum that it can be? If so then what should the maximum be? And how do you want to enter say 5%? Would it be 5 or .05? Are there other validations?

Also if Discounted Price is always LIst Price minus the discount percent, then Discounted Price on the userform isn't necessary; it can be done via a formula in the Database worksheet.

Author

Commented:
Hi Martin,
You are right for the below notes:

1. Discount it should be value as percent .. eg : 5% in which percentage will be displayed in text box .
2. Discounted price will be computed automatically. Hence we can disable manual entry .
3. All entries are must to be added.
Also can you help me to add data below Sales ID, date picker type . 
Regarding my first point, with Database 2 tab , i like ID column to have sales id name all over the contents, so i can transfer to pivot table or BI for dashboard analysis . 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Also can you help me to add data below Sales ID, date picker type .  
I don't know what you mean, but let's finish this question first.

I think I finally understand what the "Selected Choice" combobox is for. Please verify that it's there so that you can enter different prices and discounts for each of the selected categories.

Also please let me know which version of Excel you are using because I'd like to remove the Selected Choice combobox and the other textboxes on the Price page and use a Listview control instead, and I want to make sure that your Excel version won't have problems with that control.

Author

Commented:
hello martin,

selected choice is there, but when i enter the field , it doesnt reflect in excel sheet . Error displays" More info needed etc..."
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
selected choice is there,...
Yes I know. I just updated my previous post. Please take a look again.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Would this be acceptable? Instead of the selected dropdown the Categories would appear in the left column and you would enter the price and discount in the next two columns.
2020-06-19_12-46-04.png
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I went ahead and made the changes.
29185709b.xlsm

Author

Commented:
Thanks Martin. So this means for number of options i had in excel, max limit will be only 8 and cannot be increased as in this code right .

Sid
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How many would you want?

Author

Commented:
Hi Martin - Max to 12 entries from 8 would be fine. 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay here are 12.
29185709c.xlsm

Author

Commented:
Hi Martin,

i would like to thank for your patience in redesigning the vba form as per my requirement .

1. Before i close it up . i would like to know couple of points. As the entries comes in multiple row, will i be able to filter the data in pivot ( ID with particular selected choice such as vendor) . it can be count or price wise etc

in addition to what provided , i would like to edit the entry in the form . could you please advise how to .

Regards
sid
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks Martin and team . you are amazing as always  :) 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's a version of the workbook that is only 38 KB in size.
29185709d.xlsm
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.