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.