VBA工时追踪工具双按钮故障：仅一个可正常工作求助

• 我并非专业开发者，只是编程爱好者，若能对我的代码给出基础反馈将不胜感激，这有助于我学习编程。
• 我是物流专业学生，去年课堂上学过不少VBA编程。
• 上周我开始实习，需要追踪工时，因此尝试用VBA开发一个工具：打开Excel工作表，用户输入非美式格式（如01.09.2022）的起始日期，VBA会自动填充日期、星期，并高亮周末等。
• 我的问题：工具中的两个按钮仅一个可正常工作。“Nächster Monat”（下一个月）按钮应执行Tabelle宏以生成新月份的工时表，但无法触发；下方的“Entgelt aktualisieren”（更新薪资）按钮可正常执行Testing宏计算薪资。查看代码发现，创建第二个按钮时错误使用变量b而非btn，导致两个按钮均绑定至Testing宏。

完整代码 #

Sub Tabelle()

    Worksheets.Add
    
    Dim Eingabe As String, T As Integer, Tag As Integer, Z As Long, b As Excel.Shape, Lohn As Double, btn As Excel.Shape

    
    Eingabe = InputBox("Geben Sie bitte das Anfangsdatum des Monats an, z.B. 01.09.2022")
    
    ActiveSheet.Name = "Zeiterfassung " & Eingabe
    
    'Tagesanzahl für eingegebenen Monat finden
    If Mid(Eingabe, 4, 2) = "01" Then T = 31
    If Mid(Eingabe, 4, 2) = "02" Then T = 29
    If Mid(Eingabe, 4, 2) = "03" Then T = 31
    If Mid(Eingabe, 4, 2) = "04" Then T = 30
    If Mid(Eingabe, 4, 2) = "05" Then T = 31
    If Mid(Eingabe, 4, 2) = "06" Then T = 30
    If Mid(Eingabe, 4, 2) = "07" Then T = 31
    If Mid(Eingabe, 4, 2) = "08" Then T = 31
    If Mid(Eingabe, 4, 2) = "09" Then T = 30
    If Mid(Eingabe, 4, 2) = "10" Then T = 31
    If Mid(Eingabe, 4, 2) = "11" Then T = 30
    If Mid(Eingabe, 4, 2) = "12" Then T = 31
    
    'Datum erstellen in Spalte 1
    Tag = Left(Eingabe, 2)
    
    For i = 3 To (T + 2)
        Cells(i, 1) = Format(Tag, "00") & "." & Mid(Eingabe, 4, 99)
        Tag = Tag + 1
    Next i
    
    'Wochentage für jedes Datum eintragen in Spalte 2
    i = 3
    Do While Cells(i, 1) <> ""
        If Weekday(Cells(i, 1)) = 1 Then Cells(i, 2) = "Sonntag"
        If Weekday(Cells(i, 1)) = 2 Then Cells(i, 2) = "Montag"
        If Weekday(Cells(i, 1)) = 3 Then Cells(i, 2) = "Dienstag"
        If Weekday(Cells(i, 1)) = 4 Then Cells(i, 2) = "Mittwoch"
        If Weekday(Cells(i, 1)) = 5 Then Cells(i, 2) = "Donnerstag"
        If Weekday(Cells(i, 1)) = 6 Then Cells(i, 2) = "Freitag"
        If Weekday(Cells(i, 1)) = 7 Then Cells(i, 2) = "Samstag"
        
        i = i + 1
    Loop
    
    Z = 3
    
    Do While Cells(Z, 2) <> ""
        If Cells(Z, 2) = "Samstag" Or Cells(Z, 2) = "Sonntag" Then
            Cells(Z, 1).Interior.ColorIndex = 6
            Cells(Z, 2).Interior.ColorIndex = 6
            Cells(Z, 3).Interior.ColorIndex = 6
            Cells(Z, 4).Interior.ColorIndex = 6
            Cells(Z, 5).Interior.ColorIndex = 6
            Cells(Z, 6).Interior.ColorIndex = 6
        End If
        Z = Z + 1
    Loop
   
    'Code für Stunden gearbeitet
    For i = 3 To (T + 2)
        Cells(i, 5) = "=" & "(D" & i & "-" & "C" & i & ") * 24"
    Next i
    
    'Button für neuen Monat
    Set b = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 265, 500, 100, 50)
    b.OnAction = "Tabelle"
    b.OLEFormat.Object.Text = "Nächster Monat"
    
    Set btn = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 300, 470, 100, 50)
    b.OnAction = "Testing"
    b.OLEFormat.Object.Text = "Entgelt aktualisieren"
        
    Range("C34").FormulaLocal = "=Summe(E3:E33)-Summe(F3:F33)"
    Range("A35") = "Entgelt"
    
    Lohn = InputBox("Geben Sie ihren Stundenlohn ein!")
    Range("A40") = "Stundenlohn"
    Range("B40") = Format(Lohn, "00.00 €")
    
     Range("A3:F" & T + 2).BorderAround LineStyle:=xlContinuous, Weight:=xlThick
    Range("A2:F2").BorderAround LineStyle:=xlContinuous, Weight:=xlThick
    Range("A2") = "Datum"
    Range("B2") = "Tag"
    Range("C2") = "Von"
    Range("D2") = "Bis"
    Range("E2") = "Std."
    Range("F2") = "Pause in Std."
    Range("A34") = "Stunden gesamt"
    

End Sub

Sub Testing()

    Range("C35") = Format(Left(Range("C34"), 2) * Range("B40"), "#,#00.00 €")

End Sub

工作表截图 #

• 工作表1
• 工作表2

内容的提问来源于stack exchange，提问作者alex345boy