选择并编辑工作表中的所有按钮 [英] Select and Edit all buttons in sheet

问题描述

下面的例程允许用户切换他们已完成/未完成所需条目的位置.按钮文本将更改为完成/未完成"，并且使用简单的条件格式(在0或1值上)，相邻的单元格将变为绿色/红色.可以很好地用于更新单行.

The routine below allows the user to toggle where they have completed/not completed the required entry. The button text changes to Complete/Incomplete and the adjacent cell goes green/red using simple conditional formatting on the 0 or 1 value. Works fine for updating a single line.

每个用户的数据输入行数将有所不同(例如10到100)，我正在尝试找到一种选择方式，然后将工作表中的所有按钮更改为完成"，并将相邻单元格更新为0或一口气一口气，如果用户想这样做的话.

The number of data entry rows will vary for each user (say 10 to 100) and I am trying to find a way of selecting and then changing all the buttons in the sheet to "Complete" and updating the adjacent cell to 0 or 1 in one go, should the user want to do that.

每行是一条数据输入行，B列中的每个单元格都有一个按钮，C列中的相邻单元格中有一个0/1.

Each row is a data entry line and each cell in Column B has a button, and a 0/1 in adjacent cell in Column C.

Sub complete()
'Complete / Incomplete Buttton and Flag

Dim buttontext As String

buttontext = Application.Caller
ActiveSheet.Buttons(Application.Caller).TopLeftCell.Select
ActiveCell.Select

If ActiveSheet.Buttons(buttontext).Caption = "Mark as Incomplete" Then
    ActiveSheet.Buttons(buttontext).Caption = "Mark as Complete"
    ActiveCell.Offset(0, 1).Value = 1

    Else
    ActiveSheet.Buttons(buttontext).Caption = "Mark as Incomplete"
    ActiveCell.Offset(0, 1).Value = 0

End If

End Sub

以下代码有效:

Sub MarkAllComplete()
Dim btn As Button
For Each btn In ActiveSheet.Buttons
btn.Caption = "Mark as Complete"
Cells(btn.TopLeftCell.Row, btn.TopLeftCell.Column + 1) = 0
Next
End Sub

推荐答案

使用此概念:

For Each btn In ActiveSheet.Buttons
    Debug.Print btn.Name, btn.TopLeftCell.Column, btn.TopLeftCell.Row
Next

这篇关于选择并编辑工作表中的所有按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！