AW: Combobox / Textbox
26.12.2004 16:08:39
Josef
Hallo Erich!
Also gut, versuchen wir's!
Ich hab hier eine Code von Thomas Risi der sich dafür eignet.
1.) Erstelle ein KlassenModul (VBE > Einfügen > Klassenmodul) und gib ihm
den Namen "cUFC".
Füge in das Modul dann diesen Code:
Option Explicit
'Idea by Thomas Risi
<a href="'http://rtsoftwaredevelopment.de/">'http://rtsoftwaredevelopment.de/</a>
Private WithEvents cmdBtn As MSForms.CommandButton
Private WithEvents txtBox As MSForms.TextBox
Private WithEvents chkBox As MSForms.CheckBox
Private WithEvents optBtn As MSForms.OptionButton
Private WithEvents lstBox As MSForms.ListBox
Private WithEvents cmbBox As MSForms.ComboBox
Public Function Create(cntrl As MSForms.Control) As Object
Set Create = Nothing
If TypeOf cntrl Is MSForms.CommandButton Then
Set cmdBtn = cntrl
Set Create = Me
ElseIf TypeOf cntrl Is MSForms.TextBox Then
Set txtBox = cntrl
Set Create = Me
ElseIf TypeOf cntrl Is MSForms.CheckBox Then
Set chkBox = cntrl
Set Create = Me
ElseIf TypeOf cntrl Is MSForms.OptionButton Then
Set optBtn = cntrl
Set Create = Me
ElseIf TypeOf cntrl Is MSForms.ListBox Then
Set lstBox = cntrl
Set Create = Me
ElseIf TypeOf cntrl Is MSForms.ComboBox Then
Set cmbBox = cntrl
Set Create = Me
End If
End Function
Private Sub cmbBox_Change()
'Name der UF und Tabellenname anpassen
UserForm1.Controls("TextBox" & Mid(cmbBox.Name, 9, 99) + 5) = _
Sheets("TabellenName").Cells(cmbBox.ListIndex + 2, 3)
End Sub
2.) In das Modul der UF kommt dann noch dieser Code:
Option Explicit
Dim UFControls() As cUFC
Private Sub UserForm_Initialize()
Dim item As MSForms.Control
Dim n%: n = -1
For Each item In Me.Controls
n = n + 1
ReDim Preserve UFControls(n)
Set UFControls(n) = New cUFC
UFControls(n).Create item
Next
End Sub
Wenn du im Initializeereignis der UF bereits Code stehen hast, dann
füge die Codezeilen einfach am anfang ein!
Gruß Sepp