UserForm, ComboBox, TextBox - variabel ansprechen...

Frage: Userformen, ComboBoxen und Textboxen variabel ansprechen. Ich habe mehrere UserFormen. Auf Grund von Usereingaben muss eine bestimmte UserForm geöffnet werden. Eine TextBox soll schon einen bestimmten Text enthalten. Eine ComboBox einen bestimmten Wert anzeigen. Wie geht das?

User forms, combo boxes and text boxes respond variably. I have several user forms. Based on user input, a certain UserForm to open. A TextBox is already contain specific text. A ComboBox show a certain value. How does it work?

Hier noch eine Beispieldatei / Here's a sample file:
UserForm, ComboBox, TextBox variabel ansprechen...[XLS 60 KB]

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Module1 
' Procedure : Main 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 22.02.2013 
' Purpose   : UserForms and TextBoxes to address variable... 
'-------------------------------------------------------------------------- 
Public Sub Main()
    Dim intCount2 As Integer
    Dim intCount1 As Integer
    Dim intCount As Integer
    On Error GoTo Fin
    intCount2 = 1
    intCount1 = 2
    intCount = 3
    Call myForm("Userform" & intCount, "TextBox" & intCount1, _
        "ComboBox" & intCount2)
Fin:
    If Err.Number <> 0 Then MsgBox "Error: " & _
        Err.Number & " " & Err.Description
End Sub
'-------------------------------------------------------------------------- 
' Module    : Module1 
' Procedure : myForm 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 22.02.2013 
' Purpose   : UserForms and TextBoxes to address variable... 
'-------------------------------------------------------------------------- 
Private Sub myForm(strForm As String, strBox As String, strCombo As String)
    Dim blnCombo As Boolean
    Dim blnForm As Boolean
    Dim blnBox As Boolean
    Dim intTMP As Integer
    Dim objBox As Object
    With ThisWorkbook.VBProject
        For intTMP = 1 To .VBComponents.Count
            If .VBComponents.Item(intTMP).Type = 3 Then
                If UCase$(.VBComponents.Item(intTMP).Name) = _
                    UCase$(strForm) Then
                    blnForm = True
                    For Each objBox In .VBComponents(strForm). _
                        Designer.Controls
                        If objBox.Name = strBox Then
                            blnBox = True
                        ElseIf objBox.Name = strCombo Then
                            blnCombo = True
                        End If
                        If blnBox And blnCombo Then
                            .VBComponents(strForm). _
                                Designer.Controls(strBox).Text = "Test"
                            .VBComponents(strForm). _
                                Designer.Controls(strCombo).ListIndex = 3
                            UserForms.Add(strForm).Show
                            .VBComponents(strForm). _
                                Designer.Controls(strBox).Text = ""
                            Exit For
                        End If
                    Next objBox
                End If
            End If
        Next intTMP
    End With
    If Not blnForm Then blnBox = True: blnCombo = True: _
        MsgBox "UserForm does not exist!", vbCritical
    If Not blnBox Then MsgBox "TextBox does not exist!", vbCritical
    If Not blnCombo Then MsgBox "ComboBox does not exist!", vbCritical
End Sub

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...