VBA Excel VB UserForm Trim, RTrim spaces issue

[dprayner]

Hi People.

I have an application using Excel and the VB form Editor.

The code is in VBA and a user completes the VB form and the responses populate the spreadsheet.

My problem is some of the users get little black squares after text and/or numbers.

I used the Trim function and RTrim function, but it doesn't seem to help. The problem is I can only test through certain users, because I have never gotten the little black boxes when I run the program.

Any suggestions? DAVE

 

[EBGreen]

Are they copying and pasting the information into the cells? You may need to go throught the input and filter out values that are not valid ASCII for an input box.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[dprayner]

Hi EBGreen.

When the user clicks the command button on the user form the code populates the Excel spreadsheet (ie. Range("A1") = TextBox1.Value or Range("B1") = TextBox2.Text). Someone told me that the little black boxes in the cells were actually spaces from the text box on the VB form. The little black boxes are always to the right of the number of text that's why I have been using the following: Range("A1") = RTrim(TextBox1.Text) etc. DAVE

 

[EBGreen]

Find a way to run this sub on the data from a problem cell and compare the results to an ASCII chart to see what is actaully in the cell:

Sub ReportAscii(strTest)
Dim i

For i = 1 To Len(strTest)
Debug.Print Asc(Mid(strTest, i, 1))
Next
End Sub

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[IknowMe]

I had a similar issue with black boxes. My users requested the use of the "ENTER" key in liue of the "TAB" key to progress through my form.

After setting up the "ENTER" key to tab them through the Form some users started getting little black boxes at the ends of their input.

You can probably see where this is going by now but I'll continue. When a user hit "ENTER" along with the forward movement through the Form I got the unexpected addition of the "ENTER" garbage added to my variable.

Not sure if this is the problem in your case but may be worth a look. I had similar symptoms (some users getting the black boxes (those using enter to tab) and some not getting them (those who actually used the tab key).

To fix I just stripped out chr(13) from the variable if the user used "ENTER" to TAB.

Good Luck

Wow, i'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[dprayner]

Hi people. I was finally able to recreate this issue. It appears to happen when a user cuts and pastes into a text box from another application. The name (or number) is preceded a carriage return? character (the same characted that is at the end of the line in a MS Word doc). Now I have to create a filter for that. DAVE