Combobox

[mar050703]

Hi

I have 2 comboboxes in a userform, I have populated the first (called "Dept") listing the enteries using Additem ("name") etc.

I require 2nd (called "Employee") to be populated dependant on the choice of the first. The choice would be from a list in Excel (Sheet = Employees) and the list of employees is Column C (C2:???) and the matching Dept is in Column B (B2:???)

Therefore if Dept=Accounts, I want only the employees listed in column C to show if they are in Accounts (Column B)

Many Thanks

 

[mar050703]

Goeff thanks, i think i am going to change that bit anyway, with out the xRow bit, it is fine.

As part of this same programm I have created a second userform with a combobox, this will have the same choice as the Dept Combobox. I have copied the code to the new combox (called Ndept)and changed the relevant bits in the code, but I do nto get a selection, when running the code.

What type of event should this be for a stand alone, I have tried Private Sub NDept_Activate(). I just don't understand

Thanks again

 

[xlbo]

By "have the same choice as the Dept Combobox" do you mean it sahould have the same list of options available or that it should inherit the Dept selcted in the 1st combobox?

If the latter then NDept.Value = Holiday.Dept.Value should work

If the former then just set it up exactly as you have doen in the 1st userform

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mar050703]

I Have literally copied the code and changed from Dept to NDept, but I get no options within the combobox. I clearly am missing something, but can nto see what.

 

[xlbo]

please post the code - there may be a typo somewhere

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mar050703]

I have discovered that the Additem is not actually controlling the Dept combobox, but the following code is (as per stefen054)

Private Sub Dept_Change()


Dim lasrow As Long
Dim i As Integer: i = 1

Me.Employee.Clear
With Sheets("Employees")
lastrow = .Range("A65536").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, 2) = Dept.Text Then
Me.Employee.AddItem CStr(.Cells(i, 3))
Debug.Print CStr(.Cells(i, 3))
End If

Next
End With

End Sub

Within sheet Employees in Column A i have the list of Departments (this is an overflow from previous code I tried) Whenever I delete a name from column in this sheet I get a blacnk where that was. Therefore Additem does not work on either.

 

[xlbo]

Additem DOES work - your process doesn't

That's what tends to happen when you clear things out of lists and leave blanks in there

As a general note, you appear to be changing the code you are using, experiencing errors and then asking why without showing the code you are using. To be honest, I am completely lost as to what you are talking about now

Please post:

The code that populates the DEPT combobox
The code that populates the RMPLOYEES combobox
The code that populates the NDEPT combobox

Please then explain where you are having issues within those pieces of code and we can go from there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

I am not an Excel guy (at all) but it seems that there are control object issues that are confusing the heck out of me. I agree with Geoff and suggest you post the asked for code.

If I may also suggest putting populating procedures into their OWN procedure.

Sub PopulateCombobox1()
' or properly...use a explicit name (eg. Dept, as you have done..

Sub PopulateDept()
Dim DeptStuff()
Dim var
DeptStuff = Array("Yadda1", "Yadda2", _
    "Yadda3", "Yadda4")
Dept.Clear
For var = 0 To Ubound(DeptStuff)
   Dept.AddItem DeptStuff(var)
Next
End Sub

Sub PopulateRMEmployees()
Dim EmployStuff()
Dim var
EmployStuff = Array("Whatever1", "Whatever2", _
    "Whatever3", "Whatever4")
RMEmployees.Clear
For var = 0 To Ubound(EmployStuff)
   RMEmployees.AddItem EmployStuff(var)
Next
End Sub

Now you can call the populating procedure anytime, and anywhere, you like.

Sub Userform_Initialize()
   Call PopulateDept
   Call RMEmploees
End Sub

on initializing, or

Sub Textbox1_Change()
   Call PopulateDept
   Call RMEmployees
End Sub

say if you need to do something because of a textbox change, OR as part of a logic statement:

If [i]variable[/i] > 13 Then
    Call PopulateDept

This also keeps separate logic operations...separate.

faq219-2884

Gerry
My paintings and sculpture

 

[Andrzejek]

I would also strongly encurage using Type of variables and pre-fixes for your Controls. it will be much easier to read your code:

Sub PopulateDept()
Dim [blue]str[/blue]DeptStuff() [blue]As String[/blue]
Dim [blue]int[/blue]var [blue]As Integer[/blue]
[blue]str[/blue]DeptStuff = Array("Yadda1", "Yadda2", _
    "Yadda3", "Yadda4")
[blue]cbo[/blue]Dept.Clear
For [blue]int[/blue]var = 0 To Ubound([blue]str[/blue]DeptStuff)
   [blue]cbo[/blue]Dept.AddItem [blue]str[/blue]DeptStuff([blue]int[/blue]var)
Next
End Sub

This way in your code it is clear what you are refearing to: Combo Box (cbo), Label (lbl), Integer (int), Text Box (txt) etc.

Just my $0.02

Have fun.

---- Andy

 

[mar050703]

Ok

I am sorry to have confused you all, I too am confused.

The code I have that seems to populate Comboboxes (Dept and Employee) is:

Private Sub Dept_Change()

Dim lasrow As Long
Dim i As Integer: i = 1

Me.Employee.Clear
With Sheets("Employees")
lastrow = .Range("A65536").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, 2) = Dept.Text Then
Me.Employee.AddItem CStr(.Cells(i, 3))
Debug.Print CStr(.Cells(i, 3))
End If

Next
End With

End Sub

The above code is working fine.

I thought the following code would populate combobox (NDept)

Private Sub NDept_Initialize()
NDept.AddItem ("Accounts")
NDept.AddItem ("Office")
NDept.AddItem ("Office TR")
NDept.AddItem ("Sales Int")
NDept.AddItem ("Sales Ex")
NDept.AddItem ("Adblue")
NDept.AddItem ("Goods In")
NDept.AddItem ("WHS")
NDept.AddItem ("MFG")
End Sub

Thinking about it to cause the combobox (Dept and NDept) to populate from the list in Sheet Employees Column A is fine. So on that basis, what code i required to populate NDept.

Once I get this sorted, I will do as Andy suggests and prefix with the type of control (Combo Box (cbo), Label (lbl), Integer (int), Text Box (txt) etc.)

Thanks again

 

[mar050703]

I have just noticed that the code

Private Sub Dept_Change()

Dim lasrow As Long
Dim i As Integer: i = 1

Me.Employee.Clear
With Sheets("Employees")
lastrow = .Range("A65536").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, 2) = Dept.Text Then
Me.Employee.AddItem CStr(.Cells(i, 3))
Debug.Print CStr(.Cells(i, 3))
End If

Next
End With

End Sub

is actually not workign propery, works fine up to ROW 10. Is this because I only have 9 choices (which starts in Col A Row 2) However there are over 50 people the code should look at. Employee is in Column C and their respective depts are in Column B.

 

[xlbo]

Ok - first things first

You havn't posted any code that populates the DEPT combobox. The code in the Dept_Change sub will populate ONLT the EMPLOYEE combo box when the Dept Combo box is CHANGED (hence the name of the sub - it runs only when the slection within the Dept combobox is changed)

As to why the Dept_Change sub is not working properly - yes - it is because you are only looping based on the rows in col A:

lastrow = .Range("A65536").End(xlUp).Row

Lastrow therefore = 9

Change this to
lastrow = .Range("B65536").End(xlUp).Row

and it should work fine

The code to populate NDEPT & DEPT should be the same except that the code to populate DEPT needs to go in the HOLIDAYS Userform INITIALISE event and the code to populate NDEPT needs to go in your 2nd form's INITIALISE event...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question