Combo Boxes with VBA Code

[GN0001]

I have 3 combo boxes that the entry on the second combo box is restricted to what we select on first combo box and the entries of the third combo box is restricted to what we select on the second combo box.


we tried to do this task graphically, there was no success, my coworker wrote a code and combo boxes worked perfect.


I started a new workbook and entered the code into the visual basic sheet. It doesn't work any more.


I can upload the code and the workbook if you want.


The second problem is when I move the data from the sheet where data is, the code stops working because the cell reference are changes.


How codes can be modified that when combo boxes are in one sheet and the data is another sheet, the combo boxes still work?


This is the code, if you want I can upload the file itself.


Private Sub ComboBox1_Click()


On Error GoTo ErrorExit


Range("E1") = ComboBox1

Range("E2") = ""

Range("E3") = ""


ComboBox2.Clear

ComboBox3.Clear


Dim Counter As Integer

Dim rR As String

Dim bAddOk As Boolean

Dim i As Integer


Counter = 1

rR = "H1"


Do Until Range(rR) = "" 'it starts loop to begin adding all matching contract numbers for the state selected in combo box 1 into combo box 2


rR = "H" & Counter 'Loop Counter to go down State list in Column H


If ComboBox1 = Range(rR) Then 'Checks if state matches chosen combo box1 State


bAddOk = True


For i = 0 To Me.ComboBox2.ListCount - 1 'checks combobox2 to see if new item is already there


If Me.ComboBox2.Column(0, i) = Range("I" & Counter) Then

bAddOk = False

End If


Next i


If bAddOk = True Then 'If the item wasn't in the list, bAddOk is true and Ok to add to list


ComboBox2.AddItem Range("I" & Counter)


End If


End If


Counter = Counter + 1


Loop


ErrorExit:


End Sub


Private Sub ComboBox2_Click()

On Error GoTo ErrorExit


Range("E2") = ComboBox2

Range("E3") = ""


ComboBox3.Clear


Dim Counter As Integer

Dim rR As String

Dim bAddOk As Boolean

Dim i As Integer


Counter = 1

rR = "H1"

rS = "I1"


Do Until Range(rR) = "" 'Starts loop to begin adding all matching ID for the state & Contract number selected in combobox1 & combo box 2 into combo box 3

rR = "H" & Counter 'Loop counter to go down state list in column H

rS = "I" & Counter


If ComboBox1 = Range(rR) And ComboBox2 = Range(rS) Then


bAddOk = True


For i = 0 To Me.ComboBox3.ListCount - 1 'checks combobox 3 to sse if new item is already there

If Me.ComboBox3.Column(0, i) = Range("J" & Counter) Then

bAddOk = False

End If


Next i


If bAddOk = True Then 'if the item wasn't in the list, bAddOk is true and Ok to add to list

ComboBox3.AddItem Range("J" & Counter)

End If

End If


Counter = Counter + 1


Loop


ErrorExit:


End Sub


Private Sub ComboBox3_Click()


On Error GoTo ErrorExit


Range("E3") = ComboBox3


ErrorExit:


End Sub


Thank you for the help.

 

[NARAYANK991]

Hi Guity ,


I copied your code into a blank worksheet , and introduced 3 ActiveX combo boxes , and entered some data for them into columns H , I and J.


Everything works well.


Narayan

 

[GN0001]

When I copy and paste code into the code window, it doesn't work for me. I will try again. Also I need to put the data in a sheet where combo boxes are not there, the cell reference will be changed. for example: if we have Cell H, when I move the data into a different sheet, the cell H reference would be different. Then what that cell reference would be? workbook.worksheet.H1?

 

[GN0001]

Hello Narayan,

I copy and pasted the code into a new workbook and run the code and nothing happened.

Guity