Global StrConv function?

[SOS]

Hi folks,

I have built a userform and for testing purposes had pre-populated all the textboxes and comboboxes so I could F8 through the code. All's well at this point.
But, when this userform is given out I realise that not everybody is totally accurate about capitalising words when filling in forms and I now realise that all the way through my code I now have lines like



With Selection
.TypeText tbxForename & " " & tbxSurname
.TypeParagraph
.TypeText comStaffname
End With


and it's going to be an absolute pain to go through manually and change all these lines to


With Selection
.TypeText StrConv(tbxForename, vbProperCase) & " " & _
StrConv(tbxSurname, vbProperCase)
End With


etc etc.

My question is:

Is there a way of putting the StrConv at the top of the module to deal with all the instances needed in the code?

Any help appreciated

Regards

Seamus

[SOS]

Worked it out.

I've added the following code:



Dim ctl as control
For Each ctl In frmMain.Controls
If ctl.Visible = True Then
If ctl.Enabled = True Then
If Left(ctl.Name, 3) = "tbx" Then
ctl.Value = StrConv(ctl.Value, vbProperCase)
End If
End If
End If
Next ctl
For Each ctl In frmMain.Controls
If ctl.Visible = True Then
If ctl.Enabled = True Then
If Left(ctl.Name, 3) = "com" Then
ctl.Value = StrConv(ctl.Value, vbProperCase)
End If
End If
End If
Next ctl



and it seems to have taken care of it.

One of the things it can't really cope with is names like McDonald or MacDonald - they come out like Mcdonald and Macdonald - those names may just have to edited manually at the end of the process. Unless, that is, anyone has any ideas on coding for that issue.

Thanks

Seamus

[clhare]

I am in the same situation. Where exactly did you put the new code?

[SOS]

clhare,

It's in the sub attached to the OK button for the UserForm (in my case called frmMain) and I have placed it near the top.

Hope this helps

Seamus

[clhare]

Never mind-- I figured it out and it works GREAT!!! Thanks!

[SOS]

You are welcome. I've certainly had my share of help on this forum and it's always good to be able to help someone else.

Regards

Seamus

[fumei]

I would still like to know why you use .Value instead of .Text.

The other thing I would like to point out is that technically you are changing the control Value itself, NOT what you are putting in the document. The result is the same true. However, as you note, there is a problem with McDonald.


Function Prop(strIn As String)
Prop = StrConv(Left(strIn, 1), 3) & _
Right(strIn, Len(strIn) - 1)
End Function

This function make sure the first letter is capitalized, but leaves everthing else alone.

McDonald will come out.....McDonald.


For Each ctl In frmMain.Controls
If ctl.Visible = True Then
If ctl.Enabled = True Then
If Left(ctl.Name, 3) = "tbx" Then
ctl.Value = Prop(ctl.Value)
End If
End If
End If
Next ctl

[fumei]

I still dislike using Selection, but, depending on what you are doing, why not pass in your values as parameters?


Sub DoSelectionCrap(ForeName As String, _
SurName As String, _
StaffName As String)
ForeName = Prop(Forename) & " "
SurName = Prop(SurName) & vbCrLf
StaffName = Prop(Staffname)
Selection.TypeText Text:= Forename & Surname & Staffname
End Sub

Sub SendSelectionCrap()
DoSelectionCrap(txtForename, txtSurname, cboStaffname)
End Sub


Sorry, I used standard prefixes: "txt" instead of "tbx"

[clhare]

I used the function you provided, but I get an error. It says invalid argument and it looks like strIn is empty. What am I doing wrong? I don't see "strIn" in the second part of the code, but I'm not sure where I would put it if it is supposed to be in there somewhere.




Function Prop(strIn As String)
Prop = StrConv(Left(strIn, 1), 3) & _
Right(strIn, Len(strIn) - 1)
End Function

This function make sure the first letter is capitalized, but leaves everthing else alone.

McDonald will come out.....McDonald.

For Each ctl In frmMain.Controls
If ctl.Visible = True Then
If ctl.Enabled = True Then
If Left(ctl.Name, 3) = "tbx" Then
ctl.Value = Prop(ctl.Value)
End If
End If
End If
Next ctl

[fumei]

Two different things.

