Macro in excel to implement a "+" button for N items. how to create only one macro but increment all items individually?

huangapple go评论63阅读模式
英文:

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) &lt;&gt; &quot;&quot; Then
        Set rng = Cells(i, 3)
        Set btn = ActiveSheet.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        With btn
            .OnAction = &quot;btn_plus&quot;
            .Caption = &quot;+&quot;
            .Name = &quot;btn_plus_&quot; &amp; 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) = &quot;btn_plus&quot; Then
    i = CInt(Mid(Application.Caller, InStrRev(Application.Caller, &quot;_&quot;) + 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"并点击了两次:

结果截图:

Macro in excel to implement a "+" button for N items. how to create only one macro but increment all items individually?

英文:

I have created this macro:

Sub Increase()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

... and added a commandbutton, launching this macro, when clicked.

I selected cell "B2", entered the number 5 and clicked two times.
Then I selected cell "B3" and clicked two times:

Resulting screenshot:

Macro in excel to implement a "+" button for N items. how to create only one macro but increment all items individually?

huangapple
  • 本文由 发表于 2023年7月31日 18:18:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76802649.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定