Make VBA ComboBox_Change work in 3 sheets

[Chris]

Hello, I have a code that works if I got everything in 1 sheet.
However I want to split it into 3 sheets and still make it work.

Example (1 sheet): http://oi58.tinypic.com/28ahwk9.jpg

Example (3 sheets)
Sheet 1: http://oi62.tinypic.com/2qa1lhd.jpg
Sheet 2: http://oi60.tinypic.com/1ih9jr.jpg
Sheet 3: http://oi60.tinypic.com/2q35ef9.jpg

The code that is working for the first example is:

Option Explicit

Private Sub ComboBox4_Change()
Dim arrCode() As Variant
Dim arrList As Variant
Dim strCode As String
Dim Res As Variant
Dim idx As Long
Dim I As Long
Dim J As Long
Dim cnt As Long


arrList = ComboBox4.List


For I = LBound(arrList) To UBound(arrList)
arrList(I, 0) = CStr(arrList(I, 0))
Next I


idx = ComboBox4.ListIndex


cnt = 1


If idx <> -1 Then


For I = Len(ComboBox4.List(idx)) To 2 Step -1


ReDim Preserve arrCode(1 To 3, 1 To cnt)


strCode = Left(ComboBox4.List(idx), I)


Res = Application.Match(strCode, arrList, 0)


If Not IsError(Res) Then


For J = 1 To 3
arrCode(J, cnt) = IIf(Range("A" & Res + 1).Offset(, J).Value = "YES", strCode, "-")
Next J


cnt = cnt + 1


End If


Next I
End If


If cnt > 1 Then


Range("F13", Range("F13").End(xlDown).Offset(, 2)).ClearContents


With Range("F13").Resize(UBound(arrCode, 2), UBound(arrCode))
.NumberFormat = "@"
.Value = Application.Transpose(arrCode)
End With


End If


End Sub

I tried to change it to the 3-sheets-solution, but it's not working. (Combobox4 is now called Combobox1)

Option Explicit
Private Sub ComboBox1_Change()
Dim arrCode() As Variant
Dim arrList As Variant
Dim strCode As String
Dim Res As Variant
Dim idx As Long
Dim I As Long
Dim J As Long
Dim cnt As Long


arrList = Worksheets("Choose").ComboBox1.List


For I = LBound(arrList) To UBound(arrList)
arrList(I, 0) = CStr(arrList(I, 0))
Next I


idx = Worksheets("Choose").ComboBox1.ListIndex


cnt = 1


If idx <> -1 Then


For I = Len(Worksheets("Choose").ComboBox1.List(idx)) To 2 Step -1


ReDim Preserve arrCode(1 To 3, 1 To cnt)


strCode = Left(Worksheets("Choose").ComboBox1.List(idx), I)


Res = Application.Match(strCode, arrList, 0)


If Not IsError(Res) Then


For J = 1 To 3
arrCode(J, cnt) = IIf(Range("A" & Res + 1).Offset(, J).Value = "YES", strCode, "-")
Next J


cnt = cnt + 1


End If


Next I
End If


If cnt > 1 Then


With Sheets("Calculations")
Range("F13", Range("F13").End(xlDown).Offset(, 2)).ClearContents


With Range("F13").Resize(UBound(arrCode, 2), UBound(arrCode))
.NumberFormat = "@"
.Value = Application.Transpose(arrCode)
End With
End With


End If


End Sub

The problem seems to be that Excel cannot get the list of the combobox. What am I doing wrong here?

Thanks a lot.

 

[GS]

How does the combobox get its list?
Why are you converting the list to string data type when it's already
string data? (default for combobox is its .Text property)

Each sheet's combobox is a child of its respective sheet. Thus it
doesn't know anything about the other sheets unless you specify them by
name.

If the code for each combobox is common then put that code in a
standard module and call it from each sheet that needs to use it. If
done correctly, the code doesn't need to know which combobox is calling
it unless you need to ref the sheet it's on in the code. In this case
pass the sheet to the code as a ByRef argument.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion