How to Correctly Name Textboxes

[Zephid15]

Ok fumei keeps telling me that the way i am using to name my textboxes is incorrect. I have a grid of text boxes: txt1a, txt1b,txt1c across the top and then down:txt2a, txt3a ext... It makes sense to me but is not the correct way to do this. What is the correct way to name ANYTHING, not just textboxes?

[fumei]

Well...not exactly. My point was that txt1a does not say anything.

Sorry if I came across hypercritical.

Naming object is a sensitive subject for some. My viewpoint is that control names are best when they say something about the control.

"1a", "1b" MAY be quite valid for you. I don't know. I should not have jumped on it.

I have used textbox names like: txtQ1, txtQ2.

This was a shorter version of: txtQuestion1, txtQuestion2.

The textboxes were for responses to a questions that were Labels - lblQ1. So I know txtQ1 means textbox for Question 1.

So...maybe "1a" means something for you. You had a txtdis (or something like that). Shrug, maybe that is meaningful for you.

My point is that a name should immediately indicate, or give good indication of what it is for.

I know it is a bit of a pain, but once you get into the habit it does, in the long run, make reading (and more importantly RE-reading months later) easier.

For example, I may have only one commandbutton on a usrform. However, I NEVER leave it as CommandButton1. I always change it to, cmdExit, or cmdDone or whatever. It is habit. If there is only one, yes yes, it does not make much difference.

BUT - once you have a very complex userform it is very very handy to have explicit names.

I had a userform with a MultiPage. The MultiPage had seven tabs (pages). EACH tab (Page) had three commandbuttons. That is 21 commandbuttons.

During the course of development some commandbuttons were deleted, then I decided to put them back. Remember the numbering is by the order they are created.

I ended up with....

Page 1: CommandButton1, CommandButton2, Commandbutton15

Page 2: CommandButton3, CommandButton6, Commandbutton16

Page 3: CommandButton5, CommandButton7, Commandbutton18

etc. etc.

NOT very helpful. In the dropdown list (left dropdown on a code module) there is the list of all the controls on your userform.

Commandbutton1
Commandbutton2
Commandbutton3
Commandbutton4
Commandbutton5
Commandbutton6

told me NOTHING.

cmdMP_NetworkOK (multipage, NetworkPage, OK)
cmdMP_NetworkCancel (multipage, NetworkPage, cancel)
cmdMP_InstallOK (multipage, InstallPage, OK)
cmdMP_InstallCancel (multipage, NetworkPage, cancel)
cmdMainFormOK (main form, OK)
cmdMainFormCancel (mainform, cancel)

does.

txtUnixYears
txtUnixSkillLevel

yadda yadda.

Sorry. I hope I did not put you off. Use whatever names you like. I was just trying to encourage you to get into a habit of being really really clear. For yourself mostly. However, if you ever need/require/want to pass code to someone else, they will be grateful for well named objects.

[fumei]

Just a further note.

I also often tie in userform textboxes with bookmarks in a document. I match the names.

txtClientName - textbox on userform
txtClientAddress - textbox on userform
txtClientPhone - textbox on userform
txtClientCity - textbox on userform

ClientName - bookmark in document
ClientAddress - bookmark in document
ClientPhone - bookmark in document
ClientCity - bookmark in document

Dim oCtl As Control
Dim strName As String
For Each oCtl In Me.Controls
If TypeOf oCtl Is MS.Forms "Textbox" Then
strName = Right(oCtl.Name, Len(oCtl.Name) - 3)
' eg. txtClientName becomes ClientName
ActiveDocument.FormFields(strName).Result = _
oCtl.Text
End If
Next

Badda bing, badda boom. The values from the userform are dumped into the document cleanly. This ONLY works with carefully assigned names. Properly designed names make things easier.

Of course if you have other textboxes on the userform, one would also do some error trapping - checking that there IS a matching bookmark name.

But hopefully you get my drift.

[Zephid15]

fumei, don’t get me wrong I want not complaining at all. I was just asking your advice, you have mentioned my naming multiple times in the past so i thought i would ask how to appropriately name them.

Also, in terms of proper programming edict, what other tidbits of information could you suggest (other then commenting, I know you can never have enough of that).

[Zephid15]

By the way, thank you for the lesson on naming.

[fumei]

IMO the basic housekeeping/coding tenets are:

1. using Option Explicit to force declaration of variables;

2. using a consistent naming standard for variables;

3. using clear understandable names for objects;

4. breaking code into manageable/re-usable chunks (procedures); number is debatable, but if your code is longer than 60 lines...it can likely be broken up;

