(Excel VBA) 如果单元格值等于 "" 然后显示/隐藏图像

(Excel VBA) If Cell Value equals "" Then Show/Hide Images

提问by Russell Saari

I am working on a Excel Spreadsheet that when a dropdown box value is selected an image will pop up, and if another value is selected it will hide the current image and pop up the image related to the selection. I have found a few methods that are just too time consuming using just the sheet and positioning of the image using coordinates; that's not exactly the route I would like to go.I have done a quite a bit of research before using StackOverflow, and nothing seemed to work thus far. Below is what I am trying to achieve. I am trying to keep all the images within the spreadsheet which adds another level of challenge, but I believe there is a way to do this because excel assigns the image a number when inserted EX. Picture 9.

I am working on a Excel Spreadsheet that when a dropdown box value is selected an image will pop up, and if another value is selected it will hide the current image and pop up the image related to the selection. 我发现了一些方法，这些方法仅使用工作表和使用坐标定位图像太耗时；这不是我想要的路线。在使用 StackOverflow 之前，我已经做了很多研究，但到目前为止似乎没有任何效果。以下是我正在努力实现的目标。我试图将所有图像保留在电子表格中，这增加了另一个级别的挑战，但我相信有一种方法可以做到这一点，因为 excel 在插入EX时为图像分配一个数字。图9。

Sub Main()
   If Range(G11).Value = "anything" Then

   Picture1 show

   Picture2 hide

   End If
End Sub

Any Help is greatly appreciated. Thanks

任何帮助是极大的赞赏。谢谢

采纳答案by Siddharth Rout

Rather than hiding/moving/reducing the size of the unwanted pic, why not simply delete it?

与其隐藏/移动/减小不需要的图片的大小，为什么不简单地删除它呢？

Logic: Save all your images in a temp sheet. When ever a relevant picture is supposed to be shown, get it from the temp sheet and delete the previous.

逻辑：将所有图像保存在临时表中。当应该显示相关图片时，从临时表中获取并删除之前的图片。

Here is an example.

这是一个例子。

Sub Sample()
    Select Case Range("G11").Value
        Case "Picture 1": ShowPicture ("Picture 1")
        Case "Picture 2": ShowPicture ("Picture 2")
        Case "Picture 3": ShowPicture ("Picture 3")
        Case "Picture 4": ShowPicture ("Picture 4")
    End Select
End Sub

Sub ShowPicture(picname As String)
    '~~> The reason why I am using OERN is because it is much simpler
    '~~> than looping all shapes and then deleting them. There could be
    '~~> charts, command buttons and other shapes. I will have to write
    '~~> extra validation code so that those shapes are not deleted.
    On Error Resume Next
    Sheets("Sheet1").Shapes("Picture 1").Delete
    Sheets("Sheet1").Shapes("Picture 2").Delete
    Sheets("Sheet1").Shapes("Picture 3").Delete
    Sheets("Sheet1").Shapes("Picture 4").Delete
    On Error GoTo 0

    Sheets("Temp").Shapes(picname).Copy

    '<~~ Alternative to the below line. You may re-position the image 
    '<~~ after you paste as per your requirement
    Sheets("Sheet1").Range("G15").Select 

    Sheets("Sheet1").Paste
End Sub

Snapshot of temp sheet

临时表快照

回答by Rhys

Here is a solution using the Visible property of the object. I used this to show a picture based on a value in a field. The field had a formula that resulted in either "good" or "bad". If its value was "good", I wanted to show one picture; for "bad", another picture should show; and they should never show at the same time. The field needed to update its value whenever a user refreshed a pivot table, so I put the code in that method of the worksheet where the pivot table and picture were to appear.

这是使用对象的 Visible 属性的解决方案。我用它来显示基于字段中的值的图片。该领域有一个公式，导致“好”或“坏”。如果它的值是“好”，我想展示一张图片；对于“坏”，应该显示另一张图片；他们不应该同时出现。每当用户刷新数据透视表时，该字段都需要更新其值，因此我将代码放在工作表的该方法中，数据透视表和图片将出现在该方法中。

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'hide both pictures by loopng through all the shapes on the sheet
Dim s As Shape
For Each s In ActiveSheet.Shapes
'hide the shape if it is a picture, leave other shapes on the page visible.
If s.Type = msoPicture Then s.Visible = msoFalse
Next

Dim judgement As String
'The field whose value tells what picture to use is a one-cell named range called "judgement"
judgement = Range("judgement")

'you need to know which picture is which.
If judgement = "Good" Then ActiveSheet.Shapes("Picture 8").Visible = True
If judgement = "Bad" Then ActiveSheet.Shapes("Picture 1").Visible = True

End Sub

回答by jon

Sub hidePicture(myImage)
    ActiveSheet.Shapes.Range(Array(myImage)).Select
    Selection.ShapeRange.Height = 0
    Selection.ShapeRange.Width = 0
End Sub

Sub showPicture(myImage)
    ActiveSheet.Shapes.Range(Array(myImage)).Select
    Selection.ShapeRange.Height = 200
    Selection.ShapeRange.Width = 300
End Sub

Handy tip: record macro and look at the code it generates!

实用提示：记录宏并查看它生成的代码！

回答by Tim Williams

Might be better just to move your pictures "off screen", particularly if they're of different sizes.

将图片移到“屏幕外”可能会更好，特别是如果它们的大小不同。

Sub Tester()
    ShowPicture "Picture 3"
End Sub

Sub ShowPicture(PicName As String)

    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        With s
        .Top = IIf(.Name = PicName, 100, 100)
        .Left = IIf(.Name = PicName, 100, 1000)
        End With
    Next s

End Sub