Global StrConv function?

[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:

[vba]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
[/vba]

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?

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

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.


[VBA]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[/VBA]

[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!!!!!

[vba]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
[/vba]

[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:
[vba]
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
[/vba]
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?

[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.