Results 1 to 25 of 25

Thread: vba-excel allow-paste-special-only

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    vba-excel allow-paste-special-only

    http://www.siddharthrout.com/2011/08...-special-only/

    I am new to the VB Code

    We have trying to collate the information for various equipment data, which will be used in a software for analysis.

    Have created custom drop down list along with clear content as trying to utlise the paste special macro which was suggested in the above link by Mr. Siddharth Rout., but getting an error messages. The drop down fields are in yellow colour highlights.

    Also the formulas are getting copied elsewhere which need to be prevented.

    Here with attached the file for your suggestion.undefined

    Best Regards,

    Stephen M
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    but getting an error messages. The drop down fields are in yellow colour highlights.
    what error? when you do what?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    The Errors are described in the attached JPEG file.Name:  Paste Special Drop Down Errors.jpg
Views: 770
Size:  36.4 KB

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    i have looked at some of the code, it could do with significant improvement to reduce duplication, both within procedures and across worksheets

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Column = 3 Then ' plant_type Col
    Target.Offset(0, 1).ClearContents
    Target.Offset(0, 2).ClearContents
    End If

    If Target.Column = 4 Then ' plant_name Col
    Target.Offset(0, 1).ClearContents
    End If

    If Target.Column = 2 Then ' project_name Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 3 Then ' plant_type Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 4 Then ' plant_name Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 5 Then ' licensor Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 6 Then ' Confidential Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 7 Then ' item_tag_no Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 10 Then ' MERC_Group Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 12 Then ' vendor_name Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 13 Then ' MR_No Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 18 Then ' type_of_service Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 36 Then ' support_type Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 37 Then ' has_agitator Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 38 Then ' has_pump Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 39 Then ' has_heater Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 40 Then ' has_body_flange Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 41 Then ' has_flat_cover Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 42 Then ' PWHT Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    If Target.Column = 45 Then ' remarks Col
    Application.EnableEvents = False
    Target = VBA.UCase(Target)
    End If

    exitHandler:
    Application.EnableEvents = True
    Exit Sub

    End Sub
    all these ifs should be in at least a multiple elseif, but much better to use a select case
    like
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        select case target.column
             case 2 to 7, 10, 12, 13, 18, 36 to 42, 45   , check i got all the required columns
                    target = ucase(target)    ' though as the drop down list appear to be uppercase anyway, this seems pointless for most columns
        end select
        if target.column = 3 then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    i am guessing you have the same code (or close to it) for every worksheet, this code can probably be moved to the workbook_sheetchange, to work for every worksheet, it should not interfere with the code already there, it can be limited not to work for any specific sheets that do not require it

    i changed this bit to fix the errors
    Code:
        '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    basically it was errorring as the undolist was empty, and because the first lot of code allowed events to be run while cells were being edited it multiplied the errors

    please note i fixed the error but you will have to test if the code still works as required, it may need to have the end if moved up in the procedure, i am not sure
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Thanks for your support.

    The feed back is attached herewith.Name:  Paste Special Drop Down Situations.jpg
Views: 531
Size:  40.7 KB

    Appreciate your valuable input.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    i can not read the error message in the image

    what action are you doing that causes the error to occur?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    When I tried to add do the drop down, this error occurs.Name:  Paste Special Drop Down Errors3.jpg
Views: 760
Size:  27.7 KB

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    it would appear that you have pasted the code into the wrong place, it should have been used to replace the existing code, within the procedure
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Thank you very much, I appreciate the guidance.
    Have added the code in each sheet and updated the Uppercase columns. It is working fie, But new issues arise from these. The same is attached herewith.
    Name:  Paste Special Drop Down Errors4-1.jpg
