Convert event double click code to simple code

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,055
Office Version
  1. 365
Platform
  1. Windows
A couple of years ago I constructed this code with the help of Mrexcel;

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim WasProtected As Boolean
    Dim response As VbMsgBoxResult
    Dim adjust As Integer
    
    On Error GoTo myhandler
    
    WasProtected = CBool(Me.ProtectContents)
    
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    If Target.Row = 4 Then
        
        If Target.Column > 11 And Target.Column < 42 Then
            
            response = MsgBox("This will copy yesterday's data over to today - confirm ok?", vbYesNo, "Copy Yesterday's Data")
            adjust = -1
        ElseIf Target.Column = 11 Then
            
            response = MsgBox("This will copy the last day's data over to the first day, AND CLEAR DAYS 2 ONWARDS - confirm ok?", vbYesNo, "MONTH END CARRY FORWARD")
            adjust = (Range("D3") - 1)
        End If
        
    End If
    
    If response = vbYes Then
        Application.ScreenUpdating = False
        If WasProtected Then Me.Unprotect Password:="***"
        Target.Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn
        
    If Target.Column = 11 Then
        Range("L6:AO" & lastrow).ClearContents
    End If
           
        Me.Range("A7").Select
        If WasProtected Then Me.protect Password:="***"
    End If
    
myhandler:
    Application.ScreenUpdating = True
End Sub

Double clicking on row 4 between columns K and AO triggered a copy/paste/clear routine. Works great.

However I now want to extract part of this to use in another bit of code, not triggered by the double click.

I am only interested in the bit that refers to target column 11 - not the >11 and <42 option

I'm also not interested in the message boxes, I just want to extract the basic code to copy/paste/clear.

If it helps D3 contains the number of days in a month, columns 11-42 days of the month 1-31

TIA

I've tried various versions and just cant get it to work!!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi,
This is just a guess but see if below goes in right direction

I have taken code I think you want & created separate procedure

Code:
 Sub CopyColumn11(ByVal Target As Range)    
     Dim adjust As Integer
    Dim LastRow As Long
    
    If Not Target.Column = 11 Then Exit Sub
    
    adjust = Target.Parent.Range("D3").Value - 1
    Target.Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn
        
    With Target.Parent
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("L6:AO" & LastRow).ClearContents
    End With
    
End Sub

Call it by passing the range to it

Code:
 CopyColumn11 Range("K20")


Suggestion untested & you should adjust as required to meet specific project need.
If suggestion does not meet your requirement then post your attempts so board can get better understanding of what trying to do – plenty here to offer assistance.

Dave
 
Last edited:
Upvote 0
Many thanks, with your suggestion and a little trial and error this seems to have done the trick;

Code:
Dim adjust As Integer
     
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
adjust = (Range("D3") - 1)
            
Range("K4").Select
Range("K4").Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn

Range("L6:AO" & lastrow).ClearContents
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top