The function Prop would take StrIn as a parameter.

Msgbox Prop(txtWhatever)

would display a message box with the value of txtWhatever with the first letter capitalized.

The second chunk of code is a response to the looping through of controls. In which case, strIn - a parameter - is ctl.Value.

However, it should properly be ctl.Text.

[clhare]

Ok, I used the following code, but when I go through the code in Break mode, strIn = "" and I still get "Invalid procedure call or argument".

Dim ctl As Control
For Each ctl In frmInitialInfo.Controls
If ctl.Visible = True Then
If ctl.Enabled = True Then
If Left(ctl.Name, 3) = "txt" Then
ctl.Text = Prop(ctl.Text)
End If
End If
End If
Next ctl

Function Prop(strIn As String)
Prop = StrConv(Left(strIn, 1), 3) & _
Right(strIn, Len(strIn) - 1)
End Function

[fumei]

I have no idea why. Simple demo doc attached. You can fire the userform by clicking the "Cap It!" button on the menubar.

Clicking the Cap It button capitalizes the first letter of textbox contents, and leaves rest alone.

It works. Other than changing the name of the userform (to just Me), the code is exactly the same as above.

[SOS]

deleted for duplicate post

[SOS]

clhare,

You have to put something into all the textboxes for it to work. If you only fill in 1 or 2 of them you get the error message.

I was about to post the same error as you but then tried it again this time filling in all 3 boxes

Regards

Seamus

[lucas]

I think you could check for empty textboxes easily enough...something like:


if textbox1 <> "" then

[fumei]

Ummm, yeeesssss. See? There I go also making assumptions. Yes, I assumed text would be entered into all the textboxes.

Actually, for a good look at error trapping empty values see the attached files in Seamus' thread on two variables. It is not perfect, but it is good.

Why not perfect?

Because it only gives back error messages one at a time. If there are five missing data chunks the error trapping routine executes five separate times. The user gets five separate error messages.

Long time programmers know that perfect error trapping (if possible at all) sometimes takes up to 50% of ALL time spent on a project.

As a suggestion for increasing the user view, within the error trapping routine you make an array of all offending controls. Pseudocode:


For each control in Me.Controls
If control is visible (so it is supposed to be used)
If control = "" (and it is supposed to have something)
Redim Preserve ErrMessages(j)
ErrMessages(j) = control name

Now you have a listing of controls that should NOT be "".

To continue, you can go a couple of ways. First, translate the control name to an user understandable name.

Eg: txtSurName = "surname"

Do this with a Function, say MakeSense.


Dim msg As String
for var = 0 To Ubound(ErrMessages)
msg = msg & "The field " & _
MakeSense(ErrMessages(var)) & _
"is empty. This is a required field."
next
Msgbox msg

Now the users gets a full list of ALL empty textboxes.

Yadda yadda yadda. In mine I have specific error messages for each control and build the message linking the error text for a control.

Are we having fun yet??????

[SOS]

Excellent reply fumei - wow do I have food for thought?

Seamus

[clhare]

Ok, now I'm lost. I understand where you're going, but not how to get there!


Ummm, yeeesssss. See? There I go also making assumptions. Yes, I assumed text would be entered into all the textboxes.

Actually, for a good look at error trapping empty values see the attached files in Seamus' thread on two variables. It is not perfect, but it is good.

Why not perfect?

Because it only gives back error messages one at a time. If there are five missing data chunks the error trapping routine executes five separate times. The user gets five separate error messages.

Long time programmers know that perfect error trapping (if possible at all) sometimes takes up to 50% of ALL time spent on a project.

As a suggestion for increasing the user view, within the error trapping routine you make an array of all offending controls. Pseudocode:


For each control in Me.Controls
If control is visible (so it is supposed to be used)
If control = "" (and it is supposed to have something)
Redim Preserve ErrMessages(j)
ErrMessages(j) = control name

Now you have a listing of controls that should NOT be "".

To continue, you can go a couple of ways. First, translate the control name to an user understandable name.

Eg: txtSurName = "surname"

Do this with a Function, say MakeSense.


Dim msg As String
for var = 0 To Ubound(ErrMessages)
msg = msg & "The field " & _
MakeSense(ErrMessages(var)) & _
"is empty. This is a required field."
next
Msgbox msg
Now the users gets a full list of ALL empty textboxes.