5. putting repeated actions into UDF's (User Defined Functions);

6. consistent clean indenting - see Malcolm's signature line for a link to SmartIndents, if you want assistance with this;

7. liberal use of objects and object collections;

8. REALLY work at grasping basic logic tools. In particular: Select Case and For Each. Often these are much more efficient (especially when using objects) than multiple If statements.

[Zephid15]

Yea, I have SmartIndent, it is a really useful program. But, I have questions about number 5. I have a good amount of repeated code that I know can break down but I don’t know how to. it is the same thing repeated with simply a number or variable changed. How do I make a UDF?

[fumei]

Can you give an example?

Here one example from me. Getting a properly captialized first name of the person logged on. I will use myself to demonstrate.

My logon name is: gerry.knight

Function FirstName() As String
Dim myLogonName As String
Dim myFirstName As String
myLogonName = Environ("UserName")
myFirstName = Left(myLogonName, (InStr(myLogonName, ".") - 1))
FirstName = UCase(Left(myFirstName, 1)) & Right(myFirstName, _
(Len(myFirstName) - 1))
End FunctionThis UDF gets the full logon name (gerry.knight), extracts the first name (gerry), then properly capitalizes the first letter to get Gerry.

Now whenever I want to use the logged on person's first name, I use the function.
Sub ShowFirstName()
MsgBox "Hi " & FirstName & "!"
End Sub
This of course could be used in many places, a polite error message for example, "Hello Charles. It appears your input for Client Name is blank. Please input a Client name."
Function DocFullName() As String
' gets activedocument path and filename
DocFullName = ActiveDocument.Path & Application.PathSeparator & _
ActiveDocument.Name
End FunctionWith this when ever, where ever, you want to use the full path and name of a document, you just use DocFullName.

The possibilities are vast. So it not just a repetition of instructions - eg. For loops - but repetition of use.

Need to get a paragraph count regularly? Make a function.

Need to know if a document has tables? Make a function.

Globals used functions (ie. can be used in many different documents, for exampe the logon name function) should be stiored in a global template. But not normal.dot.

Normal.dot is a global, 'tis true. However, it is a notoriously easily corrupted file. Even Microsoft does not recommend stioring code there.

Make a global template file that holds your global code. Loaded at Startup all of its code will be available anywhere.

[Zephid15]

Example:

This code recalculates the values when the user changes a quantity or discount value.
(please ignore the poor naming. I have not fixed that yet)

Sub ReCalc()
If txt1q = "" Then
txt1q = 0
End If
If txt1q = "0" And CheckBox1a.Value = True Then
txt1q = 1
End If
If txt1d.Value = "" Then
OpenDatabase
GetProductData
txt1a = PROData(1, 2)
txt1b = PROData(1, 3)
txt1c = PROData(1, 4)
txt1d = PROData(1, 6)
End If
If txt1dis.Value = "" Then
txt1dis.Value = "0"
End If
If txt1dis.Value = "0" Then
txt1e.Value = txt1q.Value * txt1d.Value
Else
txt1e.Value = (txt1q.Value) * (txt1d.Value) -_
(txt1d.Value * (txt1dis.Value * ".01"))
End If
Dim SubTotal
SubTotal = Val(txt1e.Text)
txttotal = FormatCurrency(SubTotal)
End Sub

This is for row one. When I am finished with this project it will have a good 80 rows. I do not want to repete this code 80 times. What could i do with this code to make it universal for all of the rows.

[fumei]

1. Please use the underscore character in your code. NOT using it stretches the code window and makes it a pain for those of us with lower screen resolutions. We have to scroll left/right, as well as up/down.

Eg.txt1e.Value = (txt1q.Value) * (txt1d.Value) - _
(txt1d.Value * (txt1dis.Value * ".01"))

Please edit your post and correct this. It would be appreciated.

2. You need to get into the habit of giving clear descriptions.

Rows? 80 rows? Is this for Excel? For Word? If Word, an 80 row table seems excessive, and points more to using Excel.

Are they rows on a userform? I am very confused by this. WHAT rows?

I can not give a good answer because I can not picture what is going on.

These, or so I thought, were textboxes (txt1q etc.), so again...WHAT rows? You are going to have 80 rows of textboxes?????

I hope not!

3. I don't know why you declare variable. Dim SubTotal
SubTotal = Val(txt1e.Text)
txttotal = FormatCurrency(SubTotal)

If txttotal is a textbox (I think it is), then you can just do:txttotal = FormatCurrency(Val(txt1e.Text)) There is nothing wrong with using a variable. However, it is not needed.

