选择并编辑工作表中的所有按钮 [英] 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屋!