Multiple embedded textboxes with same code

[12473]

Hello.


I have 50+ embedded text boxes in my active sheet and I would like to apply a formula to all of the text boxes without having to rewrite the code for each textbox.


I am new to VBA and I have read about the possibility of using classes but I do not understand how I can make them work. I also did not enter my textboxes into a form which seems to be popular judging by the searches but I did not see a reason to use them. Should I have used a form? Any help with explaining how to write the code and explaining would be greatly appreciated.


Here are a few examples of the textbox names. Every text box starts with txt.


txtFirstYear011

txtName011

txtAddress011

txtRepeat011


Here is an example of a procedure that I would like to occur for every textbox in the active workbook whenever any of the textboxes gain focus.


Private Sub txtFirstYear011_GotFocus()

'Selects all data within the textbox when it gains focus.

txtFirstYear011.SelStart = 0

txtFirstYear011.SelLength = Len(txtFirstYear011.Text)

End Sub


Here is another sample code that I would like to apply to all textboxes.


Private Sub txtFirstYear011_LostFocus()

'Makes the entered number a percent.

txtFirstYear011.Value = Format(Val(txtFirstYear011.Value) / 100, "0.00%")

txtFirstYear011.SelStart = Len(txtFirstYear011.Value) - 1

End Sub


Thank you for your time.

 

[Luke M]

First, a link to UserForm tutorials:

http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html


I wasn't able to find any help on using a Class to control all text boxes, but you could have VB write all the code for you. Here's an example macro that writes your code.

[pre]

Sub CreateCode1()
Dim Code1String As String
Dim Code2String As String
For Each sh In Me.Shapes
'This is the line that controls which shapes to look at
If Left(sh.Name, 3) = "txt" Then
Code1String = Code1String & vbNewLine & vbNewLine & _
"Private Sub " & sh.Name & "_GotFocus()" & vbNewLine & _
"'Selects all data within the textbox when it gains focus." & vbNewLine & _
sh.Name & ".SelStart = 0" & vbNewLine & _
sh.Name & ".SelLength = Len(" & sh.Name & ".Text)" & vbNewLine & _
"End Sub"
Code2String = Code2String & vbNewLine & vbNewLine & _
"Private Sub " & sh.Name & "_LostFocus()" & vbNewLine & _
"'Makes the entered number a percent." & vbNewLine & _
sh.Name & ".Value = Format(Val(" & sh.Name & ".Value) / 100, ""0.00%"")" & vbNewLine & _
sh.Name & ".SelStart = Len(" & sh.Name & ".Value) - 1" & vbNewLine & _
"End Sub"

End If
Next sh

Debug.Print Code1String
Debug.Print Code2String

End Sub

[/pre]
Then you can just copy all the written code from the Immediate window and paste it into the module.

 

[12473]

Thank you for your help, Luke! Very cool.

 

[NARAYANK991]

Hi ,


Try out this file , though it seems difficult to work with , since it uses the Mouse Move event.


https://docs.google.com/open?id=0B0KMpuzr3MTVOUtTN3dUNk5iaWs


Narayan


Courtesy :


1. Stephen Bullen : http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2005-01/1418.html


2. Rick Rothstein : http://www.pcreview.co.uk/forums/excel-vba-using-same-code-apply-multiple-textboxes-userform-t4032037.html