AW: Auf geschlossene xlsm Datei zugreifen
13.09.2020 16:34:57
Nepumuk
Hallo Tobias,
im Modul der Tabelle:
Option Explicit
Private mavntList1 As Variant
Private mavntList2 As Variant
Private mavntList3 As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.Visible = False
'--- Prüfen, ob Combobox gezeigt werden muss
If Target.Row <= 1 Then Exit Sub 'abbruch bei Überschrift
If Target.Cells.Count > 1 Then Exit Sub 'abbruch wenn mehrere Zellen
Select Case Target.Column
Case 1, 2, 3 'hier die relevanten Spalten aufführen
Case Else
Exit Sub
End Select
'--- Combobox positionieren
ComboBox1.Top = Target.Top - 1
ComboBox1.Left = Target.Left - 1
ComboBox1.Height = Target.Height + 5
ComboBox1.Width = Target.Width + 16
'--- Zellbereiche zufügen
Select Case Target.Column
Case 1: ComboBox1.List = List1
Case 2: ComboBox1.List = List2
Case 3: ComboBox1.List = List3
End Select
'--- ausgabezelle
ComboBox1.LinkedCell = Target.Address
'--- Anzeigen und Focus
ComboBox1.Visible = True
ComboBox1.Activate
End Sub
Friend Property Get List1() As Variant
List1 = mavntList1
End Property
Friend Property Let List1(ByRef pravntList As Variant)
mavntList1 = pravntList
End Property
Friend Property Get List2() As Variant
List2 = mavntList2
End Property
Friend Property Let List2(ByRef pravntList As Variant)
mavntList2 = pravntList
End Property
Friend Property Get List3() As Variant
List3 = mavntList3
End Property
Friend Property Let List3(ByRef pravntList As Variant)
mavntList3 = pravntList
End Property
Im Modul der Mappe (DieseArbeitsmappe):
Option Explicit
Private Sub Workbook_Open()
Dim objWorkbook As Workbook
Set objWorkbook = GetObject(Pathname:=ThisWorkbook.Path & "\Masterdatei.xlsm")
With objWorkbook.Worksheets("Master")
Tabelle1.List1 = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value2
Tabelle1.List2 = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).Value2
Tabelle1.List3 = .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp)).Value2
End With
Call objWorkbook.Close(SaveChanges:=False)
Set objWorkbook = Nothing
End Sub
Ohne einmal öffnen geht es nicht. Das passiert aber ohne für den User sichtbar zu werden.
Gruß
Nepumuk