Look into the Match and Offset functions
http://www.techonthenet.com/excel/fo...ndex_alpha.php
this is a cross post, however, how can i post the link, if its not allowed ? i need 5 posts to post..thats...well..better dont say nothing (i am not beeing rude, just frustrated)
this post was posted 2 days ago on other forum, but since its already on page 7, i doubt i will get an answer, so better try here.
From a combobox named cboTM, when i chosse ("material", "worklabor" or "Equipments") the combobox named cboDM will display the data regarding the sheets ("material","worklabor" and "equipments").
So what i wanted is that data to fill the texboxes, like :
if i choose "materials" from cboTM, and option 1 ("B7") from cboDM, textemp and textHours will become blocked :
(sheet Material)
(textbox) textmeasure ="C7"
(textbox) textQuant="D7"
(textbox) textprice="E7"
(textbox) texttprice="F7"
same to Worklabor :
if i choose "worklabor" from cboTM, and option 1 ("B7") from cboDM, textQuant will become blocked :
(sheet Worklabor)
(textbox) textmeasure ="C7"
(textbox) textemp="D7"
(textbox) textHours="E7"
(textbox) textprice="F7"
(textbox) texttprice="G7"
and the same for Equipents....etc..
Look into the Match and Offset functions
http://www.techonthenet.com/excel/fo...ndex_alpha.php
OR use more columns in your combo box and reference to the additional columns
Thks NoS
Tom, while you have 5 posts now, you could have just taken out the "www." or "http://" from the link and parsed it as text, just so the forum software didn't recognize it as a URL and it would've accepted it just fine.![]()
Regards,
Zack Barresse
Thks Zac, its done.
http://www.excelforum.com/excel-prog...ox-choice.html
i followed NoS Suggestion, and tried to put this working with Match and Index function, however after the first 2 textboxes filled, i am getting error on the next 2 (textprice and texttprice on red color).
The cells where he is taking the data are formated as currency. Aslo the textboxes named textprice and texttprice are formated as currency too.
Code:Private Sub cboDM_Click() Dim resmat1 As Variant Dim resmat2 As Variant Dim resmat3 As Variant Dim resmat4 As Variant resmat1 = Application.Index(Range("'Material'!$B$7:$D$27"), _ Application.Match(cboDM.Value, Range( _ "'Material'!$B$7:$B$27"), 0), 2) textmeasure.Value = resmat1 resmat2 = Application.Index(Range("'Material'!$B$7:$D$27"), _ Application.Match(cboDM.Value, Range( _ "'Material'!$B$7:$B$27"), 0), 3) textQuant.Value = resmat2 resmat3 = Application.Index(Range("'Material'!$B$7:$D$27"), _ Application.Match(cboDM.Value, Range( _ "'Material'!$B$7:$B$27"), 0), 4) textprice.Value = resmat3 resmat4 = Application.Index(Range("'Material'!$B$7:$D$27"), _ Application.Match(cboDM.Value, Range( _ "'Material'!$B$7:$B$27"), 0), 5) texttprice.Value = resmat4
Attached the workbook for better help.
Can anyone help me how to solve this ?
Thks in advance
Those items are outside the range you are using for indexing.
yes, crap, i was so distracted, and didnt noticed that error at the time. Thk you NoS.
Meanwhile, on the other forum, i obtained the help i needed, and i could finally solved my doubt.
Thks to all that tried to help me here. Its our help that makes "noobs" like me evolve and learn.
here is the link of the other forum, with the situation solved.
http://www.excelforum.com/excel-prog...ox-choice.html
Last edited by TomBeaver; 2014-03-29 at 01:32 PM.
Bookmarks