Yadda yadda yadda. In mine I have specific error messages for each control and build the message linking the error text for a control.

Are we having fun yet??????

Can you post a sample file? I want to make sure all textboxes and listboxes have a selection made, as well as a selection between 2 option buttons.

[fumei]

Attached is a VERY simple demo. It only checks for textboxes with "". Click the "Show My Form" icon on the menu bar.

However, it should demonstrate the principle.

[clhare]

Wow! This is cool! I was able to add my list boxes and option buttons into the code. The only problem I had was with the option buttons. For example, I had a Yes and No button. If Yes is selected, the No shows up on the error list as empty.

I can just switch them to a listbox on my userform, but is it possible to test them and just have just one entry in the message box when none of the option buttons have been selected? I couldn't figure out how to do that.

[clhare]

How can I control the order of the items in the message? I had assumed they would appear in the order I listed them on the form and in the code, but that doesn't happen. I have the following:

ControlNames = Array("Addressee's First Name", "Addressee's Surname", _
"Addressee's Address", "Letter Date", "Salutation", "Employee's First Name", _
"Employee's Surname", "Employer 1", "Employer 2", "Most Recent Position", _
"Start Date", "End Date", "Previous Position?", "Previous Position", _
"Previous Start Date", "Previous End Date")

Select Case strIn
Case "txtAddresseeFirstName"
DefineControlName = ControlNames(0)
Case "txtAddresseeSurname"
DefineControlName = ControlNames(1)
Case "txtAddress1"
DefineControlName = ControlNames(2)
Case "txtLtrDate"
DefineControlName = ControlNames(3)
Case "txtSalutation"
DefineControlName = ControlNames(4)
Case "txtEmployeeFirstName"
DefineControlName = ControlNames(5)
Case "txtEmployeeSurname"
DefineControlName = ControlNames(6)
Case "lstEmployer1"
DefineControlName = ControlNames(7)
Case "lstEmployer2"
DefineControlName = ControlNames(8)
Case "lstMostRecentPosition"
DefineControlName = ControlNames(9)
Case "txtStartDate"
DefineControlName = ControlNames(10)
Case "txtEndDate"
DefineControlName = ControlNames(11)
Case "lstPrevious"
DefineControlName = ControlNames(12)
Case "lstPreviousPosition"
DefineControlName = ControlNames(13)
Case "txtPreviousStartDate"
DefineControlName = ControlNames(14)
Case "txtPreviousEndDate"
DefineControlName = ControlNames(15)
End Select
End Function


Using the above, I get the message shown in the attached file. The first control on my form is for the addressee's first name. As you can see in the graphic, this field is halfway down the list in the message box. Since there are alot of controls on my userform, it would be nice if they could appear in the message box in order of use. Is that possible?

[clhare]

Okay, it's me yet again... I found another little glitch. I have a listbox on the form that could have multiple items (which the user must choose from) or it could have only one item (which is automatically used). If there's only the one item in the list, it still shows up in the message as not selected (Employer 2). How do I update the code below to make sure this macro recognizes the single item as selected?

Case 1
lstEmployer2.Clear
lstEmployer2.Value = ""
lstEmployer2.AddItem ("ABC Co.") 'Listindex = 0
lstEmployer2.ListIndex = 0


The last line (lstEmployer2.ListIndex = 0) makes the entry highlighted in the listbox when I run the form, but it must not be "selecting" it in a way that can be recognized when testing the controls.

[fumei]

For example, I had a Yes and No button. If Yes is selected, the No shows up on the error list as empty. Cheryl, doing this is a matter of logic. Remember my comments about how MUCH code good error trapping takes up?

This is an example of that. Absolutely it can be done, just write the logic.

If No = False, check if Yes = True (as what if neither has been clicked), and IF Yes = True, Then do NOT add the No control to the empty list.

If Yes = False, check if No = True (as what if neither has been clicked), and IF No = True, Then do NOT add the Yes control to the empty list.

Check if BOTH Yes and No = False - in which case the user has not selected one, or the other. Which of course is also an error.

