Yes you can! These links will help: www.exceltrainingvideos.com/combobox/ www.exceltrainingvideos.com/display-list-of-all-files-in-folder-in-combo-box/ www.exceltrainingvideos.com/populate-combo-box-on-excel-userform-with-unique-items-using-vba/ www.exceltrainingvideos.com/tag/excel-vba-combobox-match-required/
Sir, this is very useful to me to create 2 combobox , but i need one help about this. How to get list value for combo box from specific workbook with specific wk sheet by opening a user form.
Hi, any question is there an easy way? we have a billiards club, we would like to see who can still play against. There is a max of 20 players there will be played three times together, so I want to put in the first "eg '1', for the first game, etc.) So I was wondering if there is an easier way than all inserting players (I did give them a name, for example in game '1', it is "Player1"; "Player2"; etc.), can you help, by an example?
You can use a looping process as demonstrated in this VBA tutorial: www.exceltrainingvideos.com/how-to-get-data-from-worksheet-into-combobox-and-textboxes-on-user-form/
Thanks for the video. I have a question, is there a way that we don't have to come back to add coding whenever a new category is created, like a new department in your example? As the dataset gets bigger, the 'select case' function could be tedious, also the name ranges.
Mr. Takyar, I have followed the coding when creating my user form with dependent comboboxes, but receive a "Compile error: Next without For" at the "Next I" section of the code. Do you have an idea why this is occurring? Thank you.
Most probably there is some error in your code. This link with a downloadable sample file will help: www.exceltrainingvideos.com/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Mr.Dinesh thank you very match for this helpful video. Can you tall me how can I do this to six drop-down list , I want it to be dependent drop-down lists , when I choose the first drop-down list the next one should be counting an information that related to the first one and so on .
Sir, I need help from you, In a form two combo box(Dependent) and 5 text box fill the data based on the combo box and its equivalent value respectively.
Hi sir, I want to compare three combobox and execute 4th output in textbox in excel userform . Such as when S= S3_Serious E=E2_High and A=A1_likely then RL=Medium like this there are 15 more conditions. Please help me to solve this issue. Regards, Sagar kasrung
Instead keep department. Add a video with first created rowsource. Department will be already there. Select Case Me.TextBox1.Text Or TextBox1 If an error orrucereds. Correct? Dinesh: Your video is good
Hi Kumar, Could you make another tutorial of 3 level of dependent combo box (2nd level dependent of 1st level, so does 3rd level depends on 2nd level) ? Hope that you able to make one. Thanks!
Sir please help M having 2 combobox, what I want is if I select value of 1st combobox which shows column A values, the other combo box should show value of same row which are in column B......and vice versa ( if choose value in combo box 2 first combobox should get value of same row from column a) please help
sir, I need your help, when I enter any value in multiline text box on my VBA form and click on submit button it doesn't show align in my output window
@@Exceltrainingvideos I think he was asking, as I am now asking...what if I have multiple sheets and the sheet containing my data is not my active sheet? Sheet1 contains my data, but Sheet2 is where the user will be when they activate the form. Using your code, it kept trying to rename a range on Sheet2 not on Sheet1. I tried various Thisworkbook.Worksheets, even tried to name a variable as a worksheet...all to no avail, the code either did not work or referred to Sheet2 as the active sheet (yes I renamed ActiveSheet for lastcolumn to an actual sheet). I think the issue arises with the "With Range(cells(1, i), Cells(lastrow, i) - section of the code, as it is not specific as to where it should be a range - oddly the range selected in Sheet2 is actually the size of the range in Sheet1 when selected because lastrow and lastcolumn are specified. How do I specify Sheet1 for that section of the code?
@Dinesh Kumar Takyar Just to clarify, if I run the code from Sheet1, it works fine - but that isn't where I want the user to be (in fact I want to hide this sheet so they can't mess around with it).
@@Exceltrainingvideos For anyone that comes across this question, as I did, a workaround is to place Worksheets("Sheet1").Activate at the start of that section of code, and then place Worksheets("Sheet2").Activate at the end of the code. This works for me, even if sheet1 is hidden.
Hi, I have a piece of code9below) that finds the last row in a data sheet and then adds values from the controls on the user form ( all controls a named Reg 1-10 except for a combo box I have named cbo name. 'change the number for the number of controls on the userform cNum = 10 'add the data to the selected worksheet Set nextrow = Sheets(sht).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) For X = 1 To cNum nextrow = Me.Controls("Reg" & X).Value how can I concantanate on the data sheet two of the controls values (cbobox and reg)onto one line in the cell ie : MaterialType with Material Description ie :Fitting - Pipe elbow
str = Tgt.Validation.Formula1 str = Right(str, Len(str) - 1) If Left(str, 4) = "INDI" Then lSplit = InStr(1, str, "(") str = Right(str, Len(str) - lSplit) str = Left(str, Len(str) - 1) str = Range(str).Value this function is used for indirect function but ican use the offset function in combobox drop down how does it is posible
Thanks it helps, but not completely, I want whereby I can select combobox1, and pick from combobox2 then lastly from combobox3 instances, select "Make" from combobox1, then on combobox2 select "Model" and on combobox3 "Type"... thanks
+sihdi I got this question too and you know what? Yes, you can and even should use this code (it's better practice than using "select case" in this case).
respected sir, i have 1St com. is product list and i need 2nd com. is batch no list, with exp. date list, & available qty so how i can use dependent combo boxes in it. please help... , please send u r email so i will send u my format of this...
Thank you very much. your video is very helpful. I'm getting run-time error 424 after entering "lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column" i'm using office 2016
Check your code again. If you copied and pasted from my website it won't work unless you perform adequate corrections. www.exceltrainingvideos.com/tag/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Will I be able to add new departments to the department range without having to adjust the code? is not please guide me to a link that I can learn to make the range dynamic
I tried what you said now I'm getting run-time error 1004 please check my codes? Thank you for your assistance. When I use F8 key the codes runs perfectly but when it reaches Column I, only the first row is selected then the error pops up. My data ends at column P and Row 52 Codes Shown Below Private Sub UserForm_Initialize() Dim lastrow As Long Dim lastcolumn As Long lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column With Worksheets("Reference") For I = 1 To lastcolumn With .Columns(I) lastrow = Reference.Cells(Rows.Count, I).End(xlUp).Row With Range(Cells(1, I), Cells(lastrow, I)) Range(Cells(1, I), Cells(lastrow, I)).Select Selection.CreateNames Top:=True End With End With Next I End With Me.cboFunds.RowSource = "Funds" End Sub
I really must express my gratitude at the help you freely giving to us in order to become a good learner. how can we make employees details like date of joining designation address contact details email id with picture. With Love and respect, Mohamed Yaseen
Private Sub UserForm_Initialize() Dim lastrow As Long Dim lastcolumn As Long lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column With Worksheets("barelling") For i = 1 To lastcolumn With .Columns(i) lastrow = barelling.Cells(Rows.Count, i).End(x1up).Row With Range(Cells(1, i), Cells(lastrow, i)) Range(Cells(1, i), Cells(lastrow, i)).Select Selection.CreateNames Top:=True End With End With Next i End With Me.ComboBox1.RowSource = "shift" End Sub i could not able to save this
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column: change to lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xltoleft).Column Make the same changes elsewhere also.
Hallo mr. Kumar
I tried 9 variants for my first VBA, only yours helped me. Thanks a lot!!!! You're incredible!!!!
Glad to hear that! Thank you!
Thanks much Dinesh! I loved making that first click and watching this code work. It's a big help in my latest project.
Thanks, I appreciate your easy pace and clear voice.
Thanks. Please share with your friends also.
Thank you for the great video. I would like to ask if we can create independent combo boxes from worksheet? Can I add .additem syntax?
Yes you can! These links will help:
www.exceltrainingvideos.com/combobox/
www.exceltrainingvideos.com/display-list-of-all-files-in-folder-in-combo-box/
www.exceltrainingvideos.com/populate-combo-box-on-excel-userform-with-unique-items-using-vba/
www.exceltrainingvideos.com/tag/excel-vba-combobox-match-required/
Amazing trick.. You are a really Smart Teacher.
Thank you! 😃 Please share with your friends.
Very useful, better than those instructors who just rush to finish, being slow and calm in important
This code is absolutely worked in my worksheet. Many Thanks for inputs.
Glad it helped! Please share the VBA tutorial with your friends also on social media.
habib, from algéria, thanks very much for these videos, they was very usefull for me, have a good time
Really awesome. Thank you. How can I create this with a clear or reset button on the form. Thank you!
Glad it helped! This link will help create the clear button on a form: www.exceltrainingvideos.com/add-code-clear-cancel-command-buttons-on-user-form/
Hello sir, I am watching and learning from your videos, Thank you.
+rupali gopale You're welcome!
With all my respect, thank you so much, Mr Dinesh Kumar Takyar!
Thanks very much Dinesh, this video was again exactly what I was looking for except I have set up my ranges as tables so will use table references.
Thanks Dinesh, great tips on auto named ranges and Select Case..
Sir, this is very useful to me to create 2 combobox , but i need one help about this.
How to get list value for combo box from specific workbook with specific wk sheet by opening a user form.
Hi, any question is there an easy way? we have a billiards club, we would like to see who can still play against.
There is a max of 20 players there will be played three times together, so I want to put in the first "eg '1', for the first game, etc.) So I was wondering if there is an easier way than all inserting players (I did give them a name, for example in game '1', it is "Player1"; "Player2"; etc.), can you help, by an example?
Interesting question.
What if we have more than 50 values for combo box1? Do we shall go to define all 50 cases in select case loop???
You can use a looping process as demonstrated in this VBA tutorial: www.exceltrainingvideos.com/how-to-get-data-from-worksheet-into-combobox-and-textboxes-on-user-form/
Sir,
I found this video is very much useful , thank you very much for sharing the same. Hope your help will be there in future too.
Kind regards.
Thanks you master from Santiago de Chile. How can I get access to private video? I'm only interested in vba.
Welcome!
Thanks for the video. I have a question, is there a way that we don't have to come back to add coding whenever a new category is created, like a new department in your example? As the dataset gets bigger, the 'select case' function could be tedious, also the name ranges.
Try creating dynamic ranges.
I tried doing that by putting list range in a table but how to include it in Me.cbobox.RowSource = dynamic table range
Mr. Takyar, I have followed the coding when creating my user form with dependent comboboxes, but receive a "Compile error: Next without For" at the "Next I" section of the code. Do you have an idea why this is occurring? Thank you.
Most probably there is some error in your code. This link with a downloadable sample file will help: www.exceltrainingvideos.com/create-dependent-combo-boxes-in-excel-user-form-with-vba/
thanku sir....it helped me so much....
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
How this working without defining variable i ? VB Error ' Variable not defined '
Mr.Dinesh thank you very match for this helpful video. Can you tall me how can I do this to six drop-down list , I want it to be dependent drop-down lists , when I choose the first drop-down list the next one should be counting an information that related to the first one and so on .
www.exceltrainingvideos.com/create-dependent-combo-boxes-in-excel-user-form-with-vba/
But how does it work if the first ComboBox is also dynamic? It is possible to create dependent combo boxes if both are dynamic?
Yes
Thank you sir I loved this tutorial Shalom , salaam
Glad to hear that! Please share the VBA tutorial with your friends also on social media.
Sir, I need help from you, In a form two combo box(Dependent) and 5 text box fill the data based on the combo box and its equivalent value respectively.
OK
Why but.
sir is there a way wherein you can add all your data and save in module without creating anything on spreadsheet & give commands to run userform.
Is it possible to make the case statement dynamic as well?
Yes. What have you tried?
I have to thank you Sir, this video will help me in my project, Thank you
+Samir ALGIERS Glad that this video was helpful.
Hi sir,
I want to compare three combobox and execute 4th output in textbox in excel userform . Such as when S= S3_Serious E=E2_High and A=A1_likely then RL=Medium like this there are 15 more conditions.
Please help me to solve this issue.
Regards,
Sagar kasrung
You can use IF with AND.
Thanks for sharing such useful videos.
+Manoj Sharma Thanks.
Instead keep department. Add a video with first created rowsource.
Department will be already there.
Select Case Me.TextBox1.Text Or TextBox1
If an error orrucereds.
Correct?
Dinesh: Your video is good
Sir, can u create this condition without user form in two drop down ActiveX control form in excel
This link will guide: www.exceltrainingvideos.com/populate-list-box-text-box-with-excel-data-using-vba/
Hi Kumar,
Could you make another tutorial of 3 level of dependent combo box (2nd level dependent of 1st level, so does 3rd level depends on 2nd level) ? Hope that you able to make one. Thanks!
Sir please help M having 2 combobox, what I want is if I select value of 1st combobox which shows column A values, the other combo box should show value of same row which are in column B......and vice versa ( if choose value in combo box 2 first combobox should get value of same row from column a) please help
This link will guide: www.exceltrainingvideos.com/tag/how-to-populate-combo-box-automatically/
Excellent! Keep it UP
Thank you Mr. Takyar, Your tuition method is good and helpful.
sir, I need your help, when I enter any value in multiline text box on my VBA form and click on submit button it doesn't show align in my output window
Very Helpful Video Sir...Thanks for your kind help...:)
How i can do that with multiple sheets ?
This link will help: www.exceltrainingvideos.com/transfer-specific-data-to-specific-sheets-automatically/
Or search www.exceltrainingvideos.com
@@Exceltrainingvideos I think he was asking, as I am now asking...what if I have multiple sheets and the sheet containing my data is not my active sheet? Sheet1 contains my data, but Sheet2 is where the user will be when they activate the form. Using your code, it kept trying to rename a range on Sheet2 not on Sheet1. I tried various Thisworkbook.Worksheets, even tried to name a variable as a worksheet...all to no avail, the code either did not work or referred to Sheet2 as the active sheet (yes I renamed ActiveSheet for lastcolumn to an actual sheet). I think the issue arises with the "With Range(cells(1, i), Cells(lastrow, i) - section of the code, as it is not specific as to where it should be a range - oddly the range selected in Sheet2 is actually the size of the range in Sheet1 when selected because lastrow and lastcolumn are specified. How do I specify Sheet1 for that section of the code?
@Dinesh Kumar Takyar Just to clarify, if I run the code from Sheet1, it works fine - but that isn't where I want the user to be (in fact I want to hide this sheet so they can't mess around with it).
@@Exceltrainingvideos For anyone that comes across this question, as I did, a workaround is to place Worksheets("Sheet1").Activate at the start of that section of code, and then place Worksheets("Sheet2").Activate at the end of the code. This works for me, even if sheet1 is hidden.
Hi Dinesh Kumar Takyar how can i remove or hide close button?
You can use the 'visible' property to hide or show the button.
Hi,
I have a piece of code9below) that finds the last row in a data sheet and then adds values from the controls on the user form ( all controls a named Reg 1-10 except for a combo box I have named cbo name.
'change the number for the number of controls on the userform
cNum = 10
'add the data to the selected worksheet
Set nextrow = Sheets(sht).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
how can I concantanate on the data sheet two of the controls values (cbobox and reg)onto one line in the cell ie : MaterialType with Material Description ie :Fitting - Pipe elbow
Use the concatenate function (&).
Thank you Sir, I tried that but I got errors
Saved my day!
Sir how to add more than 3 cases
This VBA tutorial will guide: www.exceltrainingvideos.com/create-dependent-combo-boxes-in-excel-user-form-with-vba/
str = Tgt.Validation.Formula1
str = Right(str, Len(str) - 1)
If Left(str, 4) = "INDI" Then
lSplit = InStr(1, str, "(")
str = Right(str, Len(str) - lSplit)
str = Left(str, Len(str) - 1)
str = Range(str).Value
this function is used for indirect function but ican use the offset function in combobox drop down
how does it is posible
This code is not working for me.
Thanks it helps, but not completely, I want whereby I can select combobox1, and pick from combobox2 then lastly from combobox3 instances, select "Make" from combobox1, then on combobox2 select "Model" and on combobox3 "Type"... thanks
why we cannot just code:
Me.ComboBox2.rawsourse = Me.ComboBox1 ?
+sihdi I got this question too and you know what? Yes, you can and even should use this code (it's better practice than using "select case" in this case).
sihdi
hey how do you now this
respected sir,
i have 1St com. is product list and i need 2nd com. is batch no list, with exp. date list, & available qty so how i can use dependent combo boxes in it. please help... , please send u r email so i will send u my format of this...
For i variable not defined.
Thank you very much. your video is very helpful. I'm getting run-time error 424 after entering "lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column" i'm using office 2016
Check your code again. If you copied and pasted from my website it won't work unless you perform adequate corrections. www.exceltrainingvideos.com/tag/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Will I be able to add new departments to the department range without having to adjust the code? is not please guide me to a link that I can learn to make the range dynamic
I tried what you said now I'm getting run-time error 1004 please check my codes? Thank you for your assistance. When I use F8 key the codes runs perfectly but when it reaches Column I, only the first row is selected then the error pops up. My data ends at column P and Row 52
Codes Shown Below
Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastcolumn As Long
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets("Reference")
For I = 1 To lastcolumn
With .Columns(I)
lastrow = Reference.Cells(Rows.Count, I).End(xlUp).Row
With Range(Cells(1, I), Cells(lastrow, I))
Range(Cells(1, I), Cells(lastrow, I)).Select
Selection.CreateNames Top:=True
End With
End With
Next I
End With
Me.cboFunds.RowSource = "Funds"
End Sub
"Reference" need to be the name of the sheet (Ie "Sheets("Sheet1".Cells(Rows ..... etc)
Country>state>district>village this way sir please help me sir
Use the strategy you just learnt: www.exceltrainingvideos.com/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Sir tried and failed from my end
hey its very very very good
Instead:
With Range(Cells(1, I), Cells(LastRow, I))
Range(Cells(1, I), Cells(LastRow, I)).Select
Selection.CreateNames Top:=True
End With
put this:
Range(Cells(1, I), Cells(LastRow, I)).CreateNames Top:=True
+Matt C Great!
+Matt C Good Code
@@ExceltrainingvideosYou are genius. clear voice
Thanks... Thanks... Thanks.
Most welcome! Please share the VBA tutorial on dependent combo boxes on user-form with all your friends also on social media.
compile error..."viable not define " why this ??
Did you declare the variable? This Excel VBA tutorial will help: ruclips.net/video/b0zZjoYzz68/видео.html
@@Exceltrainingvideos Dear sir pleas let me know you email no
@@Exceltrainingvideos done thanks and how to add another combobox ?
I really must express my gratitude at the help you freely giving to us in order to become a good learner.
how can we make employees details like date of joining designation address contact details email id with picture.
With Love and respect,
Mohamed Yaseen
+Md Yaseen Thank you!
+Md Yaseen These links might help:
www.exceltrainingvideos.com/advanced-user-form/
ruclips.net/video/yLyPe_M1sNc/видео.html
NICE VIDEO SIR,MAY I HAVE YOUR EMAIL ID
Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastcolumn As Long
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column
With Worksheets("barelling")
For i = 1 To lastcolumn
With .Columns(i)
lastrow = barelling.Cells(Rows.Count, i).End(x1up).Row
With Range(Cells(1, i), Cells(lastrow, i))
Range(Cells(1, i), Cells(lastrow, i)).Select
Selection.CreateNames Top:=True
End With
End With
Next i
End With
Me.ComboBox1.RowSource = "shift"
End Sub
i could not able to save this
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column: change to
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xltoleft).Column
Make the same changes elsewhere also.
Be enthusiastic on your next video,you had very lousy voice
Thanks Dinesh, great tips on auto named ranges and Select Case..
+krn14242 You're welcome!