Delete spaces of value just pasted in worksheet cell

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,867
Office Version
  1. 2007
Platform
  1. Windows
Some copied values that are then pasted into the worksheet cell is this format as follows MZ 2881 9728 8GB

Can we have the value spaces removed once pasted so it then looks this this MZ288197288GB

Some info to assist you.

The column for the pasted value in question will always be column E

It will only need to be applied if either of the following two value's are in column J
ROYAL MAIL or TRACKED 24

Below is my currect change event.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Target.Row > 9 Then
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Target.Interior.Color = vbRed
    
    End If
    Application.ScreenUpdating = False
    
    End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Try:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 And Target.Row > 9 Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim rng As Range
        For Each rng In Target
            If rng = "" Then rng.Interior.Color = vbRed
            If rng.Offset(, 5) = "ROYAL MAIL" Or rng.Offset(, 5) = "TRACKED 24" Then
                rng = Replace(rng, " ", "")
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
 
Upvote 0
I wasnt to know it made any difference in that i didnt say it was being pasted there from a userform.

If i just paste it normally when working on the sheet then yes it works.
Being sent from a userform to the worksheet it is the same with all the spaces.

Below is the code that i use to send value from userfom to worksheet.
Sorry about this

Rich (BB code): 
Private Sub PostageSheetTransferButton_Click()

Cancel = 0
If TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "CUSTOMER`S NAME NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox2.SetFocus
    
ElseIf CommonPurchasedItem.Text = "" Then
    Cancel = 1
    MsgBox "PURCHASED ITEM NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    CommonPurchasedItem.SetFocus
    
ElseIf TextBox9.Visible = True And TextBox9.Text = "" Then
    Cancel = 1
    MsgBox "EBAY USERNAME NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
ElseIf OptionButton15.Value = False And OptionButton16.Value = False Then
    Cancel = 1
    MsgBox "SECURITY QUESTION NOT ANSWERED", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton12.Value = False And OptionButton13.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A USER NAME OPTION", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf TextBox4.Visible = True And TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "TRACKING NUMBER NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox4.SetFocus
    
ElseIf OptionButton7.Value = False And OptionButton10.Value = False And OptionButton17.Value = False And OptionButton18.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A POSTAL COMPANY", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton4.Value = False And OptionButton5.Value = False And OptionButton6.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT AN ORIGIN", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT AN EBAY ACCOUNT", vbCritical, "POSTAGE TRANSFER SHEET"
        
ElseIf OptionButton13.Value = True And TextBox9.Value = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER AN EBAY USER NAME", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
End If

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim lastRow As Long
Dim LArea As Long
Dim xShape As Shape
Dim Mycomments As Variant
Dim answer As Integer

lastRow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.count, 1).End(xlUp).Row
    
On Error Resume Next
    
 With ThisWorkbook.Worksheets("POSTAGE")
    .Cells(lastRow + 1, 1).Value = CDate(TextBox1.Text)
    .Cells(lastRow + 1, 2).Value = TextBox2.Text
    .Cells(lastRow + 1, 3).Value = CommonPurchasedItem.Text
    .Cells(lastRow + 1, 5).Value = TextBox4.Text
    .Cells(lastRow + 1, 4).Value = TextBox6.Text
    .Cells(lastRow + 1, 9).Value = TextBox9.Text
    .Cells(lastRow + 1, 7).Value = "POSTED"
    .Cells(lastRow + 1, 4).NoteText Text:=TextBox10.Text
    
    If OptionButton1.Value = True Then .Cells(lastRow + 1, 8).Value = "DR": OptionButton1.Value = True
    If OptionButton2.Value = True Then .Cells(lastRow + 1, 8).Value = "IVY": OptionButton2.Value = True
    If OptionButton3.Value = True Then .Cells(lastRow + 1, 8).Value = "N/A": OptionButton3.Value = True
    If OptionButton4.Value = True Then .Cells(lastRow + 1, 6).Value = "EBAY": OptionButton4.Value = True
    If OptionButton5.Value = True Then .Cells(lastRow + 1, 6).Value = "WEB SITE": OptionButton5.Value = True
    If OptionButton6.Value = True Then .Cells(lastRow + 1, 6).Value = "N/A": OptionButton6.Value = True
    If OptionButton7.Value = True Then .Cells(lastRow + 1, 10).Value = "TRACKED 24": OptionButton7.Value = True
    If OptionButton10.Value = True Then .Cells(lastRow + 1, 7).Value = "COLLECTION"
    If OptionButton10.Value = True Then .Cells(lastRow + 1, 10).Value = "COLLECTION": OptionButton10.Value = True
    If OptionButton12.Value = True Then .Cells(lastRow + 1, 9).Value = "N/A": OptionButton12.Value = True
    If OptionButton15.Value = True Then .Cells(lastRow + 1, 11).Value = "YES": OptionButton15.Value = True
    If OptionButton16.Value = True Then .Cells(lastRow + 1, 11).Value = "NO": OptionButton16.Value = True
    If OptionButton17.Value = True Then .Cells(lastRow + 1, 10).Value = "EVRI": OptionButton17.Value = True
    If OptionButton18.Value = True Then .Cells(lastRow + 1, 10).Value = "ROYAL MAIL": OptionButton18.Value = True
    
On Error Resume Next
         
With ThisWorkbook.Worksheets("POSTAGE").Cells(lastRow + 1, 4).Comment
        .Shape.Autoshapetype = msoShapeRoundedRectangle
        .Shape.TextFrame.Characters.Font.NAME = "Times Roman" ' FONT FAMILY STYLE
        .Shape.TextFrame.Characters.Font.Size = 12 ' TEXT SIZE
        .Shape.TextFrame.Characters.Font.ColorIndex = 5 ' TEXT COLOR
        .Shape.LINE.ForeColor.RGB = RGB(0, 0, 0) ' ARROW & LINE COLOR
        .Shape.Fill.Visible = msoTrue
        .Shape.Fill.ForeColor.RGB = RGB(255, 255, 255) ' FILL COLOR
        .Shape.TextFrame.AutoSize = True
        
    End With
    
On Error GoTo 0

Dim colorHTML As String, r As String, g As String, b As String

        TextBox2.Value = ""
        CommonPurchasedItem.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        TextBox10.Value = ""
        Application.ScreenUpdating = True
        OptionButton1.Value = False
        OptionButton2.Value = False
        OptionButton3.Value = False
        OptionButton4.Value = False
        OptionButton5.Value = False
        OptionButton6.Value = False
        OptionButton7.Value = False
        OptionButton10.Value = False
        OptionButton12.Value = False
        OptionButton13.Value = False
        OptionButton15.Value = False
        OptionButton16.Value = False
        OptionButton17.Value = False
        OptionButton18.Value = False

        Application.Goto Sheets("POSTAGE").Range("B" & Rows.count).End(xlUp), True
        Call UserForm_Initialize
        
        If Worksheets("POSTAGE").Cells(lastRow + 1, 11).Value = "" Then MsgBox "THERE ISNT A YES / NO VALUE IN SECURITY CELL", vbCritical, "SECURITY MESSAGE"
   
        TextBox1.Value = Format(Date, "dd/mm/yyyy")
        TextBox2.Value = ""
        CommonPurchasedItem.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        TextBox10.Value = ""
        TextBox2.SetFocus
        
        ListBox2.Clear
        UserForm_Initialize
        End With
       
        answer = MsgBox("IS THERE A PHOTO TO HYPERLINK FOR THIS CUSTOMER ?", vbYesNo + vbInformation, "HYPERLINK PHOTO MESSAGE")
        If answer = vbNo Then
        Exit Sub
        Else
        End If

        
        With ActiveCell
         Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
        If ActiveCell.Column = Columns("B").Column Then
          
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        With ActiveCell
        .Font.Size = 12
        End With
        MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER PHOTO MESSAGE.") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        End If
 
        End If
        End With
        
        End Sub
 
Upvote 0
It is hard to suggest a solution without seeing how your data is organized. It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Maybe this will help you.

On the userform in TextBox4 this is where the pasted value format is placed MZ 2881 9728 8GB

Not sure if this is needed to help you but will advise anyway.
TRACKED 24 is OptionButton7 & ROYAL MAIL is OptionButton18 both are in a framce called Frame3



After values are added to other Textboxes etc its then sent to worksheet.
 
Upvote 0
Try replacing the current section of code with this:
VBA Code: 
        .Cells(lastRow + 1, 1).Value = CDate(TextBox1.Text)
        .Cells(lastRow + 1, 2).Value = TextBox2.Text
        .Cells(lastRow + 1, 3).Value = CommonPurchasedItem.Text
        .Cells(lastRow + 1, 4).Value = TextBox6.Text
        .Cells(lastRow + 1, 9).Value = TextBox9.Text
        .Cells(lastRow + 1, 7).Value = "POSTED"
        .Cells(lastRow + 1, 4).NoteText Text:=TextBox10.Text
       
        If OptionButton1.Value = True Then .Cells(lastRow + 1, 8).Value = "DR": OptionButton1.Value = True
        If OptionButton2.Value = True Then .Cells(lastRow + 1, 8).Value = "IVY": OptionButton2.Value = True
        If OptionButton3.Value = True Then .Cells(lastRow + 1, 8).Value = "N/A": OptionButton3.Value = True
        If OptionButton4.Value = True Then .Cells(lastRow + 1, 6).Value = "EBAY": OptionButton4.Value = True
        If OptionButton5.Value = True Then .Cells(lastRow + 1, 6).Value = "WEB SITE": OptionButton5.Value = True
        If OptionButton6.Value = True Then .Cells(lastRow + 1, 6).Value = "N/A": OptionButton6.Value = True
        If OptionButton7.Value = True Then
            .Cells(lastRow + 1, 10).Value = "TRACKED 24": OptionButton7.Value = True
            .Cells(lastRow + 1, 5).Value = Replace(TextBox4.Text, " ", "")
        End If
        If OptionButton10.Value = True Then .Cells(lastRow + 1, 7).Value = "COLLECTION"
        If OptionButton10.Value = True Then .Cells(lastRow + 1, 10).Value = "COLLECTION": OptionButton10.Value = True
        If OptionButton12.Value = True Then .Cells(lastRow + 1, 9).Value = "N/A": OptionButton12.Value = True
        If OptionButton15.Value = True Then .Cells(lastRow + 1, 11).Value = "YES": OptionButton15.Value = True
        If OptionButton16.Value = True Then .Cells(lastRow + 1, 11).Value = "NO": OptionButton16.Value = True
        If OptionButton17.Value = True Then .Cells(lastRow + 1, 10).Value = "EVRI": OptionButton17.Value = True
        If OptionButton18.Value = True Then
            .Cells(lastRow + 1, 10).Value = "ROYAL MAIL": OptionButton18.Value = True
            .Cells(lastRow + 1, 5).Value = Replace(TextBox4.Text, " ", "")
        End If
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro Copy and Paste Value and formatting in multiple same cells when condition is met.
Replies
4
Views
295
RobP
R
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
266
Joe4
Joe4
F
  • Question Question
Deleting table column not counting as a change in range
Replies
2
Views
184
Fudging
F
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
402
tixarah
T
L
  • Question Question
Insert a number of rows based on a cell value and insert a new string in said rows.
Replies
1
Views
940
Joe4
Joe4

Forum statistics

Threads
1,225,902
Messages
6,187,734
Members
453,437
Latest member
Chexmix

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top