Excel VBA-根据对相邻单元格的更新来更改按钮的可见性 [英] Excel VBA - Change button visibility based on update to adjacent cell

问题描述

我在"AK"列中有数据，在"AL"列中有一个按钮；有几百行，所有按钮只有一个宏，因为它基于所在行使用相对引用.

I have data in column "AK" and a button in Column "AL"; there are several hundred rows and there is only one macro for all buttons as it uses relative references based on the row it is in.

我希望按钮仅在相邻单元格中有数据时才可见.以下伪代码说明了我要实现的目标:

I want the button to only be visible when there is data in the adjacent cell. The following pseudo-code explains what I am trying to achieve:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 37 Then
        If Target.Value = 0 Then
            Shapes(Target.offset(0, 1)).Visible = False
        Else
            Shapes(Target.offset(0, 1)).Visible = True
        End If
    End If
End Sub

之所以这样做，是因为AK中的值是根据其他值计算的，并且仅在所有必填字段完成后才显示.一旦完成所有详细信息，该按钮应仅可用于自动化任务.什么真正的代码可以使这项工作有效而不必单独调用每个按钮?

The reason for doing this is that the value in AK is calculated based on other values and only displays once all mandatory fields have been completed. The button should only be available for an automation task once all details are complete. What real code would make this work without having to call each button out individually?

推荐答案

我不确定您是否可以通过在图纸上的位置直接引用它.
该代码将查看每个形状，直到找到您刚刚更改的单元格右侧的形状，然后将根据单元格的内容更改可见性.
(Target.Value<>")返回TRUE/FALSE.
仅当您的按钮放置在正确的单元格中时(此位置稍高，它将返回上方的单元格)，此功能才起作用.

I'm not sure if you can directly reference a shape by its location on the sheet.
This code will look at each shape until it finds the one to the right of the cell you've just changed, it will then change the visibility based on the contents of the cell.
(Target.Value <> "") returns TRUE/FALSE.
This will only work if your buttons are placed in the correct cell (slightly too high and it will return the cell above).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim shp As Shape

    For Each shp In ThisWorkbook.Worksheets("Sheet1").Shapes
        If shp.TopLeftCell.Address = Target.Offset(, 1).Address Then
            shp.Visible = (Target.Value <> "")
            Exit For 'Exit the loop - the correct button has been found.
        End If
    Next shp

End Sub

修改:
我已经更新了代码，因此它检查仅单个单元格已更改，然后查看已更改的单元格的每个相关单元格.
如果从属单元格在另一张纸上，则可能会变脏.

I've updated the code so it checks that only a single cell has been changed and then looks at each dependent cell of the cell that was changed.
This will probably muck up if the dependent cell is on another sheet though.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rUpdated As Range
    Dim shp As Shape
    Dim rCell As Range

    If Target.Cells.Count = 1 Then
        'Hopefully someone will have better code than On Error....
        On Error Resume Next
        Set rUpdated = Range(Target.Dependents.Address)
        On Error GoTo 0
        If Not rUpdated Is Nothing Then
            'Look at each dependent cell in rUpdated.
            For Each rCell In rUpdated
                'Look at each shape in the sheet and cross-reference with rCell.
                For Each shp In Target.Parent.Shapes
                    If shp.TopLeftCell.Address = rCell.Offset(, 1).Address Then
                        shp.Visible = (Target.Value = 0)
                        Exit For 'Exit the loop - the correct button has been found.
                    End If
                Next shp
            Next rCell
        End If
    End If

End Sub

注意:我从这里检查相关单元格的想法:

NB: I got the idea for checking the dependent cell from here: How can I run a VBA code each time a cell get is value changed by a formula?

这篇关于Excel VBA-根据对相邻单元格的更新来更改按钮的可见性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！