I had assumed they would appear in the order I listed them on the form and in the code, but that doesn't happen.Do not make assumptions. Are they REALLY in the order you listed them on the userform? What does that mean anyway? What does "listed" mean?

The code runs through the controls, yes? For ctl In Me.Controls.

What do you think this means? What do you think the first "ctl" will be?

Here is something you may not be aware of. For Each ctl In Me.Controls will process the controls in the order they were created on the userform. NOT TabIndex or alphabetically, or any other sequence. It will look at each control in the order you put it on the userform.

You can change the TabIndex to whatever you want, but VBA remembers, internally, the creation order of each control. Actually, it is not really remembering. When a control is created it is added to the Controls collection, which makes sense.

That collection of course has an index, right? So, there you go. When you run Each ctl in Controls, it of course does it by index number.

It gets even messier (in a way) if you are using Labels. Which...ahem...you should be using. They do not have a text value. They have a .Caption value. This can be taken care of by testing the TypeOf ctl.

Can you make your message match use? Yes. Build your array of controls in error. Now run through that using the .TabIndex values. Build the actual message string order based on .TabIndex.

Note that .TabIndex can NOT be done like:

ctl.TabIndex

TabIndex is a property of an object in the control collections, therefore it must be:

Me.Controls(ctl.name).TabIndex

Doing tight clean error trapping is IMO - again - the hardest part of coding. By far. Mostly because it absolutely requires LOGIC, an area most people have difficulty with.

[clhare]

Thank you for the information! Error handling is something I have been trying to get a better understanding of. I'll spend some time on this over the weekend. I was thinking the order the macro checked the controls would be based on the tab order! It never occured to me that it would be the order I put the controls on the form!

Two questions still re the file you attached to an earlier post, I noticed that if I ran the userform and didn't fill in all the fields, it would give me the message box with the list of empty fields. If I filled in everything, and clicked on the button again, I still get a message box, even though everything is filled in now. Why is that? Does it need to be reset somehow so each time it checks the controls, it starts fresh?

The second is re the addition of a listbox. My template has 2 listboxes on the form. The selection in the first listbox determines what's in the second listbox (which in some cases will be only 1 item). If the box has only 1 item, how do I get the macro to recognize that item as "selected"? As mentioned in one of my posts the other day, even when there's only 1 item in the listbox and I've set the listindex to that item in the code (which highlights it on the userform automatically), the macro doesn't recognized that something has been selected unless I actually click on the item. I'm not sure how to fix that.

Any suggestions?

[lucas]

I noticed that if I ran the userform and didn't fill in all the fields, it would give me the message box with the list of empty fields. If I filled in everything, and clicked on the button again, I still get a message box, even though everything is filled in now. Why is that? Does it need to be reset somehow so each time it checks the controls, it starts fresh?


Move the messagebox call up to above the end if as shown below. Then it only runs if there is an empty textbox.


Private Sub CommandButton1_Click()
Dim msg As String
Dim var
Call MyErrTraps
If bolHasErrors Then
For var = 0 To UBound(ErrMessages)
msg = msg & ErrMessages(var) & strEmpty & _
vbCrLf
Next
MsgBox strSaneNames & msg
End If
Unload Me
End Sub

[clhare]

Ok, I changed it as you suggested, but then after I click out of the message box, the form is unloaded. I don't want the form to unload until there are no empty text boxes left on the form. So I tried to change the code a bit so it would only unload if no empty text boxes were found, but while the form continues to pop up after I click out of the message box, once I have added text into each empty text box, I still can't get rid of the form!! AARRGGHH!!!!!:doh:

Private Sub CommandButton1_Click()
Dim msg As String
Dim var

Call MyErrTraps

If bolHasErrors Then
For var = 0 To UBound(ErrMessages)
msg = msg & ErrMessages(var) & strEmpty & _
vbCrLf
Next
MsgBox strSaneNames & msg
ElseIf bolHasErrors = False Then
Unload Me
End If
End Sub

[SOS]

Cheryl,

I'm in kind of the same area as you with regards to this.

What I want is to have the MsgBox show me what fields have yet to be completed and after clicking OK to get rid of the message box to go back to the form and allow me to complete the missing fields.

I've managed to achieve this by adapting the code like this:



