Results 1 to 10 of 10

Thread: worksheet_change event error

  1. #1

    worksheet_change event error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a worksheet where I have a drop-down cell with values from a hidden sheet. Then I have other cells where a monetary value is placed in that cell based on what is selected in the drop-down cell. When an option is selected in the drop-down cell, I want the corresponding monetary cell to allow a value to be entered and the other cell to then hide. Or disable so no data can be entered. My drop-down cell is G9, my 2 other cells are B67 and B69. Here is an example of my code:

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
    If Range("G9").Value = "Implant Pass-through: Auto Inovice Pricing (AIP)" Then
    Range("B67").Hidden = False
    End If
    If Range("G9").Value = "Implant Pass-through: PPR Tied to Invoice" Then
    Range("B67").Hidden = True
    End If
    If Range("G9").Value = "Implant Pass-through: PPR Tied to Invoice" Then
    Range("B69").Hidden = False
    End If
    If Range("G9").Value = "Implant Pass-through: Auto Invoice Pricing (AIP)" Then
    Range("B69").Hidden = True
    End If
    End Sub
    I get a debug error no matter what I try

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The error you're getting is because you can't hide a specific cell, just an entire row, column or worksheet.

    Are you trying to lock things down to prevent editing once a record has been entered, or something else? If the former, you may want to try using a SelectionChange event to kick the user somewhere else if they try to select a cell with data in it...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Would this work for you.

    In the dependent cells, add Data Validation, Custom type, with a formula of

    =IF(OR(G9="Implant Pass-through: Auto Inovice Pricing (AIP)","Implant Pass-through: PPR Tied to Invoice"),"")

    It will say 'The formula currently evaluates to an error", but just say yes and continue.

    What this is doing is saying that if the G9 cell has one of those values, anything input in the other cells will throw a DV error. If G( is clear, you can input.

  4. #4
    No, that does not work. I want cell B67 to allow input if cell G9 is selected with the AIP and if PPR is selected then B69 allows user input and cell B67 does not. This works for me:

    Code:
    Option Explicit
     Private Sub Worksheet_Change(ByVal Target As Range)
       ActiveSheet.Unprotect
        If Range("G9").Value = "Implant Pass-through: Auto Invoice Pricing (AIP)" Then
        Range("B67").Locked = False
        Else
        Range("B67").Locked = True
        End If
        If Range("G9").Value = "Implant Pass-through: PPR Tied to Invoice" Then
        Range("B69").Locked = False
        Else
        Range("B69").Locked = True
        End If
       ActiveSheet.Protect
          End Sub

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Can I suggest a change to this? The following is a little cleaner to see what you are accomplishing, I think, and will also avoid the event running each time you change the state of the protection on the cell:

    Code:
    Private lEvents As Long
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        
        'Increase event handle counter
        lEvents = lEvents + 1
        
        'Avoid recursive calls
        If lEvents = 1 Then
            Set ws = ActiveSheet
            
            'Protect the worksheet
            ActiveSheet.Unprotect
            
            'Lock both cells
            ws.Range("B67").Locked = True
            ws.Range("B69").Locked = True
            
            'Unlock cells dependant on G9's value
            Select Case ws.Range("G9").Value
                Case Is = "Implant Pass-through: Auto Invoice Pricing (AIP)"
                    ws.Range("B67").Locked = False
                Case Is = "Implant Pass-through: PPR Tied to Invoice"
                    ws.Range("B69").Locked = False
                Case Else
                    'No changes
            End Select
            
            'Re-protect the worksheet
            ActiveSheet.Protect
        
        End If
        
        'Decrease event handle counter
        lEvents = lEvents - 1
    End Sub
    Note that the Private lEvents line should be below any Option lines, but above the first procedure in the module.
    Last edited by Ken Puls; 2014-02-13 at 12:06 AM. Reason: Corrected code
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    I commented everything out of my program and put this in the worksheet VBA and not workbook and put it below option explicit and I get this error:

    compile error: expected function or variable and it says at the activesheet=unprotect section of the above code. So, in the workbook I tried uncommenting my password code stuff and then tried it and I get the same error. Do I not need any password codes when 4 of my cells are protected?

  7. #7
    The code I put in works fine though it might not be 100% clean as you state but it works. The problem is when combined with my highlight code and saveas stuff, that is when I have issues. I will play with it again tomm. I have been working on this since 712am est and it is now 610pm est. I am a SAS programmer, not VB

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Copy the code again. I edited it to fix that, but got must have posted the update after you copied it down.

    Re the combined stuff, let's make sure this is completely clean first. Those two issues shouldn't be related.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Oh awesome. That works perfect and does not error when I have under the workbook open event the password coding.

    Now my next issue is when I click save, the blue disk, it will saveas the new file just fine but then MS will stop and say MS Excel has stopped working, windows can try and recover your information and restart the program. Then I can restart the program and it will take a few minutes to restart. I am sure I should not be getting this error when doing this.

  10. #10
    Quote Originally Posted by tinamiller1 View Post
    No, that does not work. I want cell B67 to allow input if cell G9 is selected with the AIP and if PPR is selected then B69 allows user input and cell B67 does not. This works for me:
    My suggestion should work in that situation, needs two DVS along the lines I suggested not 1, but should work.

Posting Permissions

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