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

Question

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 to:

show the image when the checkbox is true and the cell B2 has a correct value
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?

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

Welcome to the site! Check out the [tour](https://stackoverflow.com/tour) and the [how-to-ask page](https://stackoverflow.com/help/how-to-ask) for more about asking questions that will attract quality answers. You can [edit your question](https://stackoverflow.com/posts/48287745/edit) to include more information. Have you done any searching to find out how to add the checkbox? — cxw, Jan 16 '18 at 18:43
You may find this helpful https://stackoverflow.com/questions/39430745/excel-vba-script-to-insert-multiple-checkboxes-linked-to-cell-with-yes-and-no-in/39434543#39434543 — Greg Viers, Jan 16 '18 at 18:48

score 0 · Answer 1 · edited Jan 17 '18 at 04:07

Here is a solution:

Place the checkboxes in column C
Add an empty column (D)
Link each checkbox to the corresponding cell in D. For example, link the one in C3 to cell D3. You can do this by right-clicking on the checkbox and choosing "format control". Then go to the control tab and set the cell link. Now when you check and uncheck the checkbox, this cell will change to TRUE or FALSE.
In cell E2, use the formula:

=and(D2,B2="correct")

with B2="correct" replaced with whatever way you are currently planning to check if B2 is correct.

Create the macro you have above, but don't use the action. Instead, make it an ordinary macro called LoadPic (ByVal Target as Range) and change your If statement to read:

If Not Intersect(Target, Rng) Is Nothing And Cells(Target.Row, 5) = True Then
Create the change macro to capture changes to the B column, but have it call your other macro:
```
Private Sub Worksheet_Change(ByVal Target As Range)

    Call loadPic(Target)

End Sub
```

Create another macro for when a checkbox is clicked:

Sub clickCheckBox()
Dim ClickedCheckBox As CheckBox
Dim source As String
source = Application.Caller

Set ClickedCheckBox = ActiveSheet.CheckBoxes(Application.Caller)
Call loadPic(Range(ClickedCheckBox.LinkedCell))
End Sub

right-click and assign this macro to each of your images.

That should work.

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

1 Answers1