Sub cmdOK_Click()
Dim msg As String
Dim var
bolHasErrors = False
Call MyErrTraps

If bolHasErrors Then
For var = 0 To UBound(ErrMessages)
msg = msg & ErrMessages(var) & strEmpty & _
vbCrLf
Next
End If
ReDim ErrMessages(var)
If var <> 0 Then
MsgBox strSaneNames & msg, , Title
Exit Sub
Else
End If
'CONTINUE THE REST OF YOUR CODE
End Sub


Hope that hepls

Seamus

[fumei]

I told you my demo was very simple, just to show things in principle.

You need to reset the Boolean variable to False before checking. So its value starts fresh.

If it is set to True (there are missing textboxes), because it is a global variable, it will remain True until you set it False. Which is what Seamus did.

[clhare]

What about the listbox problem? If there is only one entry, do I have to physically click on it to select it or can I do something in the code in that type of situation so it is "selected" as far as this macro goes?

[clhare]

Ok, I figured out a way around the problem with not recognizing a selection in the 2nd listbox if there's only 1 item in it--I disabled it.

Now, if the listbox has more than one item in it, it is enabled and the user of course would have to select something or get the message that a control is empty. But... if there's only 1 item in the listbox, the listbox is disabled. The value shows in the listbox, but the user doesn't have to do anything with it and neither does the error trapping macro.

I'm sure there's a better way to get this done, but I can't think of it.

[fumei]

Cheryl, I am not quite following. Can you repeat the problem with the listbox?

[clhare]

My userform includes 2 listboxes. The first one has several items listed. The 2nd one is filled based on the selection in the first, and in a couple instances the 2nd listbox will only have 1 item listed in it.

I modified your macro to also check the listbox controls. It works fine as long as I actually click on something in each listbox.

Here's the glitch...if the 2nd listbox only has one item in it, there's no reason to click on it, you would assume it was automatically selected. But, the macro doesn't recognize it as selected unless I actually click on that single item.

I couldn't figure out a way to get the single item "selected" as far as the control checking macro goes. When there's only going to be one item in the 2nd listbox, I use code like the following:

Select Case lstEmployer2
Case 0
' I list several items here
Case 1
lstEmployer2.Clear
lstEmployer2.Value = ""
lstEmployer2.AddItem ("ABC Co.")
'Listindex = 0
lstEmployer2.ListIndex = 0
Case 2
' I list several items here
End Select

The last line (lstEmployer2.ListIndex = 0) makes the entry highlighted in the listbox when I run the form, but it must not be "selecting" it in a way that can be recognized when testing the controls.

I figured out a work-around by disabling the 2nd listbox if it has a single item in the list. That way, the macro won't check that listbox at all.

[fumei]

1. The second line below is redundant.
lstEmployer2.Clear
lstEmployer2.Value = ""

2. The following is not a work-around. That is actually a good best-practice in error trapping.
I figured out a work-around by disabling the 2nd listbox if it has a single item in the list. That way, the macro won't check that listbox at all.If there is ONE item, then there is NO CHOICE, and therefore that value is (assumably) correct. In which case.....why check it?

[clhare]

Yea! I got one right!!

Now can I get another hint on the order of the controls in the message box? Do I need to change the code that tests the controls or change the code that builds the array? I'm kind of stumped on how to switch the message box to tabindex order.

Thanks!

[clhare]

Help!!!!!!

I've been trying for a couple days to figure out how to change the order of the controls in the message box to the tabindex order and I can't get it!!

Can someone help me with this code?
: pray2:

[lucas]

From Gerry's post #23

Do not make assumptions. Are they REALLY in the order you listed them on the userform? What does that mean anyway? What does "listed" mean?

The code runs through the controls, yes? For ctl In Me.Controls.

What do you think this means? What do you think the first "ctl" will be?

Here is something you may not be aware of. For Each ctl In Me.Controls will process the controls in the order they were created on the userform. NOT TabIndex or alphabetically, or any other sequence. It will look at each control in the order you put it on the userform.

You can change the TabIndex to whatever you want, but VBA remembers, internally, the creation order of each control. Actually, it is not really remembering. When a control is created it is added to the Controls collection, which makes sense.

That collection of course has an index, right? So, there you go. When you run Each ctl in Controls, it of course does it by index number.

