Apply one code to multiple objects

**ABSTRAKTUS** · 12-14-2016, 10:17 AM

Hi all,

I have built a multipage userform which holds around 50 of different objects, such as textboxes, comboboxes etc. For every single object I have written 2 codes that format that object on entry and exit. How do I make these 2 codes universal for the uniform, so that they kick in when any of 50 objects get focus? It drives me mad to go through 100 codes and do changes!

[CODE][/Private Sub Title_Enter()
If Title.Text = "Title" Then
Title.Text = ""
Title.BackColor = RGB(255, 255, 153)
End If
End Sub
Private Sub Title_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(Title.Text) = "" Then
Title.Text = "Title"
Title.BackColor = RGB(255, 255, 255)
End If
End SubCODE]

I would be so much gratefull if someone could help me out with this issue!

Thanks in advance!

**ABSTRAKTUS** · 12-14-2016, 10:19 AM

Sorry, messed up with tags...

Private Sub Title_Enter()
If Title.Text = "Title" Then
Title.Text = ""
Title.BackColor = RGB(255, 255, 153)
End If
End Sub
Private Sub Title_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(Title.Text) = "" Then
Title.Text = "Title"
Title.BackColor = RGB(255, 255, 255)
End If
End Sub

**xlnitwit** · 12-14-2016, 10:41 AM

Hi,

Is the code exactly the same for each control? I assume the text check is comparing the text with the name of the control.

You might use an approach like this

Private Sub Title_Enter()
    EntryFormat Title
End Sub
Private Sub Title_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitFormat Title
End Sub
Sub EntryFormat(ctl As MSForms.Control)
    If LCase$(ctl.Text) = LCase$(ctl.Name) Then
        ctl.Text = ""
        ctl.BackColor = RGB(255, 255, 153)
    End If
    
End Sub
Sub ExitFormat(ctl As MSForms.Control)
    If Trim(ctl.Text) = "" Then
        ctl.Text = ctl.Name
        ctl.BackColor = RGB(255, 255, 255)
    End If

End Sub

It is possible to implement handlers for the Enter and Exit events with a class but it is not at all trivial.

**ABSTRAKTUS** · 12-14-2016, 10:49 AM

Hi xlnitwit and thanks for your response.

Code is very similar to every control, apart from control name textbox1, 2, 3 etc. Also, the text in each control is different. The text in a control is a replacement of a label and saving space on a userform. For example textbox1 holds text "Title" in it until textbox gets focus. When it gets focus text disappears and backcolor is changed to highlight the control.

I have been searching web for a solution, but gave up now, hence asking you guys for a help. Also, I'm a very beginner in VBA, so please bear with me...

**xlnitwit** · 12-14-2016, 10:52 AM

Is the text in the control always the same as the name of the control, or not?

**ABSTRAKTUS** · 12-14-2016, 10:55 AM

No, it is not.

**xlnitwit** · 12-14-2016, 11:11 AM

Then you must pass that as an additional argument

Private Sub Title_Enter()
    EntryFormat Title, "Title"
End Sub
Private Sub Title_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitFormat Title, "Title"
End Sub
Sub EntryFormat(ctl As MSForms.Control, default as string)
    If LCase$(ctl.Text) = LCase$(default) Then
        ctl.Text = ""
        ctl.BackColor = RGB(255, 255, 153)
    End If
    
End Sub
Sub ExitFormat(ctl As MSForms.Control, default as string)
    If Trim(ctl.Text) = "" Then
        ctl.Text = default
        ctl.BackColor = RGB(255, 255, 255)
    End If

End Sub

The enter and exit for each control will be like the first two subroutines above but passing a different control name and default text.

**ABSTRAKTUS** · 12-14-2016, 11:13 AM

Sorry mate, I don't really get it. Are you saying it cannot be done? Or what do I need to do?

**xlnitwit** · 12-14-2016, 11:35 AM

No I am saying that you need only repeat this part for each additional control

Private Sub Title_Enter()
    EntryFormat Title, "Title"
End Sub
Private Sub Title_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitFormat Title, "Title"
End Sub

You need to change the name of the control (Title) to match each control name, and change the default text ("Title") to whatever should be used for each control.

If you wish to adopt a leaner, class-based approach, you need something similar along the lines of post #3 here- http://www.mrexcel.com/forum/excel-q...ss-module.html

**ABSTRAKTUS** · 12-14-2016, 11:34 AM

Oh, sorry again. I think I got it now. I still need to copy them 2 codes 50 times, but it will be easier to maintain any changes, which can be done in a subroutines.

Isn't there a piece of code that would identify which object got focus and then apply them 2 codes?

**ABSTRAKTUS** · 12-14-2016, 11:42 AM

Thanks mate, definitely much easier now! You made my day, thanks.

Apply one code to multiple objects

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Re: Apply one code to multiple objects

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Apply VBA code to multiple cells

Wants to apply vba code to multiple cell

Apply VBA code to multiple sheets

Apply VBA code to multiple cells

[SOLVED] Can i modify this code to apply to multiple checkboxes and sheets?

[SOLVED] Match code to apply multiple cells in 1 Sub

Need to apply VBA code to multiple Worksheets

Bookmarks

Bookmarks

Posting Permissions