l1075 said:
IM having trouble implementing the following code
Qty1 = txtQty1.Value
Qty2 = txtQty2.Value
...
...
Qty27 = txtQty27.Value
Qty28= txtQty28.Value
I have tried to use this code and it returns an error
For i = 1 To 28
Me.Controls("Qty" & i) = Me.Controls("txtQty" & i).Value
Next i
just wondering if there is a simplier way to do this
thanks
Simpler on what level? Being a lazy so-and-so, I'd want to do the
synchronization in one line e.g.
m_ControlPairs.SyncAll
Something that simple needs some work up front.
You don't say what your pairs (Qty1 and txtQty1, etc) are. I'd infer
they are both controls on a userform (i.e. of type MSForm.Control) and
Qty1's default property must be able to accept txtQty1's Value
property (say, a Label and a TextBox respectively).
So I'd create a class, CControlPair, as follows:
Option Explicit
Private m_CtrlPrimary As MSForms.Control
Private m_CtrlSecondary As MSForms.Control
Public Function Init( _
ByVal PrimaryControl As MSForms.Control, _
ByVal SecondaryControl As MSForms.Control _
) As Boolean
Dim v As Variant
Set m_CtrlPrimary = PrimaryControl
Set m_CtrlSecondary = SecondaryControl
On Error Resume Next
' Test CtrlSecondary has a Value property
v = PrimaryControl.Value
' Test CtrlSecondary has default property that
' can accept a value
v = SecondaryControl
Init = CBool(Err.Number = 0)
End Function
Public Function Sync() As Boolean
m_CtrlSecondary = m_CtrlPrimary.Value
End Function
Now in my form's code module I'd have to create, initialize and
persist many objects of type CControlPair and call all their
individual Sync methods. But because I'm lazy I'd make a collection
class, CControlPairs, which would look after all the individual
objects for me. All I'd then have to do is create, initialize and
persist the collection class object, pass it all the pairs and call
one SyncAll method. My CControlPairs class would look like this:
Option Explicit
Private m_colControlPairs As Collection
Public Property Get Item(ByVal Index As Variant) As CControlPair
Set Item = m_colControlPairs.Item(Index)
End Property
Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colControlPairs.[_NewEnum]
End Property
Friend Function Add( _
ByVal PrimaryControl As MSForms.Control, _
ByVal SecondaryControl As MSForms.Control _
) As CControlPair
Dim oControlPair As CControlPair
Set oControlPair = New CControlPair
m_colControlPairs.Add oControlPair, PrimaryControl.Name
oControlPair.Init PrimaryControl, SecondaryControl
Set Add = oControlPair
End Function
Friend Property Get Count() As Long
Count = m_colControlPairs.Count
End Property
Friend Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colControlPairs.Remove Index
Remove = CBool(Err.Number = 0)
On Error GoTo 0
End Function
Private Sub Class_Initialize()
Set m_colControlPairs = New Collection
End Sub
Private Sub Class_Terminate()
Set m_colControlPairs = Nothing
End Sub
Public Function SyncAll() As Boolean
Dim oControlPair As CControlPair
For Each oControlPair In m_colControlPairs
oControlPair.Sync
Next
End Function
The code in my form's code module would look something like this:
Option Explicit
Dim m_ControlPairs As CControlPairs
Private Sub UserForm_Activate()
Set m_ControlPairs = New CControlPairs
With m_ControlPairs
.Add TextBox1, Label1
.Add TextBox2, Label2
End With
End Sub
Private Sub btnSyncAll_Click()
m_ControlPairs.SyncAll
End Sub
But I'm too lazy to do any of that of course.
--
