Private Sub CommandButton1_Click()
UserForm_AddAuditor.Show
End Sub
Private Sub CommandButton_Refresh_Click()
LoadAuditoren
End Sub
Private Sub UserForm_Initialize()
LoadAuditoren
End Sub
Private Sub LoadAuditoren()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Auditorenliste")
Me.lstAuditor.Clear
With Me.lstAuditor
.ColumnCount = 11
.ColumnWidths = "50;100;100;250;25;25;25;25;25;25;100"
.BoundColumn = 1
.TextColumn = 1
.ListStyle = fmListStylePlain
.MultiSelect = 0
.MatchEntry = fmMatchEntryFirstLetter
End With
Dim cell As Range
Dim rowIndex As Long
rowIndex = 0
For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
Me.lstAuditor.AddItem cell.Value
Me.lstAuditor.List(rowIndex, 1) = cell.Offset(0, 1).Value
Me.lstAuditor.List(rowIndex, 2) = cell.Offset(0, 2).Value
Me.lstAuditor.List(rowIndex, 3) = cell.Offset(0, 3).Value
Me.lstAuditor.List(rowIndex, 4) = cell.Offset(0, 4).Value
Me.lstAuditor.List(rowIndex, 5) = cell.Offset(0, 5).Value
Me.lstAuditor.List(rowIndex, 6) = cell.Offset(0, 6).Value
Me.lstAuditor.List(rowIndex, 7) = cell.Offset(0, 7).Value
Me.lstAuditor.List(rowIndex, 8) = cell.Offset(0, 8).Value
Me.lstAuditor.List(rowIndex, 9) = cell.Offset(0, 9).Value
Me.lstAuditor.List(rowIndex, 10) = cell.Offset(0, 10).Value
rowIndex = rowIndex + 1
Next cell
End Sub
Private Sub CommandButton_AddAuditor_Click()
UserForm_AddAuditor.Show
End Sub
Private Sub CommandButton_DeleteAuditor_Click()
Dim ws As Worksheet
Dim selectedRow As Long
Dim response As VbMsgBoxResult
If Me.lstAuditor.ListIndex = -1 Then
MsgBox "Bitte wählen Sie einen Auditor aus.", vbExclamation
Exit Sub
End If
response = MsgBox("Möchten Sie den ausgewählten Auditor wirklich löschen?", vbYesNo + vbQuestion)
If response = vbYes Then
Set ws = ThisWorkbook.Sheets("Auditorenliste")
selectedRow = Me.lstAuditor.ListIndex + 2
ws.Rows(selectedRow).Delete
LoadAuditoren
End If
End Sub
Private Sub CommandButton_EditAuditor_Click()
Dim ws As Worksheet
Dim selectedRow As Long
Dim id As Long
If Me.lstAuditor.ListIndex = -1 Then
MsgBox "Bitte wählen Sie einen Auditor aus.", vbExclamation
Exit Sub
End If
On Error GoTo ErrorHandler
id = Me.lstAuditor.List(Me.lstAuditor.ListIndex, 0)
selectedRow = Me.lstAuditor.ListIndex + 2
With UserForm_EditAuditor
Set ws = ThisWorkbook.Sheets("Auditorenliste")
.TextBox_ID.Value = ws.Cells(selectedRow, 1).Value
.TextBox_Name.Value = ws.Cells(selectedRow, 2).Value
.TextBox_Vorname.Value = ws.Cells(selectedRow, 3).Value
.ComboBox_Werk.Value = ws.Cells(selectedRow, 4).Value
.CheckBox_ISO14001.Value = ws.Cells(selectedRow, 5).Value
.CheckBox_ISO45001.Value = ws.Cells(selectedRow, 6).Value
.CheckBox_ISO50001.Value = ws.Cells(selectedRow, 7).Value
.CheckBox_ISO9001.Value = ws.Cells(selectedRow, 8).Value
.CheckBox_IATF.Value = ws.Cells(selectedRow, 9).Value
.TextBox_Credits.Value = ws.Cells(selectedRow, 10).Value
.TextBox_DatumFortbildung.Value = ws.Cells(selectedRow, 11).Value
.Show
End With
Exit Sub
ErrorHandler:
MsgBox "Fehler " & Err.Number & ": " & Err.Description, vbCritical
End Sub