[clhare]

I've got that, but I can't figure out how to use the information!!! How do I squeeze the tabindex in the code? I am totally stuck!

[lucas]

When a control is created it is added to the Controls collection
tabindex has nothing to do with this.

[clhare]

Using fumei's macro in this post, the message box lists which controls are empty (text boxes in particular) in the order they were added to the form. I want the message box to list them in the order they appear. Fumei said that would be accomplished using tabindex, but I can't figure out how, and I can't figure out any other way to accomplish it.

Any suggestions?

[lucas]

Read it again.....tabindex has nothing to do with it.

[fumei]

TabIndex has nothing to do with it, BUT....you can use the TabIndex to make a new array and get your message in the user tab order. That is, in the order the user will tab through the userform. Which, I believe, is what you want to happen Cheryl. I am tempted to just give textual descriptions and hints...but, nah.....

Sample (simple) demo file attached. Fire the userform with the "Show My Form" icon on the top toolbar.

Enter something in some of the textboxes. Click "Check It". The userform expands to display three messages in Labels.

The first lists any empty control by control index. This is the order For Each ctl In Me.Controls produces.

The second is the list of empty controls sorted by TabIndex (the order the user moves through the userform). This uses a version of the BubbleSort procedure xld posted in the knowledge base.

The third message is the list of empty controls by name, also in the TabIndex (user movement) order.

If you look in the code, it requires a HARD coding of all the controls you may wish to name. It only needs names of the controls the user actually uses. Labels and command buttons are not required, as they are ignored by the checking logic anyway.

So while true, the original error checking (to get the controls that are empty) has nothing to do with TabIndex, TabIndex values ARE used to determine how the control names are displayed.

There are other possible routes to take to achieve this.

I will repeat something I mentioned earlier. Solid error trapping is BY FAR the hardest work in coding an application. Hopefully you all will be a bit more patient when you come across screwy things in major applications (like Word for example). Catching stuff is HARD.

I will repeat another mention. I did a VBA app that took 500 lines of code to make the whole thing "work"....and 3,000 lines of code to error trap it as tight as possible. Most apps do not require that level of accuracy. This one did. I would not like to repeat the experience. The coding parts was not that difficult, but the THINKING part was a major headache.

If you look at the code in the file attached, it is not very difficult at all. And I freely admit it can likely be made tighter and better. I just put it together this evening as a demo for you whiners. Just kidding Cheryl. I know it is hard. Believe me, I know.

Hope this helps. It is as far as I am going to go with this. It of course can be expanded to cover ANY control on your userform. You just have to think about it.

I would strongly recommend you do NOT, repeat NOT, do a copy and paste of the code. Write your own. Copy and paste if you must, but go through each line, line by line and make it yours.

Oh, BTW....just so you know I can be an idiot - although I suppose that is already quite apparent - here is a laugh.

I had finished this demo, placed the controls, juggled them around so they would deliberately NOT match user Tab movement to control index. Wrote the code for the userform, wriggled xld's BubbleSort (his uses Option Base 1, and that just screws me up), changed some variables (I needed an number sort, not a string sort).

I decided that I wanted to change the textbox names. I had them as "TabMeOne", "TabMeTwo" etc, to show the user movement order. Then thought, what the heck...why not use real world names like FirstName, LastName etc.

So I blithely change the control name to First Name, and quickly went on to the next control.

Note that: First Name.

Control names can NOT have spaces.

The VBE gave an error message. Invalid name.

BUT.....I had clicked so fast to the next control that the control giving the error was no longer the active control. Totally froze. The error message on screen with a lovely cursor like a Do Not Enter (a circle with a line through it). Frozen shut. The VBE was displaying a message from a control it no longer had access to.

Enter did nothing. Clicking OK on the error message did nothing. Completely frozen. I tried everything. I could NOT get back in. I had to kill Word with Task Manager.

I....ummmmm, had not saved anything. I lost EVERYTHING. I had to restart Word and begin right from the beginning with a new document. Make a new userform, write all the code again from scratch. No copy and paste. From scratch, including placing the textboxes, the commandbuttons etc.

Idiot.

My point (besides the usual SAVE IT warning) is that I timed myself. From zero, zip, nada, to the attached file.....21 minutes.

