Office: Event-Handler für Command Button in Tabellenblatt

Stefan5 · 28. November 2018

Liebe community, bestimmt kann mir jemand bei folgendem Problem weiterhelfen:

Ich möchte dynamisch einen ActiveX-Button einem Tabellenblatt zufügen.
Dazu habe ich versucht den Code zum Einfügen von Buttons in UserForms (dort bekomme ich das hin - hierzu gibt es auch viele Beispiele im Netz) entsprechend anzupassen:

Ich habe folgendes Klassenmodul "clsCommandButton" erstellt:

Code:

Public WithEvents cmdCommandButton As CommandButton

Private Sub cmdCommandButton_Click()
MsgBox "Button gedrückt."
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Das Testmodul zum Einfügen des Buttons sieht folgendermaßen aus:

Code:

Public Sub SetUpButton()
Dim mCommandButton As clsCommandButton

Set mCommandButton = New clsCommandButton

Set mCommandButton.cmdCommandButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1", Link:= _
False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=100, Height:=50)
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Problem: Es wird ein (wirkungsloser) Button eingefügt und es erscheint die Fehlermeldung "type mismatch".

Was muss ich ändern?

:)

Muller2 · 30. November 2018

Hallo,

ergänz mal:
Code:

Set mCommandButton.cmdCommandButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1", Link:= _
False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=100, Height:=50).Object
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

ah und ich seh grad Deine Klassenvariable mußt Du am besten statisch und global setzen, damit das Objekt der virtuellen Klasse erhalten bleibt, bei mehreren Objekten nutzt man dann ein Array, oder eine Collection...

Code:

Option Explicit

Private mCommandButton As clsCommandButton

Public Sub SetUpButton()

Set mCommandButton = New clsCommandButton

Set mCommandButton.cmdCommandButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1", Link:= _
False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=100, Height:=50).Object
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Gruß, Muller2

R J · 30. November 2018

...das geht auch einfacher:
Beispiel:
Code:

Sub ErstelleButton()

Dim NewButton As Object

Set NewButton = Tabelle1.Buttons.Add(255, 1, 180, 20)
NewButton.Caption = "Ich bin der neue Button"
NewButton.Font.Bold = True
NewButton.OnAction = "MachWas"

End Sub

Sub MachWas()
MsgBox "Geklickt!!"
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Wobei Muller2's Einwand bezgl. mehrerer Objekte bestehen bleibt.

PS: Wobei natürlich die Frage steht, weshalb man einen Button via VBA erstellen muss?
Auch halte ich es für sinnvoller, statt unzähliger Buttons immer den gleichen verwendet und ihn in Abhängigkeit zur verwendeten Zelle setzt...

Bsp.:
Einen Button auf dem Tabellenblatt platzieren und folgenden Code in das Modul der Tabelle:
Code:

Dim z As Long
Private Sub CommandButton1_Click()
MsgBox " Zeile: " & z
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iSec As Range
CommandButton1.Visible = False
Set iSec = Intersect(Target, UsedRange)
If Not iSec Is Nothing Then
z = Target.Row
CommandButton1.Visible = True
CommandButton1.Top = Target.Top
Set iSec = Nothing
End If
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Stefan5 · 30. November 2018

Hallo Muller2, hallo RJ,

tausend Dank für eure Tipps!*biggrin.gif*

Dank der Ergänzung ".Object" klappt das jetzt! *Smilie
Allerdings wundere ich mich, wieso das Sub

Code:

Private Sub cmdCommandButton_Click()
MsgBox "Button gedrückt."
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

das im Klassenmodul steht, nicht ausgeführt wird. Ich muss stattdessen den Code der Tabelle zuweisen:

Code:

Private Sub CommandButton1_Click()
msgbox "Button gedrückt."
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Wenn ich mit dem "controls"-Objekt und userforms arbeite, klappt es hingegen, wenn das aufzurufende sub im Klassenmodul steht.

@RJ:
Leider kommen Shapes, die mit der onaction-Eigenschaft subs aufrufen, für mich nicht in Frage, da sie sich sehr eigenartig verhalten, wenn sie bestimmte "animierte" Aktionen starten sollen, ähnlich z.B. der folgenden Prozedur:

Code:

Public Sub Animationstest()
Dim t As Integer, Timer1 As Single

Application.ScreenUpdating = True

