Hi! Roberto, Thanks for your replies. I need some time to fully understand the code and incorporate in my project. Regards Learner
On Oct 28, 2:20 am, r <robb....@gmail.com> wrote: > On 27 Ott, 14:57, learner <mahessbde...@gmail.com> wrote: > > > Hi! Ashish, > > > This does not work. I pasted the code after clicking the form in the > > empty sub with required changes of the names. But entering and exiting > > the TextBoxes + ComboBoxes do not execute the sub. > > create > 2 classes modules (the names are cCombo and cText) > 1 standard module > 1 Userform1 with same comboboxes and/or textboxes > > 'xxxxxxxxxxxxxxxxxxxxx > 'in class module cCombo > Option Explicit > Private WithEvents CC As MSForms.ComboBox > > Private Sub CC_Change() > If CC.ListIndex > -1 Then > CC.BackColor = &H80000013 > Else > CC.BackColor = &H80000005 > End If > End Sub > > Public Property Get Obj_cmb() As Control > Set Obj_cmb = CC > End Property > > Public Property Set Obj_cmb(ByVal vNewValue As Control) > Set CC = vNewValue > End Property > > 'xxxxxxxxxxxxxxxxxxxxx > 'in class module cText > > Option Explicit > Private WithEvents CC As MSForms.TextBox > > Private Sub CC_Change() > If Len(CC.Text) Then > CC.BackColor = &H80000013 > Else > CC.BackColor = &H80000005 > End If > End Sub > > Public Property Get Obj_cmb() As Control > Set Obj_cmb = CC > End Property > > Public Property Set Obj_cmb(ByVal vNewValue As Control) > Set CC = vNewValue > End Property > > 'xxxxxxxxxxxxxxxxxxxxx > 'in standard module > > Option Explicit > Public myArr1() As New cCombo > Public myArr2() As New cText > Sub show_userform() > UserForm1.Show > End Sub > > 'xxxxxxxxxxxxxxxxxxxxx > 'in classe module of the Userform > > Option Explicit > > Private Sub UserForm_Initialize() > Dim cnt As MSForms.Control > Dim i As Long, a As Long > Dim v > v = Array(1, 2, 3, 4, 5, 6) > For Each cnt In Me.Controls > If TypeName(cnt) = "ComboBox" Then > cnt.List = v > ReDim Preserve myArr1(i) > Set myArr1(i).Obj_cmb = cnt > i = i + 1 > ElseIf TypeName(cnt) = "TextBox" Then > ReDim Preserve myArr2(a) > Set myArr2(a).Obj_cmb = cnt > a = a + 1 > End If > Next > > End Sub > > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As > Integer) > Erase myArr1 > Erase myArr2 > End Sub > > regards > r -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
