Only run code if in column B

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,079
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have the code below in use but something isnt right.

My intention is that when i double click in a cell of column B the code will run BUT should a user double click in another column nothing will happen.

This is what happens.
I double click in column B & the code runs fine like it should.

However if i double click in any other column cell its like ive pressed F2 & now allowing to to make an edit ??
I see the flashing cursor in between the characters as if i was to delete / make a change.

Can you see what ive done wrong.

Thanks


Rich (BB code):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Lastrow As Long
    If ActiveCell.Column = 2 Then
    
    Lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Not Intersect(Target, Range("B8:B" & Lastrow)) Is Nothing Then
        Cancel = True

        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        With Worksheets("MCVIN")
            .Activate
            .Range("F7").Value = Target.Value
          
        End With
    Else
    Exit Sub
    
    End If
    MotorcycleBikeInfo.Show
    End If
    
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
You have not done anything wrong. The behaviour is just doing what you have in File-Options-Advanced-Allow editing directly in cells
If you don't like that setting then remove the tick from the checkbox and 'OK', or if you only want to disable that setting for this worksheet then put Cancel = True right near the top of your double-click code for this worksheet instead of where you have it now.
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,079
Office Version
  1. 2007
Platform
  1. Windows
Thanks.

Cancel = True was used
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad to help. :)
 

Forum statistics

Threads
1,141,483
Messages
5,706,649
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top