Excell VBA - Class Modules problems

[shao]

In module level I create Command Buttons(OLEObjects)
After that I need to get these buttons names when click them
In other words i need to have any buttons that call the same subroutin
but using variables that depend from the name of clicked button
I have next Module & Class Module & they working only if I runs ComNam
subroutine exact from itself (If i calling from CheckPivotItemLis
subroutine it does'nt work & I have not any warnings or errors!)
Plese Help to resolve this moment ! If anybody can suppose other wa
please I'll be glad .The focus is that the buttons should be create
after certain event (becouse their position depend on list height!)

Module "Module 2" :

Option Explicit
'________________________________________________
Public Sub CheckPivotItemList()

Dim i, ym As Integer
Dim TT, pr, T1 As String
Dim MMM As Variant
MMM = Array("E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O"
"P")
i = 50
............

For ym = 0 To 11
TT = MMM(ym)
............

T1 = Worksheets("Sheet2").Range(TT & "4").Value
Range(TT & Format(i + 5) & ":" & TT & Format(i
10)).Interior.ColorIndex = 15
Worksheets("Sheet2").OLEObjects.Add(ClassType:="Forms.CommandButton.1"
Link:=False _
, DisplayAsIcon:=False, Left:=Range(TT & Format(i + 6)).Left
_
Top:=Range(TT & Format(i + 6)).Top, Width:=Range(TT & Format(
+ 6)).Width, Height _
:=Range(TT & Format(i + 6)).Height + Range(TT & Format(i
7)).Height).Name = "ComBut" & Format(ym + 1)

Worksheets("Sheet2").OLEObjects("ComBut" & Format(ym
1)).Object.Caption = T1
Worksheets("Sheet2").OLEObjects("ComBut" & Format(ym + 1)).Activate
Worksheets("Sheet2").Range("A3").Select
Next ym
Call ComName
End Sub
'________________________________________________

Sub ComName()
Dim Buttoncount As Integer
Dim ctl As Object
Buttoncount = 0
Worksheets("Sheet2").Range("A3").Select
For Each ctl In Worksheets("sheet2").OLEObjects
If TypeName(ctl.Object) = "CommandButton" Then
Buttoncount = Buttoncount + 1
ReDim Preserve Buttons(1 To Buttoncount)
Set Buttons(Buttoncount).ButtonGroup = ctl.Object
End If
Next ctl
End Sub


Class Module "Class1":

Public WithEvents ButtonGroup As CommandButton
'________________________________________________

Public Sub ButtonGroup_Click()
MsgBox "Button with " & ButtonGroup.Name & " Name"
End Su

 

[Dick Kusleika]

shao

Excel has a real problem recognizing OLEObjects when they are added
programmatically. To get your code to work, replace

Call ComName

with

Application.OnTime Now + TimeSerial(0, 0, 0), "ComName"

Here's some minor changes to the procedure, in case you're interested

Sub CheckPivotItemList()

Dim i As Long, ym As Integer
Dim T1 As String
Dim oleComm As OLEObject

Const lColOff As Long = 5
i = 50
'...........

For ym = 0 To 11
'...........
With Worksheets("Sheet2")
T1 = .Cells(4, ym + lColOff).Value
.Cells(i + 5, ym + lColOff).Resize(5).Interior.ColorIndex = 15
Set oleComm = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Cells(i + 6, ym + lColOff).Left, _
Top:=.Cells(i + 6, ym + lColOff).Top, _
Width:=.Cells(i + 6, ym + lColOff).Width, _
Height:=.Cells(i + 6, ym + lColOff).Height + _
.Cells(i + 7, ym + lColOff).Height)
With oleComm
.Name = "ComBut" & Format(ym + 1)
.Object.Caption = T1
.Activate
End With
End With
Next ym

Application.OnTime Now + TimeSerial(0, 0, 0), "ComName"

End Sub

 

[shao]

*Thank you very much for your help,Dick!*

All working normally!

PS:I never think it so difficul