can you give the attachment please On Wed, Oct 27, 2010 at 7:57 PM, 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. > > A little digging suggests that "Class Modules" may take care of all > controls for exit event in one sub only. > > I don't have any idea what is a class module. Can you please give some > idea? > > Thanks > Learner > > On Oct 27, 5:34 pm, Ashish Jain <ashishj...@openexcel.com> wrote: > > 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.comhttp:// > 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 > -- ---------------------------------------------------------------------------------- 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