For t = 1 To 60
ActiveSheet.ChartObjects("Diagramm 1").Chart.ChartGroups(1).FirstSliceAngle = t
Timer1 = Timer + 0.1
While Timer() < Timer1
DoEvents
Wend
Next

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Insbesondere wenn das EXCEL-Tool per Fingertouch bedient wird, findet dann die Animation quasi "im Hintergrund" statt und man bekommt nach einiger Zeit nur das Endergebnis zu sehen.
ActiveX-Buttons kennen dieses Problem zwar zunächst nicht, aber leider "nützen sie sich ab" (ich weiß, das klingt sehr verrückt, ist aber leider so):
Sie erzeugen manchmal ungefragt einen "Zwillingsbutton", der versetzt auf dem ursprünglichen erscheint, meist eine deutlich andere Größe + Schriftgröße aufweist und manchmal funktionieren sie irgendwann auch gar nicht mehr. Dieses Problem tritt vor allem dann häufig auf, wenn das Gerät, auf dem das Excel-tool läuft, regelmäßig mit Beamern klarkommen muss, die unterschiedliche Auflösungen haben.
Um diesen Excel-bug zu umschiffen, weiß ich mir keinen anderen Rat, als den Button beim Aktivieren des betreffenden Tabellenblatts neu zu erschaffen und ihn beim Verlassen wieder zu löschen.

Muller2 · 1. Dezember 2018

Hallo Stefan,

zur Ausführung des Klassenevents cmdCommandButton_Click:

ja das ist ne böse Falle*upps , hatt auch nicht mehr auf dem Schirm, bei Hinzufügen von ActiveX-Controls zu TabBlättern ( auch per Code...!) wechselt Excel in den Entwurfsmodus, was zur Folge hat, daß alle virtuellen Klassen 'zerstört' und deren öffentlichen Klassenvariablen auf 'Nothing' gesetzt werden, man muß beide Vorgänge mit einem Timer entkoppeln:

' **********************************************************************
' Modul: Modul1 Typ: Standardmodul
' **********************************************************************

Option Explicit

Private Const BUTTON_NAME As String = "MyButton"

Private lobjCommandButtonClass As clsCommandButton

Public Sub SetUpButton()

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:= _
False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=100, Height:=50).Name = BUTTON_NAME

Call Application.OnTime(EarliestTime:=Now, Procedure:="prcTimer")

End Sub

Public Sub prcTimer()
Set lobjCommandButtonClass = New clsCommandButton
Set lobjCommandButtonClass.CommandButton = ActiveSheet.OLEObjects(BUTTON_NAME).Object
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

' **********************************************************************
' Modul: clsCommandButton Typ: Klassenmodul
' **********************************************************************
Option Explicit

Private WithEvents mcmdCommandButton As MSForms.CommandButton

Private Sub mcmdCommandButton_Click()
MsgBox "Button gedrückt."
End Sub

Friend Property Set CommandButton(ByRef prcmdCommandButton As MSForms.CommandButton)
Set mcmdCommandButton = prcmdCommandButton
End Property

Lukas Mosimann
Projektbetreuung durch mumpel

Code erstellt und getestet in Office 14
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Zum Zwillingsbutton-Problem: Mit einem Beamer kenn ich mich da nicht aus, aber das Problem tritt häufiger bei exotischeren ActiveX-Controls auf TabBlättern auf, da hilft meistens nur noch, die Controls in ein ActiveX-Frame zu packen, allerdings kommst Du dann an die Events der Controls wiederum nur über die AutomatisierungsKlasse(-nprogrammierung)...*cool.gif*

Gruß, Muller2

Flotter Feger · 1. Dezember 2018

Hallo,

ich habe leider keine Ahnung, was du da genau vor hast, aber ... schon mal daran gedacht, eventuell ein CommandBar zu verwenden ?
Vielleicht auch ein selbsterstelltes, neues Kontext-Menu ... wie beim Rechtsklick in die Zelle ?

Stefan5 · 2. Dezember 2018

Hallo Muller2,

danke für deinen Nachtrag!
Ich habe deinen Code in mein Tool übernommen. Auch wenn ich nicht wirklich verstehe, was du da trickst, es funktioniert!*cool.gif*

Gruß
Stefan

Office: Event-Handler für Command Button in Tabellenblatt

Event-Handler für Command Button in Tabellenblatt

Event-Handler für Command Button in Tabellenblatt

Event-Handler für Command Button in Tabellenblatt

Event-Handler für Command Button in Tabellenblatt - Similar Threads - Event Handler Command

Arbeiten Sie im Team an Handzetteln für Promotion-Events

Einladen von Teilnehmern zu einem Live-Event einer Teams

Tipps zur Barrierefreiheit für inklusive Microsoft Teams-Besprechungen und Live-Events

Stummschalten bei MS Live Events

Live Event in MS Teams

Live Event Option erscheint nicht

Live Event Teams: Speaker & Slides anzeigen