VBA - CommandButton Count only on ActiveSheet

Question

In my Workbook (Book1) i have a CommandButton which opens a new Workbook (Book2) with several sheets and CommandButtons with Code. The CommandButtons in Book2 opens a UserForm and adds Shapes to the Sheet. In each CommandButton there is a counter.

Now my Problem is, that if i click the CommandButton in Sheet1, in Sheet2 the counter also goes up.

Can someone tell me what i have to write to my VBA Code that only the ActiveSheet counter goes up?

I tried it with this in each Sheet.

Private Sub CommandButton1_Click()
    Static cnt As Long
    With ActiveWorkbook.ActiveSheet
        cnt = cnt + 1
    End With

    Call FillFormWKA
End Sub

EDIT:

On each Sheet in Book2 i have a CommandButton, which was added from the CommandButton in Book1. Each CommandButton in Book2 opens a UserForm. On this UserForm i have a CommandButton which saves the addings and adds Shapes to the Sheet. Now my problem is, that if i click on CommandButton on Sheet1, the counter also goes up on the CommandButton on Sheet2.

The whole VBA Code:

Book1 Open new Workbook with CommandButtons and Code:

Private Sub PM_Controlling_Click()
    Dim relativePath As String
    Dim nws As Worksheet
    Dim a As Integer
    Dim b As Integer

    a = 1
    b = 11

    Workbooks.Add
    relativeString = ThisWorkbook.Path & "\Test2"
    ActiveWorkbook.SaveAs Filename:=relativeString & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

'    Add New Sheet if more than one machine is in the Project
     a = a + 1
     b = b + 1
     If Workbooks("Book1.xlsm").Worksheets(b).Visible = xlSheetVisible Then
         With Workbooks(ActiveWorkbook.Name)
             Set nws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
             nws.Name = "Working Plan " & Workbooks("LPA_Projectevaluation_changing_for_PM_Toolbox.xlsm").Worksheets(b).Name
         End With
     End If

     Call Add_CommandButton
End Sub

CommandButton Modul

Sub Add_CommandButton()
    Dim btn As Object
    Dim btn1 As Object

    Dim Code As String
    Dim Code2 As String

    Dim a As Integer
    Dim b As Integer

    Static cnt As Long
    cnt = cnt + 1

    a = 1
    b = 45

'   Add First Button (Opens Userform)
    Set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=b, Top:=175, Width:=50, Height:=25)
    ActiveSheet.OLEObjects(a).Object.Caption = "Add"
    btn.Name = "Add_WKA_" & cnt

    Code = "Private Sub Add_WKA_" & cnt & "_Click()" & vbCrLf
    Code = Code & "    With ActiveWorkbook.ActiveSheet" & vbCrLf
    Code = Code & "        Call Add_WKA_Sheet" & vbCrLf
    Code = Code & "    End With" & vbCrLf
    Code = Code & "End Sub"

    With ActiveWorkbook.VBProject.VBComponents(Worksheets(ActiveSheet.Name).CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

    b = b + 60
    a = a + 1

'   Button to look at UserForm
    Set btn1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=b, Top:=175, Width:=50, Height:=25)
    ActiveSheet.OLEObjects(a).Object.Caption = "Watch"
    btn1.Name = "Watch_WKA_" & cnt

    Code = "Private Sub Watch_WKA_" & cnt & "_Click()" & vbCrLf
    Code = Code & "    Call Watch_WKA_Sheet" & vbCrLf
    Code = Code & "End Sub"


    With ActiveWorkbook.VBProject.VBComponents(Worksheets(ActiveSheet.Name).CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

CommandButton in UserForm:

Private Sub CommandButton1_Click()
    Static cnt As Long
    With ActiveWorkbook.ActiveSheet
        cnt = cnt + 1
    End With

    Call FillFormWKA

    If cnt = 1 Then
        Call AddShape1
    ElseIf cnt = 2 Then
        Call AddShape2
    End If
End Sub

Your With block does nothing here... Regardless, the code you posted doesn't explain what you're seeing: it should work as intended, but what are you doing with cnt ?\ — Tim Williams
yeah i know .. sorry forgot to post the code with cnt .. i will add it now. — diem_L
Really confused me. This code you posted is in Book1 or Book2 (Worksheet or Userform)? I believe it really comes to how those CommandButtons on worksheets in Book2 is written. And is this counter value displayed in the button Caption? — PatricK
Sorry but it's not much clearer now - you need to describe exactly what is happening, and why it's not what you expect to see. Most likely the problem is in the code called from the Click event. — Tim Williams

Shyam Pandey Shyam Pandey · Accepted Answer · 2018-07-10T06:35:33

As I understand your problem that you want to append data . You want to enter those behind previous data. Because you are activating sheets by your code so this is not selecting any range. If it is not your solution then kindly explain your problem.

Private Sub CommandButton1_Click()
Static cnt As Long

cnt = Cells(Rows.Count, "a").End(xlUp).Row + 1

ActiveWorkbook.ActiveSheet.Range("A" & cnt).Select

Call FillFormWKA

End Sub

VBA - CommandButton Count only on ActiveSheet

1 Answers