To give a direction !!! try to work with variable input(s)
Dim xx As Variant, x As Interger, y As Interger

sub -----
xx = 2
x = 1
y = 3
Worksheets(xx).Cells(x,y).value = SupervisorComboBox.Value
in stead of Sheets("Sheet2").Range("C1") = SupervisorComboBox.Value
now you can work with counter(s) x,y and xx which reacts to the
usersanswer to direct you wishes

regard
Hans





On 25 aug, 23:56, "Trevor Shuttleworth"
wrote:
An example:

Sub test()
Dim NextRow As Long
If Range("C65536").End(xlUp).Row = 1 Then
NextRow = 1
Else
NextRow = Range("C65536").End(xlUp).Offset(1, 0).Row
End If
MsgBox NextRow
End Sub

So your code would become:

Dim NextRow As Long
With Sheets("Sheet2")
If .Range("C65536").End(xlUp).Row = 1 Then
NextRow = 1
Else
NextRow = .Range("C65536").End(xlUp).Offset(1, 0).Row
End If

.Range("C" & NextRow) = SupervisorComboBox.Value
End With

Regards

Trevor

"chadtastic" wrote in message

...





Hi all,

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

I have the following code written inVBA.

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 inVBA?

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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -