Trailing character when copying excell cell into word textbox - Help? 4

[yodandy]

Heres the run down.

I have two things open, a Microsoft Excel spreadsheet, and a Microsoft Word document

When I copy a twelve digit number by right clicking the cell, hitting copy, then pasting it into a textbox on a word document, I get a trailing symbol. Here is a picture with the cell, and the result when pasted.

Why is this? Is there a macro I can run to fix it? Or a setting somewhere to fix it?

If im being too vague let me know, thanks for the help!

 

[SkipVought]

Hi,

It's the Cell Delimiter. Rather use the cell VALUE property.

If you need further help, please post the code that you are having trouble with.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[yodandy]

Well, actually all im doing is simulating what the user will be doing once this process is in order.

They will be copying a number from excel, and pasting it into a textbox on the word doc. Then filling out some stuff and saving it for records. Im just trying to avoid a million emails asking why that trailing character is there.

Its not a problem with code, well, yet...since im wondering if theres a way for that character to NOT show up when pasted into the textbox

 

[PHV]

a way for that character to NOT show up
In the Immediate window of the VBE (Ctrl+G)
ActiveDocument.ActiveWindow.View.ShowAll = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[yodandy]

PHV, that didnt work, im still getting that character in my textbox :\

 

[SkipVought]

What does your VERIFY button do?

Could you not examine the last character in the string and if the value is less than a SPACE (ASCII 32), then remove it?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[yodandy]

(Verify is just a button to verify that the number im inputting is a valid "work order number" from another excel doc)

Alright, to better illustrate the problem, since the pic is a bit confusing, and I guess im not being too specific, try doing this, youll see what I mean.

Open a blank excel document, in the first cell, type the number 1 in a cell, right click the cell, hit copy

Open a blank word document, go to view, toolbars, control toolbars (if its not already open), add a text box, exit design mode, and right click the newly added textbox (not just anywhere on the document, right click the textbox), and hit paste.

This is the result...

 

[SkipVought]

I understand EXACTLY what you are referring to.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[yodandy]

Haha, i honestly cant tell if that was sarcasm or not, but anyway, yeah

im wondering if there is a macro that can run on load or something, to somehow stop that character from showing up, as users will be copying/pasting, and that showing up will really confuse them...

 

[Fr33dan]

try putting this in the code of the text box

Private Sub TextBox1_Change()
    If (Right(Text, 1) = "¶") Then
        Text = Left(Text, Len(Text) - 1)
    End If
End Sub

 

[yodandy]

Thanks Fr33dan, I got excited there, but alas, didnt work :\

 

[VRoscioli]

How about:

Private Sub TextBox1_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

If Right(Data.GetText, 1) = "¶" Then
    Data.SetText (Left(Data.GetText, Len(Data.GetText) - 1))
End If

End Sub

 

[Fr33dan]

Did you change the name of the sub to match the name of your textbox(TextBox1 to whatever your's is named)? If so then try copying the extra character into the quotations. Maybe the character I used isn't the same as the one it's adding

 

[VRoscioli]

Fr33dan: When I tried yours, it seems that there are two characters at the end of the numerical string. I don't know what the other is or where it is coming from though, or for that matter, why mine seems to work...

 

[Fr33dan]

VRoscioli: Your code gives me an error but you are right in that my doesn't work(I thought I tested it but oh well). I've tested the following code and it works for me

Private Sub TextBox1_Change()
    Text = ""
    For i = 1 To Len(TextBox1.Text)
        character = Right(Left(TextBox1.Text, i), 1)
        If (character = "1" _
        Or character = "2" _
        Or character = "3" _
        Or character = "4" _
        Or character = "5" _
        Or character = "6" _
        Or character = "7" _
        Or character = "8" _
        Or character = "9" _
        Or character = "0") Then
            Text = Text & character
        End If
    Next i
    TextBox1.Text = Text
End Sub

Hope this works for you

 

[SkipVought]

Private Sub TextBox1_Change()
    Text = ""
    For i = 1 To Len(TextBox1.Text)
        character = Right(Left(TextBox1.Text, i), 1)
        Select Case character
          Case "0" to "9"
            Text = Text & character
        End select
    Next i
    TextBox1.Text = Text
End Sub

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[VRoscioli]

Skip's code wins, as per usual.

 

[SkipVought]

Just cleaning up the previously posted code using the IFs and ORs.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Fr33dan]

And I learn a new way to use cases, Thx Skip

 

[yodandy]

Skip, your a lifesaver! Ive been trying to figure this last piece to the puzzle for a few days, thanks to you and everyone else who helped!!! This is a great place...woop!