[SOLVED] Combobox filtered from another combobox


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 1st February 2017, 18:21
silvapereira's Avatar
silvapereira silvapereira is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Some VBA experience
 
Join Date: Feb 2017
Location: Lisbon, Portugal
Posts: 28
Posts Per Day: 0.10
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (1) silvapereira is on a distinguished road
Portugal
Default Combobox filtered from another combobox
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

I'm a beginner on excel. I know some VB access.
I would like you to help me with the following question:
I have a userform with 3 combobox (CbEntity, CbCategory, CbProduct) and a TextBox (TxtPrice).
I want to filter the CbCategory data according to the CbEntity data, and the CbProduct data refer to CbEntity and CbCategory. At the end filtered all the data would appear the value in the textbox.
Example:

Entity - Category - Product - Price
ADSE - Visit - ORL -30
ADSE - Surgery - HT + AB - 400
Multicare -Surgery -HT + AB - 550
Remember - Surgery -HT + AB + LPB + LLA - 1200
ADSE - USscan -Pelvic - 80
Medis - SurgeryHelper - HT + AB - 120
Medis Surgery -OM + LPB + Apedc -680
Recalls - Surgery - HT + AB - 700

The price varies according to the entity and the product. Equal products have different prices depending on the entidade.

On the other hand I liked that the combos did not show repeated values.

The cbCategory only shows values of the Entity selected and the cbProduct only presents values related to the Entity and the selected Category.

After that, the price automatically appears in txtPrice.
Is this possible? I'm sure it is, but i'm a kind of stucked here.

I hope I'm not too confused and look forward to your help
Thank you very very much



The Code Cage Advertisment
Advertisement
  #2  
Old 3rd February 2017, 10:23
Ingolf's Avatar
Ingolf Ingolf is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Oct 2011
Location: KK
Posts: 22
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 3
Thanked 2 Times in 2 Posts
Reputation: 60 (7) Ingolf will become famous soon enough
Peru
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Upload a workbook with all date.



  #3  
Old 4th February 2017, 13:01
silvapereira's Avatar
silvapereira silvapereira is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Some VBA experience
 
Join Date: Feb 2017
Location: Lisbon, Portugal
Posts: 28
Posts Per Day: 0.10
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (1) silvapereira is on a distinguished road
Portugal
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


This is what I have done so far ...
Hope you can help me.
Thanks
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Old 4th February 2017, 17:16
Ingolf's Avatar
Ingolf Ingolf is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Oct 2011
Location: KK
Posts: 22
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 3
Thanked 2 Times in 2 Posts
Reputation: 60 (7) Ingolf will become famous soon enough
Peru
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Is this what you want?
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.


Last edited by Ingolf; 4th February 2017 at 17:19.


  #5  
Old 4th February 2017, 19:19
silvapereira's Avatar
silvapereira silvapereira is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Some VBA experience
 
Join Date: Feb 2017
Location: Lisbon, Portugal
Posts: 28
Posts Per Day: 0.10
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (1) silvapereira is on a distinguished road
Portugal
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Thanks Ingolf.

Almost there ...

Can you please make combEntity not have duplicate values?
and the receipt field also be automatically filled??
I'll appreciate that.
thanks very very much



  #6  
Old 4th February 2017, 19:32
Ingolf's Avatar
Ingolf Ingolf is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Oct 2011
Location: KK
Posts: 22
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 3
Thanked 2 Times in 2 Posts
Reputation: 60 (7) Ingolf will become famous soon enough
Peru
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by silvapereira View Post

Thanks Ingolf.

Almost there ...

Can you please make combEntity not have duplicate values?

Did you try the code?
Where are duplicate in combEntity?
If you look carefully, the name are not identical
(SAMS Quadros and SAMS-Quadros) if you correct in database you will see it will no be duplicate.

Quote:
and the receipt field also be automatically filled??

You say nothing, about this in first post.


Last edited by Ingolf; 4th February 2017 at 19:36.


The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #7  
Old 4th February 2017, 20:10
silvapereira's Avatar
silvapereira silvapereira is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Some VBA experience
 
Join Date: Feb 2017
Location: Lisbon, Portugal
Posts: 28
Posts Per Day: 0.10
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (1) silvapereira is on a distinguished road
Portugal
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by Ingolf View Post

Did you try the code?
Where are duplicate in combEntity?
If you look carefully, the name are not identical
(SAMS Quadros and SAMS-Quadros) if you correct in database you will see it will no be duplicate.


You say nothing, about this in first post.

You are right. Very right
Thank you very much for your help



  #8  
Old 4th February 2017, 20:26
Ingolf's Avatar
Ingolf Ingolf is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Oct 2011
Location: KK
Posts: 22
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 3
Thanked 2 Times in 2 Posts
Reputation: 60 (7) Ingolf will become famous soon enough
Peru
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by silvapereira View Post

and the receipt field also be automatically filled??
I'll appreciate that.
thanks very very much

Replace all
VBA CODE:
Private Sub CombProduto_click() 

with


VBA CODE:
Private Sub CombProduto_click() Me.tbPreço = ""
Set k = Sheets("ProductPrice") For Each c In k.Range("A2:A" & k.[A65000].End(xlUp).Row) If c = Me.CombEntidade And c.Offset(, 1) = Me.CombCategoria And c.Offset(, 2) = Me.CombProduto Then Me.tbPreço = c.Offset(, 3) Me.tbRecebido = c.Offset(, 5) End If Next c End Sub


Last edited by Ingolf; 4th February 2017 at 20:28.


1 members found this post helpful.
The Following User Says Thank You to Ingolf For This Useful Post:
silvapereira (4th February 2017)
  #9  
Old 4th February 2017, 20:48
silvapereira's Avatar
silvapereira silvapereira is offline
Junior Member
MS Office Version: Ms Office 2013
MS Office Skill Level: Some VBA experience
 
Join Date: Feb 2017
Location: Lisbon, Portugal
Posts: 28
Posts Per Day: 0.10
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (1) silvapereira is on a distinguished road
Portugal
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


Ingolf
It Works perfectely, thank you.



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #10  
Old 4th February 2017, 22:59
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.51
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Combobox filtered from another combobox

------ Register to get rid of these "In Post" ads! ------


silvapereira,

How To Say Thanks!
How to say thanks!

Saying Thanks

If you have found a tip helpful then please click on the button (like the one on the left) to the bottom right of the post to register your appreciation. If you also found a particular post helpful click the green thumbs up button on that post too so others can quickly find a helpful post!

Saying thanks this way or marking as helpful ensures the person whose post you are marking gets reputation points



__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
combobox, filtered

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 09:47.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.22474 seconds
  • Memory Usage 6,641KB
  • Queries Executed 16 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (5)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (4)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (10)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (10)post_thanks_box
  • (1)post_thanks_box_bit
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (2)postbit_attachment
  • (10)postbit_ctoptip
  • (10)postbit_onlinestatus
  • (20)postbit_reputation
  • (10)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (2)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • postbit_attachment
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete