EXCEL: Help with VBA Loop of Active X TextBoxes, GotFocus, LostFocus

[IS NULL]

Hello!

First time posting here, this site has been very helpful for me while at work, however I am stuck on a bit of VBA code that I hope someone doesn't mind helping me with.
*Note* I'm VERY new to VBA, and really with coding in general. Have SQL experience but that's about it....so please keep this in mind!

Scenario:
I have 13 ActiveX Text Boxes in a Spreadsheet. When the value is empty, they display gray text (Textbox1_Lostfocus).. When a box is selected (Textbox1_GotFocus), the text value automatically clears. When a user types a Number or Value (depending on the particular text box), the text becomes black.

TextBox1 contains only numbers EX: "00000000". Text Boxes 2-4 contain "####", and text boxes 5-13 contain "YYYY-MM-DD".

Currently, I have the following for EACH Text Box:

Private Sub Textbox2_Lostfocus()


 If Me.TextBox1.Value = "" Then
  Me.TextBox1.Value = "####"
 End If
 
 If Me.TextBox1.Value = "####" Then
  Me.TextBox1.ForeColor = RGB(100, 100, 50)
 End If


End Sub


-------------------------------------------------


Private Sub Textbox2_Gotfocus()


 If Me.TextBox1.Value = "####" Then
  Me.TextBox1.Value = ""
 End If
 
 If Me.TextBox1.Value = "" Then
  Me.TextBox1.ForeColor = RGB(0, 0, 0)
 End If


End Sub

It works, but as you could imagine....it's not very efficient. I tried all day to get this piece of VBA code consolidated into a Loop of sorts (Once again, very new to all of this) based on the text values (EX: Text Boxes 2-4 contain "####), but couldn't figure it out.

Any help would be greatly appreciated! Also, please include the Subs if possible.

 

[IS NULL]

Sorry! Correction:

Private Sub Textbox2_Lostfocus()


 If Me.TextBox2.Value = "" Then
  Me.TextBox2.Value = "####"
 End If
 
 If Me.TextBox2.Value = "####" Then
  Me.TextBox2.ForeColor = RGB(100, 100, 50)
 End If


End Sub


-------------------------------------------------


Private Sub Textbox2_Gotfocus()


 If Me.TextBox1.Value = "####" Then
  Me.TextBox1.Value = ""
 End If
 
 If Me.TextBox1.Value = "" Then
  Me.TextBox1.ForeColor = RGB(0, 0, 0)
 End If


End Sub

 

[Norie]

Are you looking for something to take care of the events of all the textboxes instead of having to write code for each separate textbox?

 

[IS NULL]

Yes I believe that's exactly what I'm looking for. Right now I have the code snippet above (LostFocus, GotFocus) repeated for each text box, but would like to eliminate that if possible.

 

[Norie]

There is a method to control the events of multiple textboxes (or other controls) in one place.

Unfortunately the events that can be controlled using that method are kind of limited and don't include GotFocus/LostFocus, or even Enter/Exit.

 

[IS NULL]

Darn, well that explains some of it then. I did originally just have Me.TextBox1.Value = "####" but found it inconvenient to manually clear the box prior to typing. Oh well. Thanks for looking into it Norie!

 

[Norie]

You could set up a couple of subs like this and call them from the GotFocus/LostFocus events, passing the appropriate textbox as required.

Sub txt_Lostfocus(txtbox As Object)

    If txtbox.Value = "" Then
        txtbox.Value = "####"
    End If

    If txtbox.Value = "####" Then
        txtbox.ForeColor = RGB(100, 100, 50)
    End If

End Sub

Sub txt_Gotfocus(txtbox As Object)

    If txtbox.Value = "####" Then
        txtbox.Value = ""
    End If

    If txtbox.Value = "" Then
        txtbox.ForeColor = RGB(0, 0, 0)
    End If

End Sub

For example.

Private Sub Textbox2_Lostfocus()

    txt_LostFocus Me.TextBox2

End Sub


-------------------------------------------------


Private Sub Textbox2_Gotfocus()

    txt_Gotfocus Me.Textbox1

End Sub

That's not ideal but it will cut down on the code a fair bit.

 

[IS NULL]

Excellent, will make it much easier on the eyes. Thanks!