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

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 chosen
supervisor. The employee's name gets stored in cell, "D1"

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

Right now, if the user goes back and chooses new names from the
ComboBoses, the newly chosen name just gets REPLACED in the cells, "C1"
and, "D1." (Which makes sense because the code specifically states to
store in these cells.) HOWEVER.......I WANT EACH CLICK EVENT OF EACH
COMBOBOX TO STORE THE DATA DOWN THE ROWS OF, "C' and, "D." I want the
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 chosen
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 also
SAVE the entries that were stored in Sheet2??

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

Chad




--
chadtastic

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

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?




"chadtastic" wrote:


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 chosen
supervisor. The employee's name gets stored in cell, "D1"

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

Right now, if the user goes back and chooses new names from the
ComboBoses, the newly chosen name just gets REPLACED in the cells, "C1"
and, "D1." (Which makes sense because the code specifically states to
store in these cells.) HOWEVER.......I WANT EACH CLICK EVENT OF EACH
COMBOBOX TO STORE THE DATA DOWN THE ROWS OF, "C' and, "D." I want the
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 chosen
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 also
SAVE the entries that were stored in Sheet2??

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

Chad




--
chadtastic