Was möchte ich nun zum Ziel setzen? Oft wird in Foren nach einer Übersicht
von Tabellenblättern gefragt, die möglichst auch eine Auswahl für ein zu aktivierendes
Blatt bietet. Wir werden das mit einem Userform lösen.Klassen sind auf den ersten Blick eine eigene Form von Modulen. Beim Einfügen eines Klassenmoduls erhalte ich im Projektexplorer einen neue Gruppe, ähnlich wie beim Einfügen von Modulen oder Userforms. Für die Programmierung wird ein Modulblatt bereitgestellt. Ähnlich wie in den Tabellenblattmodulen oder in DieseArbeitsmappe ist die Auswahl oben bereits vorbelegt, links mit Class und rechts mit Initialize und Terminate. Initialize und Terminate sind dem einen oder anderen viellecht schon vom Userform bekannt und haben eine vergleichbare Funktion. Für unsere kleine Einführung benötigen wir beides jedoch nicht. Klassen kann man mit Objekten vergleichen. Sie besitzen ebenso Eigenschaften und Methoden und können auf Ereignisse reagieren.
Ich werfe hier mal ein paar Fachbegriffe in den Raum, die der unbedarfte Leser am Besten überspringt - sie werden für die (ganz) kurze Einführung nicht benötigt. Eigenschaften werden in Variablen der Klassenmodule hinterlegt, die über Properties zu belegen sind. Für Methoden und Aktionen innerhalb der Klassen nutzt man Sub's und Funktionen, und Ereignisse können von der Application oder dem jeweiligen Objekt über WithEvents abgegriffen werden, oder man programmiert in sogenannten RaiseEvents eigene. Aber das möchte ich, wie gesagt, in diesem Beispiel nicht vertiefen - ausgenommen die Ereignisse von Steuerelementen.
Was möchte ich nun zum Ziel setzen? Oft wird in Foren nach einer Übersicht
von Tabellenblättern gefragt, die möglichst auch eine Auswahl für ein zu aktivierendes
Blatt bietet. Wir werden das mit einem Userform lösen.
##################################################
**************************************************
Datei UserformButtons.xlsm (ab 2007)
Benötigt werden
1 kleines userform
ShowModal sollte auf False stehen, damit man später auch in den Blättern etwas tun
kann.
1 kleiner Rahmen
Die Höhe ist unerheblich, wir wissen ja nicht, wie viele Blätter auf uns zukommen.
Zwei Optionbuttons sollten drauf passen, breit genug, um die Tabellenblattnamen
auszugeben. Alles weitere lösen wir programmtechnisch.
optional
1 Button OK
1 Button Abbrechen
**************************************************
So, um die Auswahl der Blätter vorzunehmen, wären Radiobuttons bzw. Optionbuttons
von Vorteil. Bei diesen schaltet sich bei Aktivierung eines Buttons der vorher aktive
deaktiv. Einfügen tun wir die in einer Schleife. Wir dimensionieren dazu am Anfang
des Makros noch eine entsprechende Objektvariable.
codeauszug:
'Controls/OptionButton Dim ctrOBtn As MSForms.OptionButton 'Schleife ueber alle Tabellenblaetter For iCnt = 1 To Worksheets.Count 'Optionbutton hinzufuegen und zuweisen Set ctrOBtn = Me.Frame1.Controls.Add("Forms.OptionButton.1", "wsho" & iCnt, True) '************************************************** 'Was fehlt noch? Die Positionierung. 'mit dem Optionbutton With ctrOBtn 'Hoehe einstellen .Height = 14.75 'Breite einstellen .Width = 55 'Oben einstellen .Top = 10 + 22 * (iCnt - 1) 'links einstellen .Left = 5 'Beschreibung = Blattname .Caption = Worksheets(iCnt).Name 'waehlbar = Blatt sichtbar? .Enabled = Worksheets(s).Visible 'Wenn Blattnamme = Name des aktiven Blattes dann If CBool(Worksheets(iCnt).Name = ActiveSheet.Name) = True Then 'Button = true .Value = True 'Ende Wenn Blattnamme = Name des aktiven Blattes dann End If 'Ende mit dem Optionbutton End With Next
**************************************************
So, jetzt stehen die Buttons zwar alle schön untereinander, man kommt aber nicht
an alle ran. Was fehlt? Eine Scrollbar.
Da von vornherein nicht klar ist, ob der Platz im Rahmen für die Buttons reicht,
sollte bei entsprechender Anzahl Buttons automatisch ein Scrollbalken erscheinen.
Den entsprechenden code platzieren wir mit etwas Mathematik nach der Schleife.
codeauszug:
'Wenn Hoehe der Buttons groesser der Rahmenhoehe dann 'Hinweis: iCnt am Schleifenende 1 mehr als Optionbuttons, daher - 1 If 10 + 22 * (iCnt - 1) > Me.Frame1.Height Then 'Scrollbar setzen wenn mehr Datenspalten im Report 'Scrollbar aktivieren If Me.Frame1.ScrollBars = fmScrollBarsNone Then _ Me.Frame1.ScrollBars = fmScrollBarsVertical 'Groesse der Scrollbar einstellen If Me.Frame1.ScrollBars = fmScrollBarsVertical Then _ Me.Frame1.ScrollHeight = 10 + 23 * iCnt 'Ende Wenn Hoehe der Buttons groesser der Rahmenhoehe dann End If
Beim Test ist noch nichts weiter passiert? Beim Drücken auf die Buttons sollte auch noch nichts passieren ...
##################################################
**************************************************
Bevor wir zu den Klassen gehen, kommen noch ein paar kleine Änderungen ..
##################################################
**************************************************
So, jetzt geht es mit den Klassen los.
Datei UserformButtonsCls001.xlsm
Wir fügen ein neues Klassenmodul ein und benennen es
Public WithEvents Button As MSForms.OptionButton
Dann fügen wir eine Aktion ein - hier das Change-Ereignis. Das geht genau so wie
man es vielleicht von einem "normalen" OptionButton_Change - Ereignis kennt.
codeauszug:
Private Sub Button_Change() ' MsgBox "2 - Change" & vbLf & Button.Value & vbTab & Button.Caption Sheets(Button.Caption).Activate End Sub
**************************************************
Im Userform Modul ist etwas mehr einzufügen. Hier ganz kurz in einem Satz -
Erklärungen im folgenden Text.
Der Button wird der Klasse zugewiesen, das Ereignis wird definiert, und in
einer Collection mit den anderen Buttons "gesammelt".
Was hat es mit der Collection auf sich?
Wir benötigen eine globale Variable bzw. eine Collection, in der wir die Buttons makroübergreifend erfassen. Eine Collection hat gegenüber einem Array u.a. den Vorteil, dass keine Dimensionierung benötigt wird und das Objekte - für unser Beispiel die Optionbuttons - hinterlegt werden können. Der Name ist hier wieder frei wählbar. Excel erkennt, dass die Buttons "irgendwo" vorliegen.
Dim colBtn As Collection 'Button-Collection
Im Userform_Initialize müssen wir noch die Klasse dimensionieren. Dazu ist
der Name des Klassenmodus zu verwenden
codeauszug:
'Klasse Dim ButtonEvent As clsButtonEvents
**************************************************
Anschließend müssen wir die 3 Dinge auch zuweisen. Die Collection vor der Schleife, ...
codeauszug:
'Collection zuweisen Set colBtn = New Collection 'Button der Klasse zuweisen Set ButtonEvent = New clsButtonEvents 'Change-Event zuweisen Set ButtonEvent.Button = ctrOBtn
**************************************************
... und letztendlich den Button der Collection hinzufügen.
codeauszug:
colBtn.Add ButtonEvent
So, jetzt sollten die Blätter auch gewechselt werden.
##################################################
**************************************************
Übrigens kann man den Buttons auch unterschiedliche Aktionen beim gleichen
Ereigniss zuweisen.
Datei UserformButtonsCls002.xlsm
zusätzlicher Code im Klassenmodul
codeauszug:
Private Sub btnSpecialEve_Change() MsgBox "1 - Special Change" & vbLf & Button.Value & vbTab & Button.Caption End Sub
Das zusätzliche Ereignis ist natürlich auch im code des Userform dem Button
zusätzlich zuzuweisen.
codeauszug:
'Hinweis: 'Reihenfolge der Ausfuehrung Change - SpecialChange 'entsprechend der Reihenfolge der Zuweisung hier 'Special-Event zuweisen Set ButtonEvent.btnSpecialEve = ctrOBtn
Man könnte auf diese Weise z.B. verschiedene Ereignissse an verschiedene Buttons zuweisen.
##################################################
**************************************************
Im Beispiel ist ein ausgeblendetes Blatt dabei. Jetzt wäre noch eine Möglichkeit
zum Einblenden erwünscht.
Datei UserformButtonsCls003.xlsm
Statt Optionbuttons sind hier Checkboxen angebracht. Diese legen wir nach dem
gleichen Prinzip wie die Buttons an. Dazu kopieren wir einfach die codepassagen
der Buttons, legen ein entsprechendes Klassenmodul an, ändern Buttons in Boxen ..
Übrigens ist der jeweilige Tabellenblattname in der Eigenschaft "Tag" der CheckBoxen
versteckt. Zum einen steht der Name schon in der Beschriftung der OptionButtons,
zum anderen ist es für den Anwender auch nicht verkehrt, wenn er lesen kann, was
die Checkbox bewirkt ...
Datei UserformButtonsCls004.xlsm
... und erweitern dann den code im Klassenmodul für das Ein- und Ausbenden der Blätter
anhand dem Wahrheitswert der Checkbuttons.
Na, das war doch gar nicht so schwer. Die Commandbuttons haben wir eigentlich nicht
gebraucht - die könnten entfallen. Es ist hier einfacher, die Aktionen unmittelbar
bei Klick auf die Optionbuttons und Checkboxen auszuführen, als extra noch OK
abzuwarten. Abbrechen kann man auch über das X vom Userform ....
Hinweis:
Die OptionButtons sind hier alle wählbar gesetz. Man könnte in Abhängigkeit der
Sichtbarkeit der Blätter und der Aktion der CheckBoxen die Wählbarkeit steuern.
##################################################
Weiteres Beispiel:
Datei Logfile.xls
Eine andere Frage betrifft die Auslösung eines Ereignisses bei jedem Öffnen von beliebigen
Exceldateien. Mit der Beispieldatei kann man ein Ereignis beim Öffnen jeglicher Excel-Mappen
auslösen. Ich habe es zur Protokollierung von Dateizugriffen genutzt. Es wird ein Logdatei
erzeugt. Wo, kann man auf dem Tabellenbatt angeben. Das sollte man aber nicht zur Überwachung von
Mitarbeiteraktivitäten einsetzen - es wäre nicht die erste Affäre mit folgender negativer
Publicity dieser Art.
Online-Literatur:
deutsch
englisch