英文:
Macro in excel to implement a "+" button for N items. how to create only one macro but increment all items individually?
问题
我在Excel中创建了一个宏,并将其与一个按钮关联起来。基本上,这个宏实现了一个“+”按钮,顾名思义,它会将一个计数器变量递增存储在一个单元格中(比如说单元格是B1)。例如,如果我点击两次这个按钮,B1中就会存储值为2,以此类推。
我有一个更长的项目列表,我需要为每个项目都有这个“+”按钮(N个项目-> N个按钮来逐个递增项目)。有没有办法不为所有N个项目都创建一个宏呢?
编辑:
这是我目前的宏的样子:
Sub add_1()
Range("B1").Value = Range("B1").Value + 1
End Sub
我从单元格“A1”中的一个按钮启动这个宏。我还想在“A2”中有一个按钮,它会递增单元格“B2”的值,以此类推。
英文:
I created an macro in excel and associated it to a button. Basically, the macro implements a "+" button, which, as the name suggests, increments a counter variable in a cell (say the cell is B1). For instance, if I click twice on the button, B1 stores the value 2, and so on.
I have a longer list of items for which I need this "+" button (N items -> N buttons to increment the items individually). Is there a solution to not create a macro for all N items?
EDIT:
This is how my macro currently looks like:
Sub add_1()
Range("B1").Value = Range("B1").Value + 1
End Sub
I launch this macro from a button in cell "A1". I also want to have a button in "A2", which increments the value in cell "B2", and so on.
答案1
得分: 4
假设你的表格第一行是标题,你想要在C列添加加号按钮,那么以下代码可以帮助你实现。
Option Explicit
Sub add_buttons()
Dim btn As Button
Dim i As Long, last As Long
Dim rng As Range
last = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
For i = 2 To last
If Cells(i, 2) <> "" Then
Set rng = Cells(i, 3)
Set btn = ActiveSheet.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.OnAction = "btn_plus"
.Caption = "+"
.Name = "btn_plus_" & i
End With
Else
Exit For
End If
Next i
Application.ScreenUpdating = True
End Sub
Sub btn_plus()
Dim i As Long
If Left(Application.Caller, 8) = "btn_plus" Then
i = CInt(Mid(Application.Caller, InStrRev(Application.Caller, "_") + 1))
Cells(i, 2) = Cells(i, 2) + 1
End If
End Sub
第一个子过程 add_buttons
在每个B列的值旁边添加按钮。第二个子过程 btn_plus
在点击的按钮旁边的单元格中加1。
英文:
Lets say you have headers in row 1 and you want your plus-button in column C, then the following code will help you.
Option Explicit
Sub add_buttons()
Dim btn As Button
Dim i As Long, last As Long
Dim rng As Range
last = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
For i = 2 To last
If Cells(i, 2) <> "" Then
Set rng = Cells(i, 3)
Set btn = ActiveSheet.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.OnAction = "btn_plus"
.Caption = "+"
.Name = "btn_plus_" & i
End With
Else
Exit For
End If
Next i
Application.ScreenUpdating = True
End Sub
Sub btn_plus()
Dim i As Long
If Left(Application.Caller, 8) = "btn_plus" Then
i = CInt(Mid(Application.Caller, InStrRev(Application.Caller, "_") + 1))
Cells(i, 2) = Cells(i, 2) + 1
End If
End Sub
The first sub add_buttons add buttons next to every value in column B. The second sub btn_plus, adds +1 to the cell next to the button that is clicked on.
答案2
得分: 0
我已经创建了这个宏:
Sub Increase()
ActiveCell.Value = ActiveCell.Value + 1
End Sub
...并添加了一个命令按钮,当点击时启动这个宏。
我选择了单元格"B2",输入了数字5,并点击了两次。
然后我选择了单元格"B3"并点击了两次:
结果截图:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论