class module

Hi

The first time I run the following program, it doesn't work, but the 2nd
time and anytime after the 2nd time as long as the workbook's open, it's
OK. of course after each run , I delete the comboboxes.
Give it a try and I'm sure you'll be amazed.

Does anybody know how to fix it ? I know a workaround to make it run OK
but what I'm looking for is an real pure explanation.

Thanks for your help and I apologize for my English which I know isn't
that good.

'*********************************
To insert in a standard module
'*********************************

Dim TABCOMBO(1 To 50) As New cls
Sub creation()

Dim WS As Worksheet

Set WS = ActiveWorkbook.ActiveSheet

Set plageselect = WS.Range("A3:A9")

I = 1

For Each c In plageselect

'create each combobox
With c
Set dp = WS.OLEObjects.Add(ClassType:="Forms.Combobox.1",
Left:=.Left, Top:=.Top, Width:=12, Height:=12)
End With
dp.Name = "HZFILTER" & I
dp.Placement = 1

'feeds the comboboxes with values that are at the three cells right of
'comboboxes
For A = 1 To 3
dp.Object.AddItem c.Offset(0, A)
Next

I = I + 1

Next

On Error Resume Next
For I = 1 To WS.OLEObjects.Count 'here we guess the OLEObjects on the
'spreadsheet are the comboboxes which have been created by the code
'"Create"
valtempcombo = "HZFILTER" & I
Set TABCOMBO(I).FILTERH = WS.OLEObjects(valtempcombo).Object
Next

On Error GoTo 0

End Sub


'*********************************
To insert in a class module named cls
'*********************************
Public WithEvents FILTERH As ComboBox

Private Sub FILTERH_Click()

MsgBox "coocoo"

End Sub

By the way, if you don't declare dim ws as worksheet, it never works.I
hope me to have been specific.

Regards

Your code worked for me (after I declared the missing variable
declarations! declare your variables!) in Excel 2003.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"anonymousA" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> The first time I run the following program, it doesn't work,
> but the 2nd time and anytime after the 2nd time as long as the
> workbook's open, it's OK. of course after each run , I delete
> the comboboxes.
> Give it a try and I'm sure you'll be amazed.
>
> Does anybody know how to fix it ? I know a workaround to make
> it run OK but what I'm looking for is an real pure explanation.
>
> Thanks for your help and I apologize for my English which I
> know isn't that good.
>
> '*********************************
> To insert in a standard module
> '*********************************
>
> Dim TABCOMBO(1 To 50) As New cls
> Sub creation()
>
> Dim WS As Worksheet
>
> Set WS = ActiveWorkbook.ActiveSheet
>
> Set plageselect = WS.Range("A3:A9")
>
> I = 1
>
> For Each c In plageselect
>
> 'create each combobox
> With c
> Set dp =
> WS.OLEObjects.Add(ClassType:="Forms.Combobox.1", Left:=.Left,
> Top:=.Top, Width:=12, Height:=12)
> End With
> dp.Name = "HZFILTER" & I
> dp.Placement = 1
>
> 'feeds the comboboxes with values that are at the three cells
> right of 'comboboxes
> For A = 1 To 3
> dp.Object.AddItem c.Offset(0, A)
> Next
>
> I = I + 1
>
> Next
>
> On Error Resume Next
> For I = 1 To WS.OLEObjects.Count 'here we guess the OLEObjects
> on the 'spreadsheet are the comboboxes which have been created
> by the code '"Create"
> valtempcombo = "HZFILTER" & I
> Set TABCOMBO(I).FILTERH = WS.OLEObjects(valtempcombo).Object
> Next
>
> On Error GoTo 0
>
> End Sub
>
>
> '*********************************
> To insert in a class module named cls
> '*********************************
> Public WithEvents FILTERH As ComboBox
>
> Private Sub FILTERH_Click()
>
> MsgBox "coocoo"
>
> End Sub
>
> By the way, if you don't declare dim ws as worksheet, it never
> works.I hope me to have been specific.
>
> Regards

hi,

you were right. It works when variables declared

Thanks

Chip Pearson a écrit :
> Your code worked for me (after I declared the missing variable
> declarations! declare your variables!) in Excel 2003.
>
>