May 12 2021 05:15 AM
May 12 2021 05:19 AM
May 12 2021 11:46 AM
Sub picture_insert ()
Dim picBild As Picture
Dim blnAvailable As Boolean
With worksheets ("sheet2")
.Unprotect Password: = "1234"
For Each picBild In .Pictures
If picBild.Name = "Picture Name" Then
'The picture already exists
blnVorhanden = True
Exit For
End If
Next picPicture
'only execute if picture does not yet exist
If blnVorhanden = False Then
With .Pictures.Insert ("" C: \ Temp \ Logo.jpg ")
.Name = "picture name"
.ShapeRange.LockAspectRatio = msoFalse
.Width = 100
.Height = 100
.Left = Worksheets ("Sheet2"). Range ("A10"). Left
.Top = Worksheets ("Sheet2"). Range ("G42"). Top
End With
End If
.Protect Password: = "1234", DrawingObjects: = True, Contents: = True, Scenarios: = True _
, AllowFormattingCells: = True
End With
End Sub
Sub Image_Remove ()
Dim picBild As Picture
With Worksheets ("Sheet2")
.Unprotect Password: = "1234"
For Each picBild In .Pictures
If picBild.Name = "Picture Name" Then
picBild.Delete
Exit For
End If
Next picPicture
.Protect Password: = "1234", DrawingObjects: = True, Contents: = True, Scenarios: = True _
, AllowFormattingCells: = True
End With
End Sub
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.
May 12 2021 12:12 PM
That was extremely helpful and I've almost got it finished but I get a error in the image removal section. Where it goes Next picPicture it gets a invalid Next control variable reference, I'm not too sure why @NikolinoDE
May 12 2021 12:18 PM
This is the original vba text in the link i send you already.
Try it, if makes the same error we see what we can do.
In the same time you can search all the Link about a solution.
Sub Bild_Einfuegen()
Dim picBild As Picture
Dim blnVorhanden As Boolean
With Worksheets("Tabelle2")
.Unprotect Password:="pw"
For Each picBild In .Pictures
If picBild.Name = "Bildname" Then
' Bild ist schon vorhanden
blnVorhanden = True
Exit For
End If
Next picBild
' nur ausführen wenn Bild noch nicht vorhanden
If blnVorhanden = False Then
With .Pictures.Insert(""C:\Excel\Logo.jpg")
.Name = "Bildname"
.ShapeRange.LockAspectRatio = msoFalse
.Width = 100
.Height = 100
.Left = Worksheets("Tabelle2").Range("J42").Left
.Top = Worksheets("Tabelle2").Range("J42").Top
End With
End If
.Protect Password:="pw", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End With
End Sub
Sub Bild_Entfernen()
Dim picBild As Picture
With Worksheets("Tabelle2")
.Unprotect Password:="pw"
For Each picBild In .Pictures
If picBild.Name = "Bildname" Then
picBild.Delete
Exit For
End If
Next picBild
.Protect Password:="pw", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End With
End Sub
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
May 16 2021 02:04 AM
Hello,
that's how it should basically work.
Option Explicit
Sub Image_Insert()
Dim picBild As Picture
Dim blnVorhanden As Boolean
With Worksheets("Sheet2")
.Unprotect Password:="1234"
For Each picBild In .Pictures
If picBild.Name = "Picture Name" Then
'The picture already exists
blnVorhanden = True
Exit For
End If
Next picBild
'only execute if picture does not yet exist
If blnVorhanden = False Then
With .Pictures.Insert("C:\Temp\Logo.jpg")
.Name = "Picture Name"
.ShapeRange.LockAspectRatio = msoFalse
.Width = 100
.Height = 100
.Left = Worksheets("Sheet2").Range("A10").Left
.Top = Worksheets("Sheet2").Range("G42").Top
End With
End If
.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End With
End Sub
Sub Image_Remove()
Dim picBild As Picture
With Worksheets("Sheet2")
.Unprotect Password:="1234"
For Each picBild In .Pictures
If picBild.Name = "Picture Name" Then
picBild.Delete
Exit For
End If
Next picBild
.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End With
End Sub
If you were to use "Option Explicit", you would have noticed the errors straight away, just like with me :). You work with different variable names "Dim picBild As Picture" and "Next picPicture". Also with "Sub Image_Remove ()". And "Dim blnAvailable As Boolean" - then "blnVorhanden = True".
Hope to have helped you with this.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
If you find this helpful, please mark it as "Best Answer", it will be beneficial to more Community members reading here.