This is not a show off thing. It is - again - a statement that coding is the minor part of things. I had the logic fully worked out. I knew EXACTLY, precisely, how it was to work, and how it was supposed to flow. I did not have to think about it the second time, because I already knew what was supposed to be there.

Although, again, because this is VBA, there are very likely better ways of doing this. I can think of an alternative myself.

Cheryl, and SOS, I really hope this helps you get moving along regarding this. It is likely not a full solution for your precise userform, but the principle should be (I hope!!!) adequately demonstrated.

I'm tired.

[clhare]

I've read it a bunch of times. If tabindex has nothing to do with it, how can I change the order of the controls in the message box????

I can't figure it out!:banghead:

[fumei]

PS. northernstar, if you are cruising through the threads, and see this one, you may want to take a look. There is quite a bit of array use involved.

[fumei]

Take a look Cheryl. Get back to me. While the demo does not give a messagebox - it displays the errors right on the userform via a Label - it could very easily be adjusted to give it in a messagebox.

[clhare]

This demo is fantastic! I will look at the code very carefully! Also, I never just cut and paste the code into my template. I always look at each line and try to understand exactly what the code is doing and why, then add comments throughout so I can understand it any time I go back to it. I want to learn as much as I can!

I spend a ton of time trying to figure out how to prevent errors, especially since I have no "VBA training"--just learning as I go. There is nothing worse than working through something and locking up before it's saved--I HATE that!!!

This forum rocks! I've learned so much from you guys! Thank you, thank you, thank you!!!!!:bow:

[fumei]

especially since I have no "VBA training"--just learning as I go.That is the situation for most (if not all) of us.

I am completely self-taught, with lots of stuff gained from these forums. There is no training for VBA for the most part. That is why I am writing a Word VBA course, so I can actually teach.

I like it that questions posed here sometimes challenge me to figure things out. That makes me keep learning.

I have read a number of your posts Cheryl. You do very well for yourself. Hopefully you will get to the point where you are a teacher yourself, with others learning from YOU.

I am happy you found the demo helpful.

[clhare]

Wow! I wish I knew as much as you about this stuff! This forum is my main source of information. I've learned so much from asking questions here and applying what I've learned as I try to do other things.

I'm always searching the internet for VBA tutorials as well. Maybe one of these days I'll find yours!

[fumei]

I doubt if my course will get on the 'Net. It is going to be for a actual five day intensive classroom course. On my test run of a shortened two day version, it was hard to screen people.

I set some pre-requisites - some reading beforehand. I supplied the reading content. I also clearly stated some knowledge pre-requisites. I was NOT going to teach anything about using Option Explicit, or declaring variables (although I do quickly re-iterate things about scope).

I was NOT going to teach about basic uses of the VBE.

It was a disaster. No one did any of the reading. The skill level of the people that were sent to me was essentially zero, with no knowledge of...well, anything.

They were not even moderately skilled at Word.

I had clearly stated to the powers-that-be that this was a abbreviated test run of a course for moderate to advanced Word users. That it was decidedly focused on design, and thinking, and understanding concepts.

Yes I did cover things like logic statements, but I was basing my timing on being able to go through a For Each statement in....oh, 1 to 2 minutes.

It took me 20 minutes to get them to fully understand the concept of:Dim oPara As Paragraph
For Each oPara In ActiveDocument.Paragraphs

That was just one thing. My timing was screwed. I planned on going through four of my course modules in the test run two days. I could barely get through two of them.

I mean I had to explain how to navigate through folders to get to a file! Sheeesh.

I suppose I could write a second course more for beginners, but it is not my intention to do so.

[fumei]

Wow! I wish I knew as much as you about this stuff! I wish I knew as much as Tony Jollans...so there you go. It is all relative. You are persistent, and you are (it seems anyway) not afraid to try and work it.

That, and time, is what it takes. You will get there.

[TonyJollans]

Why, thank you Gerry :D

What I really know is just enough to enable me to find out what I want to know.

I am certainly still learning - in fact that is what really drives me on. Once upon a time, when I started in computers, I had this crazy idea that if I tried hard enough I could learn it all; now I know I can't even learn it all about Word - not that it stops me trying :)