4. I would like you to think about your logic flow. If txt1dis.Value = "" Then
txt1dis.Value = "0"
End If
If txt1dis.Value = "0" Then
txt1e.Value = txt1q.Value * txt1d.Value
Else
txt1e.Value = (txt1q.Value) * (txt1d.Value) - _
(txt1d.Value * (txt1dis.Value * ".01"))
End IfThe first IF checks the value, and if it is "" makes that value 0. It then checks the value again, and if it is 0, does stuff. if it is not 0, does other stuff.

What is the actual logic?

If value is "", make it 0 AND do stuff.
If it is not "", do other stuff.

Here is the same result, performing instructions ON the value. Not checking IF it is something or not.Select Case txt1dis.Value
Case ""
txt1dis.Value = "0"
txt1e.Value = txt1q.Value * txt1d.Value
Case Else
txt1e.Value = (txt1q.Value) * (txt1d.Value) - _
(txt1d.Value * (txt1dis.Value * ".01"))
End Select

IF statements perform instructions based on True or False results. That is the only thing they do. Select Case on the other hand performs instructions based on the actual value...not if it is ONE thing (true or false).

Explain your "rows", and I may be able to help.

[Zephid15]

1. Done
2. I'm afraid it is I just counted them and its look around 85. Yes it is Word on a form and no I can?t use excel because the finished product needs to be a nice looking Word quotation. Please allow me to explain what I am doing and what i have completed so far.

At work we use a program called Goldmine. It?s horrible but that?s beside the point. Goldmine is a database that holds all of our customers names accounts and products that they own. Whenever a customer contacts us and asks for a quote we need to give send them an e-mail with an attached PDF word document. I am writing a program that when I or one of my coworkers opens the template QuickQuote a userform pops up.

5704


From that userform they see the first tab where customer information has automatically been put in from an access database(see Main Pic). Then select the sales manager and the move to the next tab across the top. All of the following tabs have our main product groups and under each tab are all of the corresponding products, prices, and descriptions in the textboxes. The data is pulled from the access database. The user selects the checkbox next to the products that they want to include in the quote and if they want to adjust the price or put in a discount they can. I wanted all of the fields to me completely editable because it just going to be my co-workers and my self are using the program. We commonly need to make a price $0 or add RS to the end of a product number.

Once the user selects all of the products that then click the Continue button the program sees the number of items selected and creates a small table on a word document. In each table the values from the form are pulled along with a corresponding picture from the database. Now this is automatically saved as a PDF in a directory where the user can easily find it.

See next post for second pic

[Zephid15]

deleted

[Zephid15]

Picture Two

5705

[Zephid15]

There has to be a better way of doing this and i am running into many issues. Although, i do not want to start over because i have done so much and spent a lot of time creating what i have.

[fumei]

There IS a better way.

Do it in Excel. THEN pull whatever you need into a "nice looking Word quotation".

However, OK, maybe you are stuck with what you have, but....shudder....

The problem is, on the userform you do NOT, repeat NOT, have "rows". It may look like rows of textboxes, but they are not. I will repeat that. They are not rows.

They are individual, separate, controls.

Let me see if I understand correctly.

You have this multipage with a bunch of Tabs.

Each tab has "rows" of textboxes.

Eg. Part Number, Part Name, Description, Unit Price, Discount (that is the txtdis I assume) and Price.

Shudder. Again...this is a job for Excel. Word is not a spreadsheet.

But anyway....I am going to write and try and think at the same time...hmmmm.

Textboxes for "Row 1"
QC10PNumR1
QC10PNameR1
QC10DescrptR1
QC10UnitPriceR1
QC10DiscountR1
QC10PriceR1

For "Row 2"
QC10PNumR2
QC10PNameR2
QC10DescrpR2
QC10UnitPriceR2
QC10DiscountR2
QC10PriceR2

For "Row 3"
QC10PNumR3
QC10PNameR3
QC10DescrpR3
QC10UnitPriceR3
QC10DiscountR3
QC10PriceR3

I don't think you can get away with not hard coding the number of "rows", but, if you DID name the textboxes like above you could:

Sub Wow()
Dim var
Dim RowNumbers As Long
RowNumbers = 45

For var = 1 To RowNumbers

Select Case Me.Controls("QC10DiscountR" & var).Value
Case ""
Me.Controls("QC10DiscountR" & var).Value = "0"
Me.Controls("QC10PriceR" & var).Value = txt1q.Value * _
Me.Controls("QC10UnitPriceR" & var).Value
Case Else
txt1e.Value = (txt1q.Value) * (txt1d.Value) - _
(txt1d.Value * _
(Me.Controls("QC10DiscountR" & var).Value * ".01"))
End Select

