By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,234 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,234 IT Pros & Developers. It's quick & easy.

VBA Excel escaping edit mode not working

P: 1
Hi all,

I'm a bit of a VBA newb, but I'm trying to write a small function in a spreadsheet that will execute code on double-clicking a cell and then escape straight out of the cell edit mode. I gather that this is to be done using Cancel = True, which I have tried, but this does not stop the cell going into edit mode. Here is the code so far:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  2.  
  3. Dim rng1 As Range
  4. Set rng1 = Range("D3:E198")
  5.  
  6.  
  7. If Not Intersect(Target, rng1) Is Nothing Then
  8.  
  9.     If Target.Value = "No" Then
  10.         Target.Value = "Yes"
  11.         Target.Interior.Color = RGB(0, 255, 0)
  12.  
  13.         Cancel = True
  14.  
  15.     ElseIf Target.Value = "Yes" Then
  16.         Target.Value = "No"
  17.         Target.Interior.Color = RGB(255, 0, 0)
  18.  
  19.         Cancel = True
  20.  
  21.     End If
  22.  
  23. End If
  24.  
  25.  
  26.  
  27. End Sub
Any idea why this isn't working?

Thanks,

Simt33
Jun 20 '13 #1
Share this Question
Share on Google+
1 Reply


100+
P: 759
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  2.  
  3. Dim rng1 As Range
  4.     Set rng1 = Range("D3:E198")
  5.  
  6.     If Intersect(Target, rng1) Is Nothing Then
  7.         Exit Sub
  8.     End If
  9.  
  10.     If Target.Value = "No" Then
  11.         Target.Value = "Yes"
  12.         Target.Interior.Color = RGB(0, 255, 0)
  13.      ElseIf Target.Value = "Yes" Then
  14.         Target.Value = "No"
  15.         Target.Interior.Color = RGB(255, 0, 0)
  16.      End If
  17.  
  18.      Cancel = True
  19.  
  20. End Sub
Jun 20 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.