mardi 16 janvier 2018

Show or delete an image based on a checkbox value in VBA excel

I have a code that inserts images in range (D2:D10) based on the cell value in range (B2:B10). So if I enter a correct value in B2 an image will appear in D2, etc... However, I want to add a checkbox that controls the visibility of the image.

So I want excel only to show the image when the checkbox is true and the cell B2 has a correct value and delete the image when the checkbox is false even if B2 still has the correct value.

So I think the checkbox should be the action that starts the macro, but I don't know how to make this. Because now the macro runs when I enter a value. In my current code, I even don't have a checkbox...

I never made something like this. Is this possible to make? Could someone help me out? Thanks in advance.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPict As Picture
Dim PictureLoc As String
Dim rng As Range, cell As Range
Set rng = Range("B2:B10")
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    PictureLoc = "C:\Users\" & Target.Value & ".png"
    Set cell = Target.Offset(, 2)
    With cell
        On Error Resume Next
        Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
        myPict.Select
        With Selection
            .Height = 30
            .Width = 60
            .Top = cell.Top + cell.Height / 2 - .Width / 2
            .Left = cell.Left + cell.Width / 2 - .Width / 2
            Rows(Target.Row).RowHeight = .Height
        End With
        On Error GoTo 0
    End With
    Application.EnableEvents = True
End If
End Sub




Aucun commentaire:

Enregistrer un commentaire