Dim SubTotal
SubTotal = Val(txt1e.Text)
txttotal = FormatCurrency(SubTotal)
End Sub

Do you see my issue with names?????

I would have liked to rename the txt1q, and txt1d in the above code...but I am not 100% WHAT THEY ARE!
txt1q looks like it should be a quantity number(unit price * quantity), but I can't see where that is.

I hope you are following this.

You have 45 "rows" (or whatever).

If the textboxes are ALL properly named, you can loop through them by incrementing the last character.

var starts at 1, so

Me.Controls("QC10UnitPriceR" & var).Value will give the value of: QC10UnitPriceR1

Me.Controls("QC10DiscountR" & var).Value will give the Value of: QC10DiscountR1

And does your calculations.

The next iteration of var is 2, so......

Me.Controls("QC10UnitPriceR" & var).Value will give the value of: QC10UnitPriceR2

Me.Controls("QC10DiscountR" & var).Value will give the Value of: QC10DiscountR2

And does your calculations.

The next iteration of var is 3, so......

Going through all your "rows".

One chunk of code to do all your "rows". It will ONLY work if you have ALL the appropriate textboxes named correctly. In effect, you are numbering the "rows". The textboxes all use the same names (by type - UnitPrice, PName etc.) but with the "row" number as a suffix.

So the code does the processing but increments that last suffix by a variable.

45 "rows", it will do 45 processing instructions, changing the name of the appropriate control (textbox) by that last number.

UGLY!!!!!!!

Word is not a spreadsheet. Mind you, technically, this is not really Word. It is VBA doing it.

The point is, it CAN be done, but only if you name things correctly ...all (what? 80 rows * 6 textboxes) 480 of them.

UGLY.

OK. OK. Someone is going to come along and point out that you could do itwith your existing names.

Dim RowNumbers As Long
RowNumbers = 45

For var = 1 To RowNumbers
Select Case Me.Controls("txt" & var & "dis").Value
Case ""
Me.Controls("txt" & var & "dis").Value = "0"
Me.Controls("txt" & var & "e").Value = _
Me.Controls("txt" & var & "q").Value * _
Me.Controls("txt" & var & "d").Value
Case Else
Me.Controls("txt" & var & "e").Value = _
(Me.Controls("txt" & var & "q").Value) * _
(Me.Controls("txt" & var & "d").Value) - _
(Me.Controls("txt" & var & "d").Value * _
(Me.Controls("txt" & var & "dis").Value * ".01"))
End Select

So incrementing var from 1 to 45 (or whatever) becomes

Me.Controls("txt" & var & "d") or txt1d
Me.Controls("txt" & var & "d") or txt2d
Me.Controls("txt" & var & "d") or txt3d

etc. etc.

Are you following this?

[fumei]

OK, I finally figured out the q" in txt1q...THAT is the quantity value. Doh. Again...naming. txt1Quantity perhaps?

Here is an alternative to getting the textboxes using control objects. But still using your bleech names.Sub ReCalc()
Dim oTxtq As Control
Dim oTxtdis As Control
Dim oTxte As Control
Dim oTxtd As Control
Dim var
Dim RowNumbers As Long ' still hard coded
' again making up as the number
RowNumbers = 45
For var = 1 to RowNumbers
Set oTxtq = Me.Controls("txt" & var & "q")
Set oTxtdis = Me.Controls("txt" & var & "dis")
Set oTxtd = Me.Controls("txt" & var & "d")
Set oTxte = Me.Controls("txt" & var & "e")
Select Case oTxtdis.Value
Case ""
oTxtdis.Value = "0"
oTxte.Value = oTxtq.Value * oTxtd.Value
Case Else
oTxte.Value = (oTxtq.Value) * (oTxtd.Value) - _
(oTxtd.Value * (oTxtdis.Value * ".01"))
End SelectAgain, the incrementation of var Sets a different textbox.

var = 1
Set oTxte = Me.Controls("txt" & var & "e") txt1e

var = 2
Set oTxte = Me.Controls("txt" & var & "e") txt2e

var = 3
Set oTxte = Me.Controls("txt" & var & "e") txt3e

And so on.

So if your "rows" are named:

txt1e
txt2e
txt3e

etc. The code will loop through them correctly..

[Zephid15]

Wow, thank you for the through response. Give me a minute to read and digest this all.