VBA/Excel help - Coding ComboBoses......

[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

 

[Trevor Shuttleworth]

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

 

[hans.van.snick]

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