Faster way to replicate UserForms with similar ControlSources

[Ace71425]

I definitely need a faster way to accomplish this so any help would be appreciated...for examples sake I have a userform with 50 textboxes and the control sources are A1-A50...Now I need 10 more of the exact same UserForms except the control boxes will be B1-B50, C1-C50 and so on and so forth. It's bad enough having to fill out the first UserForms control boxes let alone the next 9. So how can I replicate the UserForm but mass change the control source to a different letter with the same number? Any help would be so greatly appreciated

 

[Norie]

Wouldn't it be easier to use code to populate the textboxes and send the data from them back to the worksheet?

Then you wouldn't need multiple userforms.

 

[Ace71425]

Code would certainly be "faster" in terms of changing all the values...basically the userform is pulling from the excel sheet and this is to check employees time cards so say bills time card info for 2 weeks is located in a1-a50 and johns is located b1-b50 I want it to be in a floating userform so it can better be checked against deltek...so either multiple userforms or a button that changes the userforms data to the next persons info

 

[Ace71425]

But I can't think of another way to do it besides either control sources or

Textbox1 =
Textbox2 =
Textbox3 =

 

[mikerickson]

You could use a custom object (class module)
If you create a Class module and name it clsLinkedTextBox and put this code in it.

Option Explicit
' in class module clsLinkedTextBox

Public LinkedCell As Range
Public WithEvents LinkedSheet As Worksheet
Public WithEvents LinkedBox As MSForms.TextBox
Public EventsDisabled As Boolean
Event Change()

Sub LinkToCell(LinkCell As Range, Optional Parent As Object)
    If LinkedBox Is Nothing Then
        If Not Parent Is Nothing Then
            Set LinkedBox = Parent.Controls.Add("Forms.TextBox.1")
        End If
    End If
    Set LinkedCell = LinkCell.Cells(1, 1)
    Set LinkedSheet = LinkCell.Parent
    EventsDisabled = True
    Me.Text = LinkedCell.Text
    EventsDisabled = False
End Sub

Property Get Font() As NewFont
    Set Font = LinkedBox.Font
End Property
Property Get ForeColor() As OLE_COLOR
    ForeColor = LinkedBox.ForeColor
End Property
Property Let ForeColor(oleColor As OLE_COLOR)
    LinkedBox.ForeColor = oleColor
End Property
Property Get Height() As Single
    Height = LinkedBox.Height
End Property
Property Let Height(sngHeight As Single)
    LinkedBox.Height = sngHeight
End Property
Property Get Left() As Single
    Left = LinkedBox.Left
End Property
Property Let Left(sngLeft As Single)
    LinkedBox.Left = sngLeft
End Property
Property Get Name() As String
    Name = LinkedBox.Name
End Property
Property Get Top() As Single
    Top = LinkedBox.Top
End Property
Property Get Text() As String
    Text = LinkedBox.Text
End Property
Property Let Text(strText As String)
    LinkedBox.Text = strText
End Property
Property Let Top(sngTop As Single)
    LinkedBox.Top = sngTop
End Property
Property Get Width() As Single
    Width = LinkedBox.Width
End Property
Property Let Width(sngWidth As Single)
    LinkedBox.Width = Width
End Property

Function UFParent() As Object
    Set UFParent = Me.LinkedBox
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function

Private Sub LinkedBox_Change()
    If EventsDisabled Then Exit Sub
    EventsDisabled = True
    LinkedCell.Value = LinkedBox.Text
    Set UFParent.ActiveBox = Me
    RaiseEvent Change
    EventsDisabled = False
End Sub

Private Sub LinkedSheet_Change(ByVal Target As Range)
    If EventsDisabled Then Exit Sub
    EventsDisabled = True
    If Not Application.Intersect(Target, LinkedCell) Is Nothing Then
        With LinkedCell
        LinkedBox.Text = Application.Text(.Value, .NumberFormat)
        End With
    End If
    
    Set UFParent.ActiveBox = Me
    RaiseEvent Change
    EventsDisabled = False
End Sub

Private Sub Class_Terminate()
    Set LinkedBox = Nothing
    Set LinkedCell = Nothing
    Set LinkedSheet = Nothing
End Sub

And then put code like this in the user form's code module.

Option Explicit
' in userform's code module

Dim LInkedBoxes As Collection
Public WithEvents ActiveBox As clsLinkedTextBox

Private Sub ActiveBox_Change()
    With ActiveBox
        MsgBox .Name & ", linked to " & .LinkedCell.Address(, , , True) & ", has changed" & " to """ & .Text & """"
    End With
End Sub

Private Sub UserForm_Click()
    Sheet1.Range("a1").Value = "hellow"
    Call makeChange
End Sub

Private Sub UserForm_Initialize()
    
    Dim aLinkedBox As clsLinkedTextBox
    Dim RangeToLink As Range, oneCell As Range
    Dim nextTop As Single
    
    Set LInkedBoxes = New Collection
    Set RangeToLink = Range("A1:A10")
    nextTop = 5
    
    For Each oneCell In RangeToLink
        Set aLinkedBox = New clsLinkedTextBox
        With aLinkedBox
            .LinkToCell LinkCell:=oneCell, Parent:=Me
            .Height = 22: .Width = 75
            .Top = nextTop
            .Left = 5
            
            .Font.Size = 12
            .ForeColor = RGB(255, 0, 0)
            
            nextTop = nextTop + .Height + 4
        End With
        LInkedBoxes.Add Item:=aLinkedBox
    Next oneCell
    Set aLinkedBox = Nothing
End Sub

Private Sub UserForm_Terminate()
    Dim oneBox As clsLinkedTextBox
    For Each oneBox In LInkedBoxes
        Set oneBox = Nothing
    Next oneBox
    Set LInkedBoxes = Nothing
End Sub

You can created text boxes at runtime to link with each of your cells.
The properties in the class module are mostly there to pass properties from the text box to the class.
The events can be controlled either from the class module (as in Private Sub LinkedBox_Change() ) or in the user forms module via the ActiveBox's events. As written only the Change event is used, but other events could be added.

 

[Norie]

How about this?

A userform with a combobox that list employees and has 50 textboxes with their data.

User picks employee from combobox, code finds the relevant column for chosen employee and populates the textboxes with the data for the selected employee.

Or, a userform that pops up when the user double clicks an employee's 'column'.

This userform has code in the initialize event, or it could be in the worksheet BeforeDoubleClick event, that populates the textboxes with data from the column that's been clicked.