AW: Viele Button
06.10.2020 10:47:49
Nepumuk
Hallo Daniel,
folgende Prozeduren ändern:
Private Sub UserForm_Initialize()
Dim objControl As Control
Dim objCommndButtonClass As clsCommandButton
Dim lngIndex As Long
Frame1.Caption = Format$(Tabelle1.Name, "dddd, dd.mm.yyyy")
Frame2.Caption = Format$(Tabelle2.Name, "dddd, dd.mm.yyyy")
Frame3.Caption = Format$(Tabelle3.Name, "dddd, dd.mm.yyyy")
Frame4.Caption = Format$(Tabelle4.Name, "dddd, dd.mm.yyyy")
Frame5.Caption = Format$(Tabelle5.Name, "dddd, dd.mm.yyyy")
Frame6.Caption = Format$(Tabelle6.Name, "dddd, dd.mm.yyyy")
Set CommndButtonClassCollection = New Collection
For lngIndex = 1 To 6
For Each objControl In Controls("Frame" & CStr(lngIndex)).Controls
If TypeOf objControl Is MSForms.CommandButton Then
Set objCommndButtonClass = New clsCommandButton
With objCommndButtonClass
Set .CommandButton = objControl
.FrameNumber = lngIndex
Select Case objControl.Caption
Case "07:00 Uhr": .CommandButtonNumber = 4
Case "07:30 Uhr": .CommandButtonNumber = 5
Case "08:00 Uhr": .CommandButtonNumber = 6
Case "08:30 Uhr": .CommandButtonNumber = 7
Case "Pause": .CommandButtonNumber = 8
Case "09:30 Uhr": .CommandButtonNumber = 9
Case "10:00 Uhr": .CommandButtonNumber = 10
Case "10:30 Uhr": .CommandButtonNumber = 11
Case "11:00 Uhr": .CommandButtonNumber = 12
Case "11:30 Uhr": .CommandButtonNumber = 13
Case "13:00 Uhr": .CommandButtonNumber = 14
Case "13:30 Uhr": .CommandButtonNumber = 15
Case "14:00 Uhr": .CommandButtonNumber = 16
Case "14:30 Uhr": .CommandButtonNumber = 17
Case "15:00 Uhr": .CommandButtonNumber = 18
Case "15:30 Uhr": .CommandButtonNumber = 19
Case "16:00 Uhr": .CommandButtonNumber = 20
Case "16:30 Uhr": .CommandButtonNumber = 21
Case "17:00 Uhr": .CommandButtonNumber = 22
Case "17:30 Uhr": .CommandButtonNumber = 23
Case "18:00 Uhr": .CommandButtonNumber = 24
Case "18:30 Uhr": .CommandButtonNumber = 25
Case "19:00 Uhr": .CommandButtonNumber = 26
Case "19:30 Uhr": .CommandButtonNumber = 27
Case "20:00 Uhr": .CommandButtonNumber = 28
Case "Kopieren": .CommandButtonNumber = 1
End Select
If Not IsEmpty(Worksheets(lngIndex).Cells(.CommandButtonNumber, 2).Value) Then _
objControl.BackColor = vbRed
Call CommndButtonClassCollection.Add(Item:=objCommndButtonClass, _
Key:=CStr(lngIndex) & "|" & CStr(.CommandButtonNumber))
End With
End If
Next
Next
Set objCommndButtonClass = Nothing
End Sub
Private Sub mobjCommandButton_Click()
If CommandButton.Caption = "Kopieren" Then
Call CopyRange
ElseIf CommandButton.BackColor = vbRed Then
If MsgBox("Soll der Kunde gelöscht werden?", vbQuestion Or vbYesNo, _
"Sicherheitsabfrage") = vbYes Then Call SelectTime
Else
Call SelectTime
End If
End Sub
Neu im Klassenmodul:
Private Sub CopyRange()
With Tabelle7
Call Worksheets(FrameNumber).Range("B4:E28").Copy(Destination:=.Range("A2"))
Call .Select
End With
End Sub
Gruß
Nepumuk