ich habe einen Button, der beim Drücken eine Listbox mit Projekten öffnet, die in meinem Reiter "ACTIVE" vorhanden sind. Dann hat man anschließend die Wahl, ob man das Projekt "einfriert" oder "aktiviert" (dies funktioniert auch einwandfrei!)...Mein Anliegen ist nun folgendes: In der Listbox werden die Spalten 12 bis 14 des jeweiligen Projektes angezeigt...Ich möchte allerdings, dass die Spalten 10 bis 12 angezeigt werden...Wie kann ich das schaffen? Ich danke euch im Voraus!!
Hier wäre noch mein Code:
Private Sub CommandButton1_Click()
Dim i, j, k, m, n, o, p, Index_ID, Offset_row, File_Index_ As Integer
Dim LINK_, File_Name_, Project_Titel_, Path_, Switch_1, OCIP_Workbook_Name_, _
Hours_Workbook_Name_, Text As String
Dim Status_in_hours_booking, Proj_ID_in_hours_booking As Variant
'Konstante
Offset_row = 6
Index_ID = UserForm2.ListBox1.ListIndex
'Daten in ACTIVE speichern
With Workbooks(ThisWorkbook.Name).Worksheets("ACTIVE")
j = 5 ' Spalte
.Cells(Index_ID + Offset_row, j).Value = "frozen"
Status_in_hours_booking = .Cells(Index_ID + Offset_row, 5).Value
Proj_ID_in_hours_booking = .Cells(Index_ID + Offset_row, 2).Value
End With
UserForm2.CommandButton1.Enabled = False
'*** Daten aktualisieren in hours_booking_ *****
With Workbooks(ThisWorkbook.Name).Worksheets("Set-up")
i = 50 ' Reihe Adressfeld = 50..400
j = 1 ' Spalte
Switch_1 = ""
Switch_2 = ""
'.Activate
Do
If .Cells(i, j).Value = "PATH_HOURS_BOOKING_" Then
LINK_ = .Cells(i, j + 1).Value
Switch_1 = "1"
End If
i = i + 1
Loop Until .Cells(i, j).Value = "" Or i = 400
i = 50 ' Reihe Adressfeld = 50..400 in Set-up
o = 2 ' Reihe in Errors
Do
If .Cells(i, j).Value = "FILE_HOURS_BOOKING_" Then
File_Name_ = .Cells(i, j + 1).Value
Switch_2 = "1"
End If
Offset_row = 6
k = 10
Project_Titel_ = Workbooks(ThisWorkbook.Name).Worksheets("ACTIVE").Cells(Index_ID + _
Offset_row, k).Value
OCIP_Workbook_Name_ = ThisWorkbook.Name
Path_ = LINK_ & File_Name_ & ".xlsm"
If File_Name_ "" Then
ChDir LINK_
Workbooks.Open Filename:=Path_, UpdateLinks:=0
ActiveWorkbook.RunAutoMacros xlAutoOpen
Hours_Workbook_Name_ = File_Name_ & ".xlsm"
m = 1 ' project title Row in hours booking
n = 1 ' project title column in hours booking
Do
Test = Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n).Value
If Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n).Value = Project_Titel_ Then
Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n + 1).Value = Status_in_hours_booking
Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n + 2).Value = Proj_ID_in_hours_booking
Exit Do
End If
m = m + 1
Loop Until m = 2000 ' limit for item number in hours booking. searching range = 1.. _
2000
If m
Private Sub CommandButton2_Click()
Dim i, j, k, m, n, o, p, Index_ID, Offset_row, File_Index_ As Integer
Dim LINK_, File_Name_, Project_Titel_, Path_, Switch_1, OCIP_Workbook_Name_, _
Hours_Workbook_Name_, Text As String
Dim Status_in_hours_booking, Proj_ID_in_hours_booking As Variant
'Konstante
Offset_row = 6
Index_ID = UserForm2.ListBox1.ListIndex
'Daten in ACTIVE speichern
With Workbooks(ThisWorkbook.Name).Worksheets("ACTIVE")
j = 5 ' Spalte
.Cells(Index_ID + Offset_row, j).Value = "active"
Status_in_hours_booking = .Cells(Index_ID + Offset_row, j).Value
Proj_ID_in_hours_booking = .Cells(Index_ID + Offset_row, 2).Value
End With
UserForm2.CommandButton2.Enabled = False
'*** Daten aktualisieren in hours_booking_ *****
With Workbooks(ThisWorkbook.Name).Worksheets("Set-up")
i = 50 ' Reihe Adressfeld = 50..400
j = 1 ' Spalte
Switch_1 = ""
Switch_2 = ""
'.Activate
Do
If .Cells(i, j).Value = "PATH_HOURS_BOOKING_" Then
LINK_ = .Cells(i, j + 1).Value
Switch_1 = "1"
End If
i = i + 1
Loop Until .Cells(i, j).Value = "" Or i = 400
i = 50 ' Reihe Adressfeld = 50...400 in Set-up
o = 2 ' Reihe in Errors
Do
If .Cells(i, j).Value = "FILE_HOURS_BOOKING_" Then
File_Name_ = .Cells(i, j + 1).Value
Switch_2 = "1"
End If
Offset_row = 6
k = 10
Project_Titel_ = Workbooks(ThisWorkbook.Name).Worksheets("ACTIVE").Cells(Index_ID + _
Offset_row, k).Value
OCIP_Workbook_Name_ = ThisWorkbook.Name
Path_ = LINK_ & File_Name_ & ".xlsm"
If File_Name_ "" Then
ChDir LINK_
Workbooks.Open Filename:=Path_, UpdateLinks:=0
ActiveWorkbook.RunAutoMacros xlAutoOpen
Hours_Workbook_Name_ = File_Name_ & ".xlsm"
m = 1 ' project title Row in hours booking
n = 1 ' project title column in hours booking
Do
Test = Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n).Value
If Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n).Value = Project_Titel_ Then
Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n + 1).Value = Status_in_hours_booking
Workbooks(Hours_Workbook_Name_).Worksheets("Project_and_activity_list"). _
Cells(m, n + 2).Value = Proj_ID_in_hours_booking
Exit Do
End If
m = m + 1
Loop Until m = 2000 ' limit for item number in hours booking. searching range = 1.. _
2000
If m
Private Sub Frame1_Click()
End Sub
Private Sub ListBox1_Click()
Dim Offset_row, Index_ID As Integer
'Konstante
Offset_row = 6
'Markierung Daten in Reiter ACTIVE
With Workbooks(ThisWorkbook.Name).Worksheets("ACTIVE")
.Activate
Index_ID = UserForm2.ListBox1.ListIndex
Adresse_ = "b" & CStr(Index_ID + Offset_row) & ":ae" & CStr(Index_ID + Offset_row)
.Range(Adresse_).Select
If UCase(.Cells(Index_ID + Offset_row, 5)) = "ACTIVE" Then
UserForm2.CommandButton1.Enabled = True
UserForm2.CommandButton2.Enabled = False
ElseIf UCase(.Cells(Index_ID + Offset_row, 5)) = "FROZEN" Then
UserForm2.CommandButton2.Enabled = True
UserForm2.CommandButton1.Enabled = False
Else
UserForm2.CommandButton1.Enabled = False
UserForm2.CommandButton2.Enabled = False
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim i, j, k, l, Index_ID As Integer
Dim Adresse_ As String
UserForm2.ListBox1.ColumnCount = 7
'Leere Reihe finden in: ACTIVE
With Worksheets("ACTIVE")
.Activate
i = 6 ' Reihe Anfang
k = i 'Reihe Ende
j = 10 'Spalte Anfang
l = 12
Do
k = k + 1
Loop Until .Cells(k, j).Value = "" Or k = 2000
End With
Adresse_ = "l" & CStr(i) & ":n" & CStr(k)
UserForm2.ListBox1.RowSource = Adresse_
End Sub