Views: 611
Size:  53.3 KBName:  Paste Special Drop Down Errors4-2.jpg
Views: 1005
Size:  31.9 KB

    Once again thank you for the support.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    once again, i can not read the images

    post the code as you have it now (in code tags, not an image) and state where (which line) any error occurs and what the error message is and what action you are doing that cause the error to occur

    how are you protecting the yellow coloured columns?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    The following code is from the "Reactors" Sheet, This protected the format copying from one cell to another (Formatted columns are in the yellow colour cells), But for our work flow of data population, we need users are allowed to copy the content in rows and pasted below, so that the repeated datas, need not be typed or selected from drop down list.

    Currently we intend to lock sheet by password protect the cells of rows 1, 2, 3 and the columns A & B to prevent the user from modifying by mistake, these are used in web application to co-relate the data search.

    Hope you are able to see the code below now.

    Code:
    'REACTORS
     Private Sub Worksheet_Change(ByVal Target As Range)
         '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Column
             Case 2 To 7, 10, 12, 13, 18, 36 To 42, 45 'check i got all the required columns
                    Target = UCase(Target)    ' though as the drop down list appear to be uppercase anyway, this seems pointless for most columns
        End Select
        If Target.Column = 3 Then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    When I tried to copy the data from row 4 to row 5, the following error appears

    Run-time error '9'
    Subscript out of range

    when I choose "debug" option
    The following line in code area will become yellow in colour.

    Quote
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    Unquote

    Trust I could express my requirement in more understandable way.

    Appreciate your time and efforts.
    Last edited by si_the_geek; Aug 30th, 2017 at 03:37 AM. Reason: fixed typo in tags

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Quote Originally Posted by STEPHENM View Post
    The following code is from the "Reactors" Sheet, This protected the format copying from one cell to another (Formatted columns are in the yellow colour cells), But for our work flow of data population, we need users are allowed to copy the content in rows and pasted below, so that the repeated datas, need not be typed or selected from drop down list.

    Currently we intend to lock sheet by password protect the cells of rows 1, 2, 3 and the columns A & B to prevent the user from modifying by mistake, these are used in web application to co-relate the data search.

    Hope you are able to see the code below now.

    Code:
    'REACTORS
     Private Sub Worksheet_Change(ByVal Target As Range)
         '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Column
             Case 2 To 7, 10, 12, 13, 18, 36 To 42, 45 'check i got all the required columns
                    Target = UCase(Target)    ' though as the drop down list appear to be uppercase anyway, this seems pointless for most columns
        End Select
        If Target.Column = 3 Then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    When I tried to copy the data from row 4 to row 5, the following error appears

    Run-time error '9'
    Subscript out of range

    when I choose "debug" option
    The following line in code area will become yellow in colour.

    Quote
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    Unquote

    Trust I could express my requirement in more understandable way.

    Appreciate your time and efforts.
    Looking forward with some suggestions.....

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    Trust I could express my requirement in more understandable way.
    yes i can now read and understand

    i tried pasting row 4 to row 5 on the original workbook (sheet reactors) you posted, worked without error, but as there is no data a bit difficult to be sure if it worked correctly
    post a new sample workbook with some data on one sheet and your current code, to test with, i may also need to test on a later version of excel than i normally use, somethings work differently
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Thank you very much for your time.

    Here below are the scenarios.

    Attached the sample filled in file along with VB code inside

    The VBA code is given below for ease.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
         '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Column
             Case 2 To 7, 10, 12, 13, 18, 36 To 42, 45 'check i got all the required columns
                    Target = UCase(Target)    ' though as the drop down list appear to be uppercase anyway, this seems pointless for most columns
        End Select
        If Target.Column = 3 Then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    • ROW COPYING FUNCTIONALIST

    The copying of the rows completely are happening, except for the column C, D & E where the drop down values inter linkage & clear content features are in built requirement are not working correctly.

    Described the requirement below:-
    The Linkage is the selection is from C to D the E columns. (i.e PLANT_TYPE -- PLANT_NAME -- LICENSOR_NAME)
    if any of the value in column C or D changed then clear content should work to remove the Column D and /or Column E appropriately.

    With the above requirement the column D or E should not be allowed to copy independently.

    Please refer in the attached file "Reactor" sheet
    1. The column D drop down is pasted in row 10, without column C row 10 value selected.
    2. Also in Column E drop down is pasted in row 12, without column C & D value are selected.


    This should be restricted & avoided.

    • COLUMN COPYING FUNCTIONALIST


    The column format in yellow colour is having the drop down values,
    One column drop down value is different from another, hence should not be allowed to copy from one column to another.
    The drop down value is getting copied from one column to another along with drop down value, this is spoiling the format & data capture.

    Please refer in the attached file "Reactor" sheet
    1. In the column H row 6, 7 & 8 the drop down values of the column C, D & E are copied.


    This should be restricted.

    Appreciate your help in fixing these problems.
    Attached Files Attached Files

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Quote Originally Posted by STEPHENM View Post
    Thank you very much for your time.

    Here below are the scenarios.

    Attached the sample filled in file along with VB code inside

    The VBA code is given below for ease.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
         '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Column
             Case 2 To 7, 10, 12, 13, 18, 36 To 42, 45 'check i got all the required columns
                    Target = UCase(Target)    ' though as the drop down list appear to be uppercase anyway, this seems pointless for most columns
        End Select
        If Target.Column = 3 Then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    • ROW COPYING FUNCTIONALIST

    The copying of the rows completely are happening, except for the column C, D & E where the drop down values inter linkage & clear content features are in built requirement are not working correctly.

    Described the requirement below:-
    The Linkage is the selection is from C to D the E columns. (i.e PLANT_TYPE -- PLANT_NAME -- LICENSOR_NAME)
    if any of the value in column C or D changed then clear content should work to remove the Column D and /or Column E appropriately.

    With the above requirement the column D or E should not be allowed to copy independently.

    Please refer in the attached file "Reactor" sheet
    1. The column D drop down is pasted in row 10, without column C row 10 value selected.
    2. Also in Column E drop down is pasted in row 12, without column C & D value are selected.


    This should be restricted & avoided.

    • COLUMN COPYING FUNCTIONALIST


    The column format in yellow colour is having the drop down values,
    One column drop down value is different from another, hence should not be allowed to copy from one column to another.
    The drop down value is getting copied from one column to another along with drop down value, this is spoiling the format & data capture.

    Please refer in the attached file "Reactor" sheet
    1. In the column H row 6, 7 & 8 the drop down values of the column C, D & E are copied.


    This should be restricted.

    Appreciate your help in fixing these problems.

    Looking forward your suggestion.
    STEPHEN m

  16. #16

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Looking forward for the suggestion...

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    as far as i can tell this can not work as when the code in the worksheetchange event is run, the paste does not go into the undo list, if the code in the undo list is skipped then the paste does go into the undo list, whether any of this behavior varies by excel version i do not know, certainly i am using a older version of excel than you

    sidharth is seldom here these days, but maybe if you pm him, with a link to this thread, he possibly will assist you
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Dear Sir,

    Thank you very much for your valuable time & suggestions, Will send the personal message to Mr. Siddharth Rout and seek his guidance.

    Best Regards,

    Stephen M

  19. #19
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: vba-excel allow-paste-special-only

    Hi Stephen

    Got your PM. Slightly busy with other projects and hence was not able to reply on time. Anyways, I see lot of posts above. So if you want me to effectively and quickly help you then can you in ONE POST cover the below.

    1. What are you trying to achieve?
    2. What code are you using?
    3. What problem are you facing?
    4. What is the error message (if any) that you are getting? Please attach screenshots
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  20. #20

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    To Achieve:
    We are having a team of around 15 persons collating the various old project data’s to be uploaded in the web based in house developed s/w for data mining and searching the relevant data for current use as reference.
    In order to collate the data we have created Éxcel workbook as one file with various sub sheet according to the typology of equipment’s.
    This file has common & department specific sheet at the end which contain the various drop down values and the interlinkage is created using Name Manager under Formula Tab.
    For ease, Yellow color is given to identify the columns, which has the drop down values.
    While coping the data from another file or from the same file, the format that is created for the drop downs are destroyed by the user, unknowingly, which is very difficult to identify.

    Code Used:
    VBA Code.

    Problem Faced:
    • ROW COPYING FUNCTIONALITY
    • Described the requirement below:-
    The Linkage is the selection is from C to D the E columns. (i.e PLANT_TYPE -- PLANT_NAME
    • if any of the value in column C or D changed then clear content should work to remove the Column D and /or Column E appropriately.
    • With the above requirement the column D or E should not be allowed to copy independently
    • Please refer in the attached file "Reactor" sheet
    • The column D drop down is pasted in row 11, without column C row 11 value selected. (Refer Error snap shot PS-2)
    • This should be restricted & avoided

    • COLUMN COPYING FUNCTIONALITY
    • The column format in yellow colour is having the drop down values,
    • One column drop down value is different from another, hence should not be allowed to copy from one column to another.
    • The drop down value is getting copied from one column to another along with drop down value, this is spoiling the format & data capture.
    • Please refer in the attached file "Reactor" sheet (Column H , Row 7, 8 & 10) Refer Snapshot PS-1
    • This should be restricted

    Error Messages:
    • PS-1 JPG file
    • PS-2 JPG files
    Attached Images Attached Images   
    Attached Files Attached Files

  21. #21

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
         
        '~~> Get the undo List to capture the last action performed by user
        If Application.CommandBars("Standard").Controls("&Undo").Enabled Then
           UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        
           '~~> Check if the last action was not a paste nor an autofill
           If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
           Then GoTo LetsContinue
        
           '~~> Undo the paste that the user did but we are not clearing
           '~~> the clipboard so the copied data is still in memory
           Application.Undo
        
           If UndoList = "Auto Fill" Then Selection.Copy
           
           '~~> Do a pastespecial to preserve formats
           On Error Resume Next
           '~~> Handle text data copied from a website
           Target.Select
           ActiveSheet.PasteSpecial Format:="Text", _
           Link:=False, DisplayAsIcon:=False
        
           Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
           On Error GoTo 0
        
           '~~> Retain selection of the pasted data
           Union(Target, Selection).Select
        End If
    LetsContinue:
    
    If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Column
             Case 2 To 7, 10, 12, 13, 18, 36 To 42, 45
                    Target = UCase(Target)
        End Select
        If Target.Column = 3 Then
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        ElseIf Target.Column = 4 Then   ' plant_name Col
            Target.Offset(0, 1).ClearContents
        End If
     
    
        'Module for accepting NUMBER ONLY is called below:
    
        Select Case Target.Column
        Case 19 To 22, 28, 29:
            Application.EnableEvents = False
        Call OnlyNums(Target)
        End Select
    
    exitHandler:
        Application.EnableEvents = True
        Exit Sub   ' not required as nothing after
        
    End Sub
    The Code Used are above.

  22. #22
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: vba-excel allow-paste-special-only

    Problem Faced:
    • ROW COPYING FUNCTIONALITY
    • Described the requirement below:-
    The Linkage is the selection is from C to D the E columns. (i.e PLANT_TYPE -- PLANT_NAME
    • if any of the value in column C or D changed then clear content should work to remove the Column D and /or Column E appropriately.
    • With the above requirement the column D or E should not be allowed to copy independently
    • Please refer in the attached file "Reactor" sheet
    • The column D drop down is pasted in row 11, without column C row 11 value selected. (Refer Error snap shot PS-2)
    • This should be restricted & avoided

    • COLUMN COPYING FUNCTIONALITY
    • The column format in yellow colour is having the drop down values,
    • One column drop down value is different from another, hence should not be allowed to copy from one column to another.
    • The drop down value is getting copied from one column to another along with drop down value, this is spoiling the format & data capture.
    • Please refer in the attached file "Reactor" sheet (Column H , Row 7, 8 & 10) Refer Snapshot PS-1
    • This should be restricted
    Unfortunately, the code that you are using is not meant for such complex operation. You will have to re-do the code from the scratch keeping all your requirements in mind. Mind you, it will not be a simple code.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  23. #23

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Dear Sir,

    Thank you very much for your feed back.

    Is there any way, We can get this done as professional services, either some one you know who could do or yourself.

    It would be great help for us. My contact details are +91 9920912295 or stephen.murugaiah@gmail.com

    Appreciate your support.

    Best Regards,

    Stephen M

  24. #24

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    15

    Re: vba-excel allow-paste-special-only

    Dear Sir,

    Appreciate your help in advance.

    Best Regards,

    Stephen M

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: vba-excel allow-paste-special-only

    despite your clarification, i still do not really understand exactly what you want to do or why
    whilst i could understand previously, why the code was erroring i never figured out what the desired result should be, or rather what was unacceptable

    i think i would suggest using a userform to enter data and only make the worksheet viewable, not editable, do all new entry and edits using the userform, probably the same userform could be used for all worksheets, just have a combobox for the userform name
    using a userform, you can validate all fields of data before updating the worksheet

    alternatively it may be possible when editing the worksheet, if the user tries to move to another row, validate the entire row he is moving from, if the data does not validate correctly, then lock the rest of the worksheet till that row is corrected, or clear the entire row and make them start again
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width