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
