Excell VBA - Class Modules problems
 

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:="Fo rms.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

--
Message posted from http://www.ExcelForum.com

Excell VBA - Class Modules problems
 

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"shao " wrote in message
...
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 subroutine
but using variables that depend from the name of clicked button
I have next Module & Class Module & they working only if I runs ComName
subroutine exact from itself (If i calling from CheckPivotItemList
subroutine it does'nt work & I have not any warnings or errors!)
Plese Help to resolve this moment ! If anybody can suppose other way
please I'll be glad .The focus is that the buttons should be created
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:="Fo rms.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=Range(TT & Format(i + 6)).Left,
_
Top:=Range(TT & Format(i + 6)).Top, Width:=Range(TT & Format(i
+ 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 Sub


---
Message posted from http://www.ExcelForum.com/

Excell VBA - Class Modules problems
 

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

All working normally!:)

PS:I never think it so difficul

--
Message posted from http://www.ExcelForum.com