Need Help with Coding a ComboBox in Excel/VBa......

[chadtastic]

Hi all,

Perhaps someone can help me..........

I have the following code written in VBA.

Private Sub SupervisorComboBox_Change()
Sheets("Sheet2").Range("C1") = SupervisorComboBox.Value

I have a ComboBox that lists ten supervisors.
When the user chooses a supervisor from the ComboBox drop down list
the name gets stored on the second Sheet, in cell, "C1".

Now then.......there is a second ComboBox underneath this first one
This ComboBox lists each employee that works under the chose
supervisor. The employee's name gets stored in cell, "D1"

My question is this - If the user clears the screen (there is
ClearScreen button on the form) and wants to choose a new superviso
and a new employee, how do I get the program to store the ne
supervisor and employee name in cells, "C2" and, "D2"
repsectively....?

Right now, if the user goes back and chooses new names from th
ComboBoses, the newly chosen name just gets REPLACED in the cells, "C1
and, "D1." (Which makes sense because the code specifically states t
store in these cells.) HOWEVER.......I WANT EACH CLICK EVENT OF EAC
COMBOBOX TO STORE THE DATA DOWN THE ROWS OF, "C' and, "D." I want th
rows to get filled up each time a new name is picked from the ComboBox
(So each time a supervisor is chosen, for example, I want the chose
entry to get stored in, "C1" then in, "C2", then in, "C3"
ect........Can anyone help me with the coding of this in VBA?

And when I clear the screen, how the heck do I get the program to als
SAVE the entries that were stored in Sheet2??

I'm sorry this was so long. I'm just trying to explain it as fully a
possible.
Pllease help, this is driving me mental. lol Thank you!

Cha

 

[Guest]

Revise:

Private Sub SupervisorComboBox_Change()
Sheets("Sheet2").Range("C1") = SupervisorComboBox.Value

to

Private Sub SupervisorComboBox_Change()
Dim lastRow As Long

lastRow = Sheets("Sheet2").Range("C" & Rows.Count). _
End(xlUP).Row + 1
Sheets("Sheet2").Range("C" & lastRow) = SupervisorComboBox.Value

similar logic for storing the employee name, but since I presume the
supervisor's name will already be in C, you either need this to get 'lastRow'
in that routine:

lastRow = Sheets("Sheet2").Range("D" & Rows.Count). _
End(xlUP).Row + 1
Sheets("Sheet2").Range("D" & lastRow) = EmployeeComboBox.Value

or
lastRow = Sheets("Sheet2").Range("C" & Rows.Count). _
End(xlUP).Row
Sheets("Sheet2").Range("D" & lastRow) = EmployeeComboBox.Value

For the rest, we need to see or be told exactly what is being cleared when
you do your Clear Screen operation. And where would you like this
information from Sheet2 to be saved at?