Consulting

Results 1 to 12 of 12

Thread: Linking Label Caption To Textbox

  1. #1
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location

    Linking Label Caption To Textbox

    I need a macro code that links an array of Labels to an array of Textboxes.

    These controls are located on a Userform.

    Ex: Label1.Caption = Textbox1.Text ... Label2.Caption = Textbox2.Text.

    There will be a number of these controls on the UserForm, so using : Label1.Caption = Textbox1.Text for each and every pair will be unreasonable.

    How can an array of Labels and Textboxes be set to handle this ?

    Thanks.

  2. #2
    VBAX Regular pike's Avatar
    Joined
    Dec 2007
    Location
    Alstonville, Australia
    Posts
    97
    Location
    Hi,
    Possibly with your code
    Class module named clsTextBox

    Option Explicit 
    Private WithEvents MyTextBox As MSForms.TextBox 
     
    Public Property Set Control(tb As MSForms.TextBox) 
    Set MyTextBox = tb 
    End Property 
     
    Private Sub MyTextBox_Change() 
        Call UserForm1.PersistentUpdate_ItemNumber(MyTextBox.Name) 
    End Sub
    and in the userform1 module
    Option Explicit 
    Dim tbCollection As Collection 
     
    Private Sub UserForm_Initialize() 
         
        Dim ctrl As MSForms.Control 
        Dim obj As clsTextBox 
         
        Set tbCollection = New Collection 
        For Each ctrl In Me.Controls 
            If TypeOf ctrl Is MSForms.TextBox Then 
                 
                Set obj = New clsTextBox 
                Set obj.Control = ctrl 
                tbCollection.Add obj 
            End If 
        Next ctrl 
        Set obj = Nothing 
         
    End Sub 
     
    Sub PersistentUpdate_ItemNumber(MyName As String) 
        Dim myIndex As Long 
        myIndex = Right(MyName, Len(MyName) - 7) 
        Me("Label" & myIndex).Caption = MyName
    End Sub

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    sub M_snb()
       for j=1 to 300
          me("Label" & j).caption=me("Textbox" &j)
       next
    End Sub

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    PIKE:

    Receiving error on
    Dim obj As clsTextBox
    "User Defined Type Not Defined"

    snb :

    Does your Sub replace one in PIKE's submission or is it an addition ?
    Where does it go ... how to call it.

  5. #5
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    Pike:

    Disregard my previous. Got it working. However, instead of the Label accepting data input into the Textbox as it's caption, it is using the Textbox name as it's caption.

    Whatever is entered into the textbox field should also become the associated Label Caption.

    ??

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Guessing, but try this


    Sub PersistentUpdate_ItemNumber(MyName As String) 
        Dim myIndex As Long 
        myIndex = Right(MyName, Len(MyName) - 7) 
        Me("Label" & myIndex).Caption = Me("Label" & myIndex).Text
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    Paul:

    You da man !

    Made a slight edit to the code :

    Sub PersistentUpdate_ItemNumber(MyName As String)
        Dim myIndex As Long
        myIndex = Right(MyName, Len(MyName) - 7)
        Me("Label" & myIndex).Caption = Me("Textbox" & myIndex).Text     '<--- From "Label" to "Textbox"
    End Sub
    Thanks to all for your help !

  8. #8
    VBAX Regular pike's Avatar
    Joined
    Dec 2007
    Location
    Alstonville, Australia
    Posts
    97
    Location
    Hello Logit,
    The syntax below is universal with the userform name also captured.
    Class Code Named clsFrmCtls
    Option Explicit
    Public mName
    Public nlabel
    Public mFrm As Object
    Public Event SelectedChange(objCtr, intCtr)
    Public WithEvents mTextBox As MSForms.TextBox
    Private Sub mTextBox_Change()
        nlabel = Replace(mName, "TextBox", "")
        RaiseEvent mFrm.SelectedChange(mName, nlabel)
    End Sub
    Userform Code

    Option Explicit
    Dim mcolEvents As Collection
    Public Sub SelectedChange(objCtr, intCtr)
        Me.Controls("Label" & intCtr).Caption = Me.Controls(objCtr).Text
    End Sub
    Private Sub UserForm_Initialize()
        Dim cCBEvents As clsFrmCtls
        Dim ctrl As MSForms.Control
        Set mcolEvents = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBox Then
                Set cCBEvents = New clsFrmCtls
                Set cCBEvents.mFrm = Me
                cCBEvents.mName = Me.Controls(ctrl.Name).Name
                Set cCBEvents.mTextBox = Me.Controls(ctrl.Name)
                mcolEvents.Add cCBEvents
            End If
        Next ctrl
    End Sub
    Private Sub butClose_Click()
        Unload Me
    End Sub
    Private Sub UserForm_Terminate()
        Set mcolEvents = Nothing
    End Sub
    Last edited by pike; 01-27-2017 at 01:21 AM.

  9. #9
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    Thanks friend !

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If using a class, the only code you need is:

    In the userform:

    Dim sn() As New c_check
    
    Private Sub UserForm_Initialize()
       ReDim sn(Controls.Count)
       
       For Each it In Controls
          If TypeName(it) = "TextBox" Then Set sn(j).cl_Text = it
          j = j + 1
       Next
    End Sub
    In the classmodule c_check

    Public WithEvents cl_Text As MSForms.TextBox
    
    Private Sub cl_Text_Change()
         cl_Text.Parent(Replace(cl_Text.Name, "TextBox", "Label")).Caption = cl_Text.Text
    End Sub

  11. #11
    VBAX Regular pike's Avatar
    Joined
    Dec 2007
    Location
    Alstonville, Australia
    Posts
    97
    Location
    good one snb
    using Parent to trap the userform

  12. #12
    pls infom in Excel VBA, how to change the present caption of checkbox mentioned inside the user form to a new dynamic manual caption when command button is clicked and transfer the updated value in the excel she

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •