Hi Paul & Learner
Sorry that's for VSTO guys - just got confused:
Try this:
Private Sub UserForm_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
With ctrl
If Trim(.Text) <> "" Then
.BackColor = vbRed
End If
End With
End If
If TypeName(ctrl) = "ComboBox" Then
With ctrl
If Trim(.Text) <> "" Then
.BackColor = vbYellow
End If
End With
End If
Next ctrl
End Sub
Regards
Ashish Jain
McKinsey India Knowledge Center
(Microsoft Certified Application Specialist)
(Microsoft Certified Professional)
http://www.excelitems.com
http://www.openexcel.com
___________________________________
On Oct 27, 4:57 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> Interesting...
> I've not come across this before.
> I tried to pop it into one of my userforms, but it doesn't like the "Handles"
> part
> after the )... it expects the end of the statement.
>
> Is there some Reference I need to include?
>
> Paul
>
>
>
>
>
>
>
> ----- Original Message ----
> > From: Ashish Jain <ashishj...@openexcel.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Wed, October 27, 2010 7:34:35 AM
> > Subject: $$Excel-Macros$$ Re: Manipulating Userform controls' back color by
> > one
> >routine instead of separate change routines
>
> > Hello Mr. Learner,
>
> > There are 2 ways to resolve your issue:
>
> > i. Sharing Event Handlers
> > Private Sub TextBoxes_TextChanged(ByVal sender As System.Object, _
> > ByVal e As System.EventArgs) Handles TextBox1.TextChanged, _
> > TextBox2.TextChanged, TextBox3.TextChanged
> > TextBox1.BackColor = RGB(255, 150, 200)
> > TextBox2.BackColor = RGB(255, 150, 200)
> > TextBox3.BackColor = RGB(255, 150, 200)
> > End Sub
>
> > ii. Iterating Through Controls
> > Private Sub ChangeBackColor(ByVal container As Control)
> > Dim ctrl As Control
> > For Each ctrl In container.Controls
> > If TypeOf (ctrl) Is TextBox Then
> > ctrl.BackColor = ""
> > End If
> > If ctrl.HasChildren Then
> > ClearText(ctrl)
> > End If
> > Next
> > End Sub
>
> > Regards
> > Ashish Jain
> > McKinsey India Knowledge Center
> > (Microsoft Certified Application Specialist)
> > (Microsoft Certified Professional)
> >http://www.excelitems.com
> >http://www.openexcel.com
>
> > On Oct 27, 3:00 pm, learner <mahessbde...@gmail.com> wrote:
> > > Hi! All Members,
>
> > > I am a new member seeking guidance on a problem I can't solve.
>
> > > A Userform having 96 Controls (TextBoxes + ComboBoxes) captures data
> > > to create a worksheet. I need to change back color of each control as
> > > data is entered by the user to indicate that the control is already
> > > visited. Instead of writing 96 change routines, I am sure there must
> > > be a simple solution.
>
> > > Please advise how to manage it by only one routine.
>
> > > Thanks
>
> > > Learner
>
> > --
> >--------------------------------------------------------------------------
> >--------
> >-
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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&;...
--
----------------------------------------------------------------------------------
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
