Public Variable verliert ihren Wert

Option Explicit
Public Kategorie As String 'Global Verfügbar, enthält Text aus Auswahl userform2, um in  _
Userform 3 zu verwenden
Public Auswahllistbox1 As String
Public Tbox2 As String
Public CoBo1 As String
Public CoBo2 As String
Public CoBo3 As String
Public CoBo4 As String
Public CoBo5 As String
Public CoBo6 As String
Public CoBo7 As String
Public CoBo8 As String
Public CoBo9 As String
Public CoBo10 As String
Public CoBo11 As String
Public CoBo12 As String
Public CoBo13 As String
Public CoBo14 As String
Public AktName As String
Dim vOldVal

Private Sub Go_Click() ' Darf nur starten, wenn alle Felder ausgefüllt sind
Dim LastRow As Long
Dim rng As Range
Dim rng1 As Range
Dim wsqm As Worksheet
Dim MA As Long
Dim Flaeche As Long
Dim Umsatz As Long
Dim SOP As Long
Dim MaFlUm As Long
Dim Immobilie As Long
Dim Software As Long
Dim Hardware As Long
Dim Prozesse As Long
Dim FFZ As Long
Dim QM As Long
Dim Personal As Long
Dim ProduktKategorie As Long
Dim SummebisaufSOP As Long
Dim objtxt As Object
'On Error GoTo Err_irgendwas
Set wsqm = Sheets("PM")
For Each objtxt In UserForm1.Controls 'Überprüfe ob überall eine Auswahl getroffen wurde
If TypeName(objtxt) = "ComboBox" Then
If objtxt.Value = "" Then
MsgBox " Es wurden nicht alle Kategorien ausgefüllt!", 48
objtxt.SetFocus
Exit Sub
End If
End If
Next
Call deletePM
Worksheets("PM").Unprotect Password:="Secret"
Worksheets("PM").Range("A:H").Locked = False
'Combobox 1-7
CoBo1 = ComboBox1.Value ' Immobilie
CoBo2 = ComboBox2.Value ' Software
CoBo3 = ComboBox3.Value ' Prozesse
CoBo4 = ComboBox4.Value ' Hardware
CoBo5 = ComboBox5.Value ' QM
CoBo6 = ComboBox6.Value ' FFZ
CoBo7 = ComboBox7.Value ' Personal
CoBo9 = ComboBox9.Value
CoBo10 = ComboBox10.Value
CoBo11 = ComboBox11.Value
CoBo12 = ComboBox12.Value
Debug.Print CoBo1
Debug.Print "go"
Debug.Print CoBo2
Application.ScreenUpdating = False
If ComboBox1.Value = "Neubau" Then
Immobilie = 9
ElseIf ComboBox1.Value = "RLG stellt Immobilie" Then
Immobilie = 5
ElseIf ComboBox1.Value = "Kunde stellt Immobilie" Then
Immobilie = 3
End If
If ComboBox2.Value = "Fremde Software" Then
Software = 3
ElseIf ComboBox2.Value = "Eigene Software" Then
Software = 5
ElseIf ComboBox2.Value = "Eigene und fremde Software" Then
Software = 9
End If
If ComboBox3.Value = "Neue Prozesse" Then
Prozesse = 9
ElseIf ComboBox3.Value = "Prozesse Kunde" Then
Prozesse = 3
ElseIf ComboBox3.Value = "Prozesse LDL" Then
Prozesse = 5
End If
If ComboBox4.Value = "Fremde Hardware (Prozessrelevant)" Then
Hardware = 3
ElseIf ComboBox4.Value = "Eigene Hardware (Nur Kommunikation)" Then
Hardware = 5
ElseIf ComboBox4.Value = "Eigene und fremde Hardware" Then
Hardware = 9
End If
If ComboBox5.Value = "ISO 9001" Then
QM = 3
ElseIf ComboBox5.Value = "ISO 14001" Then
QM = 5
ElseIf ComboBox5.Value = "OHSAS 18001" Then
QM = 9
End If
If ComboBox6.Value = "FFZ werden vom Kunde gestellt" Then
FFZ = 3
ElseIf ComboBox6.Value = "FFZ werden vom LDL übernommen" Then
FFZ = 5
ElseIf ComboBox6.Value = "FFZ müssen beschafft werden" Then
FFZ = 9
End If
If ComboBox7.Value = "Betriebsübergang" Then
Personal = 5
ElseIf ComboBox7.Value = "Neueinstellungen 100%" Then
Personal = 9
ElseIf ComboBox7.Value = "Neueinstellungen Hochlauf" Then
Personal = 5
End If
If ComboBox9.Value = "200 MA" Then
MA = 9
End If
If ComboBox10.Value = "20 Millionen" Then
Umsatz = 9
End If
If ComboBox11.Value = "75.000m²" Then
Flaeche = 9
End If
If ComboBox12.Value = "12 Monate" Then
SOP = 1
End If
MaFlUm = (MA + Flaeche + Umsatz) / 3
ProduktKategorie = (Hardware + Software + Prozesse + Personal + QM + Immobilie + FFZ) / 7
SummebisaufSOP = MaFlUm + ProduktKategorie
If ComboBox12.Value = "12 Monate" Then
Range("F95").Value = SummebisaufSOP
Range("F96").Value = 0
Range("F97").Value = 0
Range("F98").Value = 0
End If
Range("E98").Value = "12 Monate"
Range("E95:F98").Font.ColorIndex = 2
Range("G95").Value = "Ihr Projekt"
Range("G96").Value = "Ihr Projekt"
Range("G97").Value = "Ihr Projekt"
Range("G98").Value = "Ihr Projekt"
Range("G95:G98").Font.ColorIndex = 2
Call ChartErstellen
'case 1.QM
'immer 2.Betriebsübergang
'case 3.Personal
'case 4.Immobilie
'5.Projektcontrolling
'immer 6.Betriebsmittel
'immer 7.Betriebsmittel_Anlauf
'case 8.FFZ_Übergang
'case 8.FFZ_Neubau
'immer 9.Betriebsausstattung
'case 10.EDV_Software
'case 11.EDV_Hardware
'immer 12.Operative_Unterstützung
'immer 13.Sicherheit
'immer 14.Prozesse
'immer 15. Sonstiges
Select Case ComboBox1.Value
Case "Neubau"
Set rng = Worksheets("4.Immobilie_Neubau").UsedRange.Columns(1). _
Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "RLG stellt Immobilie"
Set rng = Worksheets("4.Immobilie").UsedRange.Columns(1).Resize(, 2) _
_
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Kunde stellt Immobilie"
Set rng = Worksheets("4.Immobilie").UsedRange.Columns(1).Resize(, 2) _
_
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox2.Value
Case "Fremde Software"
Set rng = Worksheets("10.EDV_Software").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Eigene Software"
Set rng = Worksheets("10.EDV_Software").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Eigene und fremde Software"
Set rng = Worksheets("10.EDV_Software").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox3.Value
Case "Neue Prozesse"
Set rng = Worksheets("14.Prozesse_Neu").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Prozesse Kunde"
Set rng = Worksheets("14.Prozesse").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Prozesse LDL"
Set rng = Worksheets("14.Prozesse").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox4.Value
Case "Fremde Hardware (Prozessrelevant)"
Set rng = Worksheets("11.EDV_Hardware").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Eigene Hardware (Nur Kommunikation)"
Set rng = Worksheets("11.EDV_Hardware").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Eigene und fremde Hardware"
Set rng = Worksheets("11.EDV_Hardware").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox5.Value
Case "ISO 9001"
Set rng = Worksheets("1.QM").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "ISO 14001"
Set rng = Worksheets("1.QM").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "OHSAS 18001"
Set rng = Worksheets("1.QM").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox6.Value
Case "FFZ werden vom Kunde gestellt"
Set rng = Worksheets("8.FFZ_Übernahme").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "FFZ werden vom LDL übernommen"
Set rng = Worksheets("8.FFZ_Übernahme").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "FFZ müssen beschafft werden"
Set rng = Worksheets("8.FFZ_Neuerwerb").UsedRange.Columns(1).Resize( _
_
, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Select Case ComboBox7.Value
Case "Betriebsübergang"
Set rng = Worksheets("2.Betriebsübergang").UsedRange.Columns(1). _
Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("3.Personal").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Neueinstellungen 100%"
Set rng = Worksheets("3.Personal").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Case "Neueinstellungen Hochlauf"
Set rng = Worksheets("3.Personal").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
End Select
Set rng = Worksheets("6.Betriebsmittel").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("7.Betriebsmittel_Anlauf").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("9.Betriebsausstattung").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("5.Projektcontrolling").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("12.Operative_Unterstützung").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("13.Sicherheit").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Set rng = Worksheets("15.Sonstiges").UsedRange.Columns(1).Resize(, 2)
Set rng1 = Worksheets("PM").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Copy Destination:=rng1
Call sor(wsqm)
Call Checkliste
Worksheets("PM").Unprotect Password:="Secret"
Worksheets("PM").Range("A:H").Locked = False
Call gruppieren
Columns("B:B").EntireColumn.AutoFit
Call ausrichten
Unload UserForm1
ActiveSheet.Shapes.Range(Array("Bild")).Select
Selection.ShapeRange.ZOrder msoBringToFront
Application.ScreenUpdating = True
Worksheets("PM").Range("A:H").Locked = True
With Worksheets("PM")
.Protect Password:="Secret", DrawingObjects:=False, Contents:=True, Scenarios:=True ' _
UserInterfaceOnly:=True
.EnableOutlining = True
End With
MsgBox ("Bitte markieren Sie in Spalte C durch anklicken der Chekboxen, welche Punkte für Ihr   _
_
Projekt relevant sind! Klicken Sie im Anschluss auf ""Druckseite erstellen"""), vbInformation
Debug.Print CoBo1
Debug.Print "go2"
Debug.Print CoBo2
Worksheets("PM").OLEObjects("CommandButton24").Enabled = True
' Exit Sub
'Err_irgendwas:
' If Err.Number = 1004 Then
' Call deletePM
' ' besondere Aktion: Schicke Fehler in die Kalahari
' Else
' MsgBox Err.Number, Err.Description
End Sub

Private Sub UserForm_Initialize()
Debug.Print CoBo1
Debug.Print "Init"
Debug.Print CoBo2
With Me.ComboBox1 'Immobilie
.AddItem "Neubau"
.AddItem "RLG stellt Immobilie"
.AddItem "Kunde stellt Immobilie"
' .Style = fmStyleDropDownList
If CoBo1 = "Neubau" Then
.ListIndex = 0
ElseIf CoBo1 = "RLG stellt Immobilie" Then
.ListIndex = 1
ElseIf CoBo1 = "Kunde stellt Immobilie" Then
.ListIndex = 2
End If
End With
With Me.ComboBox2 'Software
.AddItem "Fremde Software"
.AddItem "Eigene Software"
.AddItem "Eigene und fremde Software"
.Style = fmStyleDropDownList
If CoBo2 = "Fremde Software" Then
.ListIndex = 0
ElseIf CoBo2 = "Eigene Software" Then
.ListIndex = 1
ElseIf CoBo2 = "Eigene und fremde Software" Then
.ListIndex = 2
End If
End With
With Me.ComboBox3 'Prozesse
.AddItem "Neue Prozesse"
.AddItem "Prozesse Kunde"
.AddItem "Prozesse LDL"
.Style = fmStyleDropDownList
If CoBo3 = "Neue Prozesse" Then
.ListIndex = 0
ElseIf CoBo3 = "Prozesse Kunde" Then
.ListIndex = 1
ElseIf CoBo3 = "Prozesse LDL" Then
.ListIndex = 2
End If
End With
With Me.ComboBox4 'Hardware
.AddItem "Fremde Hardware (Prozessrelevant)"
.AddItem "Eigene Hardware (Nur Kommunikation)"
.AddItem "Eigene und fremde Hardware"
.Style = fmStyleDropDownList
If CoBo4 = "Fremde Hardware (Prozessrelevant)" Then
.ListIndex = 0
ElseIf CoBo4 = "Eigene Hardware (Nur Kommunikation)" Then
.ListIndex = 1
ElseIf CoBo4 = "Eigene und fremde Hardware" Then
.ListIndex = 2
End If
End With
With Me.ComboBox5 'QM
.AddItem "ISO 9001"
.AddItem "ISO 14001"
.AddItem "OHSAS 18001"
.Style = fmStyleDropDownList
If CoBo5 = "ISO 9001" Then
.ListIndex = 0
ElseIf CoBo5 = "ISO 14001" Then
.ListIndex = 1
ElseIf CoBo5 = "OHSAS 18001" Then
.ListIndex = 2
End If
End With
With Me.ComboBox6 'FFZ
.AddItem "FFZ werden vom Kunde gestellt"
.AddItem "FFZ werden vom LDL übernommen"
.AddItem "FFZ müssen beschafft werden"
.Style = fmStyleDropDownList
If CoBo6 = "FFZ werden vom Kunde gestellt" Then
.ListIndex = 0
ElseIf CoBo6 = "FFZ werden vom LDL übernommen" Then
.ListIndex = 1
ElseIf CoBo6 = "FFZ müssen beschafft werden" Then
.ListIndex = 2
End If
End With
With Me.ComboBox7 'Personal
.AddItem "Betriebsübergang"
.AddItem "Neueinstellungen 100%"
.AddItem "Neueinstellungen Hochlauf"
.Style = fmStyleDropDownList
If CoBo7 = "Betriebsübergang" Then
.ListIndex = 0
ElseIf CoBo7 = "Neueinstellungen 100%" Then
.ListIndex = 1
ElseIf CoBo7 = "Neueinstellungen Hochlauf" Then
.ListIndex = 2
End If
End With
With Me.ComboBox9 'Anzahl MA
.AddItem "200 MA"
.Style = fmStyleDropDownList
If CoBo9 = "200 MA" Then
.ListIndex = 3
End If
End With
With Me.ComboBox10 'Umsatz
.AddItem "20 Millionen"
.Style = fmStyleDropDownList
If CoBo10 = "20 Millionen" Then
.ListIndex = 3
End If
End With
With Me.ComboBox12 'Start of Production
.AddItem "12 Monate"
.Style = fmStyleDropDownList
If CoBo12 = "12 Monate" Then
.ListIndex = 3
End If
End With
With Me.ComboBox11 'Fläche
.AddItem "75.000m²"
.Style = fmStyleDropDownList
If CoBo11 = "75.000m²" Then
.ListIndex = 3
End If
End With
End Sub