Select Case problem when hitting the Delete key
 

Below is my code. Basically, when the user selects "Final Decision in
any of the cells in range g30:g54, a userform pops up and they select
Approved or Denied. This places that in the cell to the right of that
field. The problem is that sometimes this will change from Final
Decision to something else and then I want that Approved or Denied
removed. Using the worksheet change function works great until I hit
"Delete". When I delete the value from g30 I get a Type Mismatch
error. I'm assuming it's looking for a value and there isn't one
there. If the value is deleted, I want the cell to the right to be
cleared.

Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("AppendPermit").Unprotect
Application.EnableEvents = False

' Check cell A30
' If the cell is empty, leave it alone and don't check
If Range("A30") = "" Then
GoTo Fin
End If

' If cell A30 equals anything other than "Maint.", then display an
error
' empty the cell and place the cursor back at that cell
If Range("A30").Value < "Maint." Then
MsgBox ("All permits originate from the Maintenance level.
Please enter 'Maint.'"), vbInformation, "Ruh roh. Wrong value."
Range("A30").Value = "Maint."
Range("A30").Select
GoTo Fin
End If

If Application.Intersect(Target, Range("g30:j54")) Is Nothing Then
GoTo Fin
Else
With Sheets("AppendPermit").Range("g30:j54")
Select Case Target.Value

Case ""
Target.Offset(0, 1).Value = ""

Case "Final Decision"
If Target.Offset(0, 1).Value = "" Then
Decision.Show
Target.Offset(0, 1).Value = Sheets("Lists").Range("Q7")
End If

Case Else
Target.Offset(0, 1).Value = ""
End Select
End With
End If

Fin:
Application.ScreenUpdating = True
ActiveWorkbook.Sheets("AppendPermit").Protect
Application.EnableEvents = True
End Sub

Select Case problem when hitting the Delete key
 

On Oct 14, 1:38*pm, gab1972 wrote:
Below is my code. *Basically, when the user selects "Final Decision in
any of the cells in range g30:g54, a userform pops up and they select
Approved or Denied. *This places that in the cell to the right of that
field. *The problem is that sometimes this will change from Final
Decision to something else and then I want that Approved or Denied
removed. *Using the worksheet change function works great until I hit
"Delete". *When I delete the value from g30 I get a Type Mismatch
error. *I'm assuming it's looking for a value and there isn't one
there. *If the value is deleted, I want the cell to the right to be
cleared.

Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("AppendPermit").Unprotect
Application.EnableEvents = False

' Check cell A30
' If the cell is empty, leave it alone and don't check
* * If Range("A30") = "" Then
* * * * GoTo Fin
* * End If

' If cell A30 equals anything other than "Maint.", then display an
error
' empty the cell and place the cursor back at that cell
* * If Range("A30").Value < "Maint." Then
* * * * MsgBox ("All permits originate from the Maintenance level..
Please enter 'Maint.'"), vbInformation, "Ruh roh. *Wrong value."
* * * * Range("A30").Value = "Maint."
* * * * Range("A30").Select
* * * * GoTo Fin
* * End If

If Application.Intersect(Target, Range("g30:j54")) Is Nothing Then
GoTo Fin
Else
* * With Sheets("AppendPermit").Range("g30:j54")
* * Select Case Target.Value

* * Case ""
* * Target.Offset(0, 1).Value = ""

* * Case "Final Decision"
* * If Target.Offset(0, 1).Value = "" Then
* * * * * * Decision.Show
* * * * * * Target.Offset(0, 1).Value = Sheets("Lists").Range("Q7")
* * End If

* * Case Else
* * * * Target.Offset(0, 1).Value = ""
* * End Select
* * End With
End If

Fin:
Application.ScreenUpdating = True
ActiveWorkbook.Sheets("AppendPermit").Protect
Application.EnableEvents = True
End Sub

And once again, I find and answer to my own problem...

Here's what I came up with:

On Error GoTo ErrorTrap '<---- added this
If Intersect(Target, Range("g30:j54")) Is Nothing Then
GoTo Fin
Else
.....
.....
End If
GoTo Fin
ErrorTrap: '<--- and this
Target.Offset(0, 1).Value = ""
....
....

Select Case problem when hitting the Delete key
 

Hi

Always remeber to tell which line is causing the error (which line is
highlighted when you click Debug).

I don't see what you are using the 'with...end with' statemnt for..

Rewrote your code a bit to get rid of goto statements:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

ActiveWorkbook.Sheets("AppendPermit").Unprotect

' Check cell A30
' If the cell is empty, leave it alone and don't check
If Not Range("A30") = "" Then
' If cell A30 equals anything other than "Maint.", then display an error
' empty the cell and place the cursor back at that cell
If Range("A30").Value < "Maint." Then
MsgBox ("All permits originate from the Maintenance level. Please
enter 'Maint.'"), vbInformation, "Ruh roh. Wrong value."
Range("A30").Value = "Maint."
Range("A30").Select
End If
ElseIf Not Application.Intersect(Target, Range("g30:j54")) Is Nothing Then
'With Sheets("AppendPermit").Range("g30:j54")
Select Case Target.Value
Case ""
Target.Offset(0, 1).Value = ""
Case "Final Decision"
If Target.Offset(0, 1).Value = "" Then
Decision.Show
Target.Offset(0, 1).Value = Sheets("Lists").Range("Q7")
End If
Case Else
Target.Offset(0, 1).Value = ""
End Select
'End With
End If

Application.ScreenUpdating = True
ActiveWorkbook.Sheets("AppendPermit").Protect
Application.EnableEvents = True
End Sub

Regards,
Per

"gab1972" skrev i meddelelsen
...
On Oct 14, 1:38 pm, gab1972 wrote:
Below is my code. Basically, when the user selects "Final Decision in
any of the cells in range g30:g54, a userform pops up and they select
Approved or Denied. This places that in the cell to the right of that
field. The problem is that sometimes this will change from Final
Decision to something else and then I want that Approved or Denied
removed. Using the worksheet change function works great until I hit
"Delete". When I delete the value from g30 I get a Type Mismatch
error. I'm assuming it's looking for a value and there isn't one
there. If the value is deleted, I want the cell to the right to be
cleared.

Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("AppendPermit").Unprotect
Application.EnableEvents = False

' Check cell A30
' If the cell is empty, leave it alone and don't check
If Range("A30") = "" Then
GoTo Fin
End If

' If cell A30 equals anything other than "Maint.", then display an
error
' empty the cell and place the cursor back at that cell
If Range("A30").Value < "Maint." Then
MsgBox ("All permits originate from the Maintenance level.
Please enter 'Maint.'"), vbInformation, "Ruh roh. Wrong value."
Range("A30").Value = "Maint."
Range("A30").Select
GoTo Fin
End If

If Application.Intersect(Target, Range("g30:j54")) Is Nothing Then
GoTo Fin
Else
With Sheets("AppendPermit").Range("g30:j54")
Select Case Target.Value

Case ""
Target.Offset(0, 1).Value = ""

Case "Final Decision"
If Target.Offset(0, 1).Value = "" Then
Decision.Show
Target.Offset(0, 1).Value = Sheets("Lists").Range("Q7")
End If

Case Else
Target.Offset(0, 1).Value = ""
End Select
End With
End If

Fin:
Application.ScreenUpdating = True
ActiveWorkbook.Sheets("AppendPermit").Protect
Application.EnableEvents = True
End Sub

And once again, I find and answer to my own problem...

Here's what I came up with:

On Error GoTo ErrorTrap '<---- added this
If Intersect(Target, Range("g30:j54")) Is Nothing Then
GoTo Fin
Else
.....
.....
End If
GoTo Fin
ErrorTrap: '<--- and this
Target.Offset(0, 1).Value = ""
....
....