worksheet_change event error

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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...
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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:

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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?
 

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top