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

 

[Andrzejek]

Show us your code of how you populated the first combo box. It will be easier to show you how to do the same with the second one using the same approach since you can do this in many ways.



Have fun.

---- Andy

 

[SkipVought]

The DEPT will be the CRITERIA for a Query returning EMPLOYEE. Use the resultset to populate your second combo.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[mar050703]

Ok the code I have for the 1st combobox is:

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

If there is a simpler way to do this allowing for the 2nd combobox then I am open to suggestions.

I am only self taught in VBA!

 

[xlbo]

This is not the most efficient way to do it but uses a similar technique as you have already used

dim lRow as long, strDept as string, i as long

with sheets("Employees")

  lRow = .cells(65536,3).end(xlup).row
  strDept = controls("Dept").value

  Employee.clear

  For i = 1 to lrow
    if .cells(i,2).value = strDept then
      Employee.additem .cells(i,3).value
    end if
  next i
end with

A more efficient way would be to (as Skip has suggested) based the rowsource of the combobox on a SQL string which returns a list of employees in Col C using a WHERE criteria based on column B

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

 

[xlbo]

btw - most people on here are self taught in VBA. Not sure why you think that is an issue for you...

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]

Thanks Geoff

Is the code you have used for combobox 1 or 2 or for both?

 

[xlbo]

For your dependant combo box

This would be run on the CHANGE event of your 1st combobox to repopulate the 2nd one

May need a bit of tweaking for the details but the basic concept is that you loop through the rows in your employee sheet and check the department against what has been selected in the 1st combo

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]

Thank, however I do not get a list in the dependant combobox (Employee), it is just blank. Combobox 1 (Dept) is an activate event, and Employee is a Change event. I assume the ".cells(x,2) or 3 refere to the column numbers. ie columns B & C (which would be correct).

Thanks again

 

[combo]

Why do you populate the first control (Dept) when you activate it? If the user deactivate it and activate again, your list will duplicate entries. Typically the controls are filled with initial data by UserForm's Initialize event.
You need 'Change' event for the 'Dept' combobox to populate 'Employee'; 'Change' event for the 'Employee' will follow user's choice.

combo

 

[xlbo]

As per combo - populate the Dept combobox on userform initialise

Enter the code I gave you into the Dept change 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

 

[mar050703]

Thank you again.

However I now get a run-time error 70 - Permission Denied. The debug highlights 'Holiday.Show' in the code

Sub Button2_Click()
Holiday.Show
End Sub

This code is attached to a button in Excel which should bringup the userform (Holiday), up until the changes I made it did.

 

[xlbo]

Please post the code that is in the initialise event of the form

What I have posted should have no impact whatsoever on the ability to open the form itself

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]

Here is the code you requested.

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

This has not changed since the code I put in that you kindly suggested.

 

[xlbo]

That error would be associated with trying to access something that has security - are you making any calls to FileSystemObject or databases / email servers anywhere ?

Is there any other code associated with the form or other control son the form?

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

 

[stefen054]

Hi

Try this


Private Sub ComboBox1_Change()
Dim lastrow As Long
Dim i As Integer: i = 1

Me.ComboBox2.Clear
With Sheet1
lastrow = .Range("a65536").End(xlUp).Row
For i = 1 To lastrow
'column 1 has dept infor and column 2 has employee info
If .Cells(i, 1) = ComboBox1.Text Then
Me.ComboBox2.AddItem CStr(.Cells(i, 2))
Debug.Print CStr(.Cells(i, 2))
End If

Next
End With

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Maths"
ComboBox1.AddItem "English"
ComboBox1.AddItem "Tamil"
End Sub


Stefen

 

[mar050703]

Geoff

The codes are simple. i open the user form, which has the 2 comboboxes (previously mentioned), and 2 text boxes (where I enter start and finish dates.) The userform has an Ok button, which should then enter the data of the userform onto a sheet called dates, a clear button which should clear the userform, and a cancel button which should close the userform (if decide not to enter details).

There are no outside databases or anything.

Does anythign need to be option explicit?

 

[mar050703]

I have just done some tests, and it appears that the code that causes the problem is the Dept_initialise(). The one that populates Combobox 1 (Dept) with "Accounts, Office etc.

Any suggestions

 

[mar050703]

Hey Guys.

Thanks for your help, I have just solved it, I had Userform_Initialise() instead of Dept_Initialise.

However I have come against another problem.

The following code:

Private Sub Ok_Click()
Dim Holname As String
Dim Holfrom As Date
Dim Holto As Date
Dim xRow As Integer
Dim lcol As Integer
Dim ucol As Integer
Dim i As Integer


Holname = Employee.Text
Holfrom = DateFrom.Text
Holto = DateTo.Text
DateFrom = Format(Holfrom, "dd-mmm-yy")
DateTo = Format(Holto, "dd-mmm-yy")

Sheets("Dates").Select
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Employee.Value
ActiveCell.Offset(0, 1) = DateFrom
ActiveCell.Offset(0, 2) = DateTo

xRow = WorksheetFunction.Match(Holname.Value, Sheet2.Columns(1), 0)
lcol = WorksheetFunction.Match(Holfrom.Value, Sheet2.Rows(1), 0)
ucol = WorksheetFunction.Match(Holto.Value, Sheet2.Rows(1), 0)

For i = lcol To ucol
Sheet2.Cells(xRow, i).Value = "H"
Next

Seems to give an ivalid qualifier for Holname, which as you will see is the entry from the Employee combobox. The same idea also for the Date to and Date from text boxes (being formatted as dates.

Sorry about this.

 

[xlbo]

try .Value rather than .Text

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