Excel VBA – Wie Sie ein Bild als Kommentar hinzufügen
Dieser Beitrag ist ein Teil der VBA Grundlagen Serie.
Neben normalen Texten können Excel Kommentare auch Bilder enthalten. Diese werden, wie normale Kommentare, durch das Überfahren mit dem Mauszeiger angezeigt. Leider ist diese Funktionalität etwas versteckt untergebracht. In diesem Beitrag programmieren wir eine Abkürzung.
Zunächst möchte ich aber den Standardweg zeigen. Führen Sie einen Rechtsklick auf die Zelle mit dem Kommentar aus und wählen Sie Kommentar bearbeiten aus dem Kontextmenü.
Klicken Sie anschließend auf den Rahmen des Kommentarfeldes und wählen Sie Kommentar formatieren.
Ein neuer Dialog öffnet sich. Wählen Sie den Reiter Farben und Linien aus, klicken Sie auf das Dropdownfeld Ausfüllen / Farbe und anschließend auf Fülleffekte.
Im nächsten Dialog können Sie schließlich eine Grafik als Hintergrund auszuwählen.
Sie merken schon, dass es ziemlich aufwendig ist und viel Geklicke verursacht. Daher programmieren wir uns einen Shortcut. In unserem Makro wird eine ausgewählte Bilddatei als Hintergrund eines Kommentars eingefügt. Außerdem wird in dieselbe Zelle ein Hyperlink eingefügt, der auf die Datei verweist. Wenn die Datei auf dem Computer des Anwenders nicht vorhanden ist, funktioniert der Link zwar nicht. Aber das Bild ist dennoch zu sehen, da es in der Mappe intern gespeichert wird.
111 VBA Makros die Ihr Leben leichter machen
Wollen Sie Ihre langweiligen Routineaufgaben loswerden? Möchten Sie Excel an Ihre Bedürfnisse anpassen, Berechnungen automatisieren, eigene Dialogfelder erstellen und vieles mehr? Dieses Buch zeigt Ihnen, wie Sie Ihre Aufgaben schnell und einfach mit VBA erledigen.
Rufen Sie bitte mit der Tastenkombination ALT+F11
die Entwicklungsumgebung auf und fügen Sie ein neues Modul ein. Zunächst legen wir das Makro an, welches das Ziel des Kommentars festlegt und die Datei auswählt. Um die Datei auszuwählen nutzen wir die GetOpenFilename
Methode. Diese liefert den Namen und den Pfad des Bildes. Diesen Pfad übergeben wir zusammen mit der Zielzelle und der Bildhöhe an die Prozedur AddPictureAsComment
. Diese bringt das ausgewählte Bild in das Kommentarfeld. In diesem Beispiel wird der Pfad der Datei auch als Kommentartext hinzugefügt.
Anschließend legen wir einen Hyperlink in der Zelle mit dem Kommentar an. Dieser verweist auf das ausgewählte Bild. Über die DisplayCommentIndicator
Eigenschaft können Sie steuern ob die Kommentare immer eingeblendet oder ausgeblendet sind. Analog zu dem Icon Alle Kommentare anzeigen in der Gruppe Kommentare. Diesen finden Sie in dem Reiter Überprüfen.
Unser Makro sieht so aus:
Sub BildHinzufuegen() Dim strFilename As Variant Dim strFilter As String Dim rngDest As Range 'Ziel des Kommentars festlegen Set rngDest = Worksheets("Kommentar").Range("A1") 'Dateiauswahl filtern strFilter = "JPG Files (*.jpg), *.jpg" _ & ", GIF Files (*.gif), *.gif" _ & ", Bitmaps (*.bmp), *.bmp" _ & ", WMF Files (*.wmf), *.wmf" 'Dialog Dateiauswahl strFilename = Application.GetOpenFilename(strFilter) 'Brich ab, wenn nichts gewählt If strFilename = False Then Exit Sub 'Bild als Kommentar hinzufügen AddPictureAsComment rngDest, CStr(strFilename), _ CStr(strFilename), 300 'Link hinzufügen rngDest.Worksheet.Hyperlinks.Add rngDest, CStr(strFilename), _ , , Dir(strFilename) 'Kommentare immer eingeblendet Application.DisplayCommentIndicator = xlCommentAndIndicator 'Kommentare ausgeblendet 'Application.DisplayCommentIndicator = xlCommentIndicatorOnly End Sub
Nun legen wir das Makro an um das Bild in den Kommentar zu bringen. Zunächst laden wir mit der Methode LoadPicture
das Bild in den Speicher. Wenn es nicht existiert, brechen wir die Funktion ab. Danach löschen wir eventuell vorhandene Kommentare und fügen ein neues hinzu. Wenn eine Kommentarhöhe übergeben wurde ist der Parameter PicHeight
größer als 0. In diesem Fall wird das Verhältnis von gewünschter zu vorhandener Höhe berechnet und in der Variablen dblScale
gespeichert. Anschließend laden wir mit der Methode UserPicture
das Bild als Füllung des Kommentarfeldes. Der Kommentartext wird durch die als Parameter strComment
übergebene Stringvariable bestimmt.
Wenn wir das Bild mit LoadPicture
laden, liefert dieses die Höhe und Breite in der Himetric Einheit, welche 1/1000 cm entspricht. Wenn wir diesen Wert durch 1000 teilen, bekommen wir die Abmessungen in cm. Wenn wir durch 2540 teilen, erhalten wir die Angabe in Zoll. Allerdings benötigen wir für das Kommentarfeld die Angaben in Punkt. Ein Punkt ist 1/72 Zoll groß. Wenn wir also die Himetric-Angabe mal 72 nehmen und durch 2540 teilen, erhalten wir die Abmessungen in Punkt. Über den Skalierungsfaktor dblScale
können wir nun die gewünschte Breite und Höhe des Kommentarfeldes berechnen.
Public Sub AddPictureAsComment(Dest As Range, _ Source As String, Optional strComment As String, _ Optional PicHeight As Double = 0) Dim objPic As IPictureDisp Dim objComment As Comment Dim dblScale As Double On Error Resume Next 'Bild wegen Abfrage der Größe laden Set objPic = LoadPicture(Source) 'Brich ab, wenn kein Bild If objPic Is Nothing Then Exit Sub 'Kommentar in Zielzelle löschen Dest.ClearComments 'Kommentar in Zielzelle hinzufügen Set objComment = Dest.AddComment 'Skalierung berechnen If PicHeight > 0 Then dblScale = PicHeight / (objPic.Height * 72 / 2540) Else dblScale = 1 End If 'Kommentar mit Bildhintergrund und Text füllen With objComment .Shape.Fill.UserPicture Source .Shape.Height = (objPic.Height * 72 / 2540) * dblScale .Shape.Width = (objPic.Width * 72 / 2540) * dblScale .Text Text:=strComment End With End Sub
Wenn Sie das Makro nun starten, können Sie ein Bild auswählen welches dann als Kommentar hinzugefügt wird.
Als Nächstes fügen wir einen Button ein, um das Makro aus dem Arbeitsblatt heraus aufzurufen. Gehen Sie dazu in der Reiter Entwicklertools und fügen Sie einen Button als Active-X Steuerelement ein.
Zeichnen Sie den Button und klicken Sie anschließend auf Eigenschaften.
Geben Sie unter Caption Ihre Beschreibung ein. Zum Beispiel „Bild als Kommentar“.
Klicken Sie schließlich auf Code anzeigen
und geben Sie den Namen des aufzurufenden Makros ein. In unserem Fall sieht es so aus:
Private Sub CommandButton1_Click() BildHinzufuegen End Sub
Nun wollen wir unser Makro flexibler gestalten. Statt der Zelle A1
soll der Kommentar in die gerade ausgewählte Zelle eingefügt werden. Daher ändern wir den Code wie folgt ab:
'Ziel des Kommentars festlegen 'Set rngDest = Worksheets("Kommentar").Range("A1") Set rngDest = ActiveCell
Darüber hinaus wollen wir auch den Kommentartext selbst eingeben. Daher fügen wir eine zusätzliche Variable strText
ein. Diese wird über eine Eingabemaske befüllt.
'Kommentartext abfragen strText = InputBox("Bitte Kommentar eingeben", "Kommentar")
Anschließend übergeben wir den eingegebenen Kommentar an die Funktion.
'Funktion aufrufen AddPictureAsComment rngDest, CStr(strFilename), _ strText, 200
Das neue Makro sieht nun so aus:
Sub BildHinzufuegen() Dim strFilename As Variant Dim strFilter As String Dim strText As String Dim rngDest As Range 'Ziel des Kommentars festlegen Set rngDest = ActiveCell 'Dateiauswahl filtern strFilter = "JPG Files (*.jpg), *.jpg" _ & ", GIF Files (*.gif), *.gif" _ & ", Bitmaps (*.bmp), *.bmp" _ & ", WMF Files (*.wmf), *.wmf" 'Dialog Dateiauswahl strFilename = Application.GetOpenFilename(strFilter) 'Brich ab, wenn nichts gewählt If strFilename = False Then Exit Sub 'Kommentartext abfragen strText = InputBox("Bitte Kommentar eingeben", "Kommentar") 'Funktion aufrufen AddPictureAsComment rngDest, CStr(strFilename), _ strText, 200 'Link hinzufügen rngDest.Worksheet.Hyperlinks.Add rngDest, CStr(strFilename), _ , , Dir(strFilename) 'Kommentare immer eingeblendet Application.DisplayCommentIndicator = xlCommentAndIndicator 'Kommentare ausgeblendet 'Application.DisplayCommentIndicator = xlCommentIndicatorOnly End Sub
Haben Sie Fragen oder Anregungen? Hinterlassen Sie einen Kommentar.
Die Beispiel Excel Datei mit dem sofort einsetzbaren Makro und 111+ andere Makros, die Ihr Leben leichter machen, finden Sie hier.
Beeindrucke deinen Chef mit coolen Excel Tricks!
- Lasse dich von deinen Kollegen nicht abhängen!
- Spare Zeit und nutze diese für angenehmere Dinge
- Steigere deine Produktivität und Effizienz
- Es ist leicht - In meinem Newsletter gebe ich Schritt für Schritt Anleitungen die jeder versteht
- Bei der Anmeldung zu meinem Newsletter erhälst du das Buch „Excel Tricks die deinen Chef beeindrucken“ als Willkommensgeschenk.
Weiterführende Links:
DisplayCommentIndicator Eigenschaft
LoadPicture Methode
UserPicture Methode
ActiveCell Eigenschaft
Falls Ihnen dieser Beitrag weitergeholfen hat, wäre es eine sehr nette Anerkennung meiner Arbeit wenn Sie z.B. Ihre Bücher über Amazon bestellen würden. Wenn Sie ein Produkt kaufen, erhalte ich dafür eine Provision. Für Sie ändert sich am Preis des Produktes gar nichts. Ich möchte mich an dieser Stelle jetzt schon für Ihre Unterstützung bedanken.
Bildquelle: Pixabay, CC0 Public Domain
Hey,
wie kann ich bei jedem Kommentar separat das Bild aussuchen? Ich möchte also ein Makro, welches das Fenster öffnet bei der Grafik. Also genau das Fenster was aufgeht wenn man „Grafik auswählen“ klickt
Sollte bei der Auswahl funktionieren
Moin,
das echt eine Top-Anleitung!
Nur leider funktioniert die Zeile mit der Funktion nicht. –> Sub oder Funktion nicht definiert.
Was mache ich falsch? bzw. muss ich noch etwas ergänzen?
Moin Level1, du musst beide Makros definieren.
Könntest du das etwas genauer ausführen bitte? Stehe etwas auf dem Schlauch. LG
Hallo Arouf, wir haben folgende Makros:
CommandButton1_Click
BildHinzufuegen
AddPictureAsComment
CommandButton1_Click startet das Makro BildHinzufuegen. Das Makro BildHinzufuegen wiederrum nutzt AddPictureAsComment.
Ich bin Excel Anfängerin, deswegen ist für mich jeder Tipp wertvoll. Vielen dank.
Lg Imelda
Hallo Denis,
Ich habe deinen Code noch um die Bild-Datei .*png erweitert.
Allerdings fügt das Makro eine gewählte PNG nicht ein.
Manuell geht es aber mit der gleichen PNG.
Wo steckt denn da der Fehler?
Gruß Oli