Page 1 of 4 123 ... LastLast
Results 1 to 15 of 60

Thread: Combining code

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Combining code

    I have an Excel 2010 file where I need (a) to disable entry in a couple of columns depending on the value in another column, and (b) to disable pasting into these columns.

    I have achieved (a) with help from this thread (see attached example "DisableEntry").
    http://windowssecrets.com/forums/sho...n-another-cell

    I have achieved (b) with help from this thread (see attached example "DisablePaste").
    http://windowssecrets.com/forums/sho...ste-into-cells

    I need to combine both functions in the same file, but simply combining the event tracking code from both files causes an error, because they both have a routine named "Worksheet_Change" (see attached example "DisableEntry&Paste").

    DisableEntry.xlsm
    DisablePaste.xlsm
    DisableEntry&Paste.xlsm

    How do I combine both functions in the same file (presumably there needs to be some renaming or merging)?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    I've played with your logic a bit and combined the functions to the point where I think they do what you want and are easier to understand, well at least for me.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim zUndo   As String
        Dim zSheet  As String
    
        If Not Intersect(Target, rngPreventPaste) Is Nothing Then
        
          Application.EnableEvents = False
          
          zUndo = Application.CommandBars("Standard").Controls("&Undo").List(1)
           
          If Left(zUndo, 5) = "Paste" Then    'i.e. Paste or PasteSpecial
            With Application                    'use shortcut
                .ScreenUpdating = False     'freeze display till ready
                .Undo                       'Undo last operation
                KillPaste
            End With                         'end of shortcut
            
          Else
           
            Select Case Target.Column
                Case 1
                       Select Case Target.Value
                             Case ""
                                 Cells(Target.Row, "B").ClearContents
                                 Cells(Target.Row, "C").ClearContents
                             Case "V"
                                 Cells(Target.Row, "B").ClearContents
                             Case Else
                                 Cells(Target.Row, "C").ClearContents
                       End Select 'Target.Value
                Case 2
                    If Cells(Target.Row, "A") = "V" And Cells(Target.Row, "A") <> "" Then
                        Cells(Target.Row, "B").ClearContents
                    End If
                Case 3
                    If Cells(Target.Row, "A") <> "V" And Cells(Target.Row, "A") <> "" Then
                        Cells(Target.Row, "C").ClearContents
                    End If
                
            End Select   'Target.Column
          
          End If 'Left(zUndo... check for a Paste operation
            
          Application.EnableEvents = True
          
        End If     'Not Intersect(...
        
    End Sub    'Sub Worksheet_Change(ByVal Target As Range)
    HTH
    Last edited by RetiredGeek; 2016-05-07 at 21:54.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Murgatroyd (2016-05-07)

  4. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that works OK.

    However, there are a couple of issues.

    1. The validation needs to be case insensitive. I gather this can be done by including "Option Compare Text", but where does it go?

    2. If the user makes valid entries, everything works OK. However, if the user makes an invalid entry, then the validation message appears, and if the user clicks on "Retry", it continues OK, but if the user clicks on "Cancel", a VBA error occurs, and the event tracking stops working. ("Run-time error -2147467259 (80004005): Method 'List' of object_'CommandBarComboBox' failed".
    Last edited by Murgatroyd; 2016-05-07 at 22:45.

  5. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that works OK and is simpler.

    However, there are a couple of issues.

    1. When the file is opened, a VBA error occurs ("Run-time error '9': Subscript out of range").

    2. If the user deletes a single entry in column A, it works OK, but if the user selects and deletes two or more entries in column A, a VBA error occurs ("Run-time error '13': Type mismatch).

    3. The validation in this code needs to be case insensitive. In my actual application, the options are not single letters like "Q" and "V" but strings like "QTY" and "VAL". The validation list is a named range on the sheet, and the validation function itself is case insensitive, so the validation in this code needs to be likewise. I know I could use "If UCase () ...", but I gather including "Option Compare Text" would be simpler, but where does it go?
    Last edited by Murgatroyd; 2016-05-08 at 00:56.

  6. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that works OK.

    I combined your entry-disabling code with the paste-disabling code (see attached file).
    DisableEntry&Paste_v2.xlsm

    1. I added a couple of lines to clear the cells in Columns B & C if a cell in Column A is cleared, and it works OK if only one cell in Column A is cleared, but if the user selects and clears two or more cells at once in Column A, then it doesn't work, and the rest of the code stops working. How can I fix this?

    2. If the user makes valid entries in all columns, everything works OK. However, if the user makes an invalid entry in any column, then the validation message appears, and if the user clicks on "Retry", it continues OK, but if the user clicks on "Cancel", a VBA error occurs, and the code stops working. ("Run-time error -2147467259 (80004005): Method 'List' of object_'CommandBarComboBox' failed".
    Last edited by Murgatroyd; 2016-05-08 at 05:31.

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    On your posted file, on sheet [RestrictedEntrySheet]
    Select the range [A2:A7] and then press the [Del] key to delete this range
    ..you are now left with 'orphan' values in columns [B] and [C].
    ..you can modify these with impunity.
    ..you can also modify the other cell values as well!

    In the attached file, this issue is fixed.

    zeddy
    Attached Files Attached Files

  8. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks. However, if I open your sheet and clear any of the entries (e.g., B2), a VBA error occurs ("Run-time error 424: Object required").

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    You're getting into some pretty murky logic here but let's tackle what you have so far.

    First, I don't think you want a WorkSheet_Change and a WorkSheet_SelectionChange Event. You're going to get into conflicts here unless you have tests to make sure they don't try to operate on the same ranges!

    Next, in the logic for the code presented below I don't think you want the "ProtectedRange" to be A1:C10 but rather A1:A10 since all you are checking for is to see if there is a value of QTY or VAL which only show up in column A! This will also fix your problems with Validation in B&C.

    The code below will however take care of the issue where a user tries to clear multiple items in column A, and they don't even have to be contiguous, e.g. Crtl+Click.

    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
    
    Set rngRestrictedEntry = Range("A2:A10")
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim zUndo   As String
    Dim zSheet  As String
    Dim Cell    As Range
    
    If Not Intersect(Target, rngRestrictedEntry) Is Nothing Then
        
      Application.EnableEvents = False
      
      zUndo = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
      If Left(zUndo, 5) = "Paste" Then
        With Application
          .ScreenUpdating = False
          .Undo
          KillPaste
        End With
      Else
           
        For Each Cell In Target
         
          Select Case UCase(Cell)
            Case "QTY"
              Cells(Cell.Row, 3).ClearContents
              Cell.Offset(0, 1).Select
            Case "VAL"
              Cells(Cell.Row, 2).ClearContents
              Cell.Offset(0, 2).Select
            Case ""
              Cells(Cell.Row, 2).ClearContents
              Cells(Cell.Row, 3).ClearContents
          End Select
        Next 'Cell
      
      End If  'Left(zUndo...
            
      Application.EnableEvents = True
          
    End If    'Not Intersect...
    
    End Sub
    While testing this I commented out the Worksheet_SelectionChange event code.

    Also, if you encounter an error and stop the code you need to go into the Immediate Window and enter Application.EnableEvents=True to reactivate events. Also you need to select the Unrestricted sheet then reselect the Restricted sheet to reset the RestrictedRange variable.

    BTW: It is very hard to work on code when you don't know all the necessary operations (i.e. the specifications). A clearer picture of what you are trying to accomplish would be very helpful.

    HTH
    Last edited by RetiredGeek; 2016-05-08 at 09:56.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Ooops. Forgot to remove a line of code
    see attached

    zeddy
    Attached Files Attached Files

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud and RG

    ..aside from unintentionally turning event trapping OFF during an unforeseen glitch or halt in the code, it is sometimes the case that calc mode is also left in 'manual mode' (turned OFF for speeding up vba etc etc etc)

    ..so, I usually include something like the following routine, which I generally 'attach' to a logo, shape, or whatever on the sheet, that provides a 'code blue' Excel kick-up-the-pants to make sure things are reset OK:
    Code:
    Sub rzDummy()
    
    With Application
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
    End With
    
    End Sub
    ..I'm sure you both do something similar too!

    zeddy

  12. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by RetiredGeek View Post
    It is very hard to work on code when you don't know all the necessary operations (i.e. the specifications). A clearer picture of what you are trying to accomplish would be very helpful.
    The specs are as follows.

    1. Type column
    - Should allow only QTY or VAL.

    2. Quantity column
    - Should allow only whole numbers.
    - Should be disabled unless Type = QTY.
    - Should be greyed out when disabled.
    - Should be cleared if Type column is cleared.

    3. Value column
    - Should allow only decimals.
    - Should be disabled unless Type = VAL.
    - Should be greyed out when disabled.
    - Should be cleared if Type column is cleared.

    4. All data entry cells (rngRestrictedEntry = Range("A2:C10"))
    - Should not allow pasting.

    5. The code should be as simple and robust as possible.

  13. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by zeddy View Post
    Ooops. Forgot to remove a line of code
    see attached
    Thanks, your DisableEntry&Paste_v3-zeddy.xlsm file works OK.

    However, one issue remains.

    If the user makes valid entries in all columns, everything works OK.

    However, if the user makes an invalid entry, the following occurs.

    1. Type column
    The validation error message correctly appears, and
    (a) if the user clicks on "Retry", then it correctly continues, but if the user now makes a valid entry or blank, then a VBA error occurs,
    or
    (b) if the user clicks on "Cancel", then a VBA error occurs immediately.

    2. Quantity and Value columns
    The validation error message correctly appears, and
    (a) if the user clicks on "Retry", then it correctly continues, and if the user now makes a valid entry, then it works OK, but if the user enters blank, a VBA error occurs,
    or
    (b) if the user clicks on "Cancel", then a VBA error occurs immediately.

    The VBA error that occurs in all of the above cases is: "Run-time error -2147467259 (80004005): Method 'List' of object_'CommandBarComboBox' failed [Continue] [End] [Debug] [Help]", and because the [Continue] button is greyed out, the user has to click on the [End] button, and then the code stops working.
    Last edited by Murgatroyd; 2016-05-08 at 22:03.

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    Personally (I know others will probably have a different take) I'd ditch the Validation Rules and implement it all in VBA.

    Here's a code shell that implements the check for paste and Column A with the appropriate error message.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
    
    Set rngRestrictedEntry = Range("A2:C10")
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim zUndo   As String
    Dim zSheet  As String
    Dim Cell    As Range
    
    If Not Intersect(Target, rngRestrictedEntry) Is Nothing Then
        
      Application.EnableEvents = False
      
      zUndo = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
      If Left(zUndo, 5) = "Paste" Then
        With Application
          .ScreenUpdating = False
          .Undo
          KillPaste
        End With
      Else
        
        Select Case Target.Column
          Case 1  'Col A
                For Each Cell In Target
           
                  Select Case UCase(Cell)
                    Case "QTY"
                        Cells(Cell.Row, 3).ClearContents
                        Cell.Offset(0, 1).Select
                    Case "VAL"
                        Cells(Cell.Row, 2).ClearContents
                        Cell.Offset(0, 2).Select
                    Case ""
                        Cells(Cell.Row, 2).ClearContents
                        Cells(Cell.Row, 3).ClearContents
                    Case Else
                        MsgBox "Error: Only QTY, VAL, or blank allowed!", _
                               vbCritical + vbOKOnly, "Data Entry Error"
                        Target.ClearContents
                        Target.Select
                  End Select
          
                Next 'Cell
                
          Case 2  'Col B
          Case 3  'Col C
        End Select  'Case Target.Column
        
      End If  'Left(zUndo...
            
      Application.EnableEvents = True
          
    End If    'Not Intersect...
    
    End Sub   'Worksheet_Change(ByVal Target As Range)
    Yes we're nesting the Selects a bit but I think it is clear and you can see all that is going on in a single place.
    Sorry, I don't have time to complete the code and I have PT first thing in the morning so it will be a while until I can get back to it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your suggestion. I would like to use the regular validation if possible, as my actual file has other columns using regular validation, and the Type column actually has a longer list of options, not just QTY and VAL.

    I am a beginner with VBA but am learning a lot from the experts here. I can follow the code in the "DisableEntry&Paste_v3-zeddy.xlsm" file enough to adapt it to my actual file; the only issue is the VBA error that appears when an invalid entry is retried or cancelled (see my previous post). I am wondering what is causing this and how to fix it.

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Murgatroyd

    Thanks for the detailed reply in post#15.

    I can now look at that and give a proper fix.
    I'll post an updated file when I've tested it more thoroughly!

    zeddy

Page 1 of 4 123 ... LastLast

Posting Permissions

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