Would it be possible use double clicking on cell for following 3 subs?

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
Office Version
  1. 2010
Hello,</SPAN></SPAN>

For now using 3 different worksheet functions to select entire C:H columns for 3 different targets cells "1st for L:V", "2nd for AB:AL" and "3rd for AO:AY"</SPAN></SPAN>

Would it be possible use " Worksheet_BeforeDoubleClick" for all targets? </SPAN></SPAN>

Data looks as shown below</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1
2
3
4
5n1n2n3n4n5n6R1R2R3R4R5R6R1R2R3R4R5R6R1R2R3R4R5R6
60111210111-110111-110111-11
7003200000000000000000000
8014321-3113-31-3113-31-3113-31
9225042-4222-42-4222-42-4222-42
10306053-3131-33-3131-33-3131-33
11020070-4020-42-4020-42-4020-42
12031281-513-1-33-513-1-33-513-1-33
13100490-402-2-22-402-2-22-402-2-22
14211600-313-3-31-313-3-31-313-3-31
15300711-202-2-22-202-2-22-202-2-22
16010923-311-3-11-311-3-11-311-3-11
171201044-220-2-22-220-2-22-220-2-22
182311150-131-1-11-131-1-11-131-1-11
19042001-242-2-22-242-2-22-242-2-22
20153122-153-1-33-153-1-33-153-1-33
21264343064-2-44064-2-44064-2-44
22470450-173-1-33-173-1-33-173-1-33
235815620840-220840-220840-22
24092773-195-1-13-195-1-13-195-1-13
25110380401060-2401060-2401060-24
260114916-11171-13-11171-13-11171-13
27113000001060-2201060-2201060-22
2821412121117-1-111117-1-111117-1-11
2931524332128-2-222128-2-222128-2-22
3041630443139-3-133139-3-133139-3-13
31517405541410-40441410-40441410-404
3260517651311-3-1551311-3-1551311-3-15
3382620741212-2-2641212-2-2641212-2-26
3490741851113-3-1751113-3-1751113-3-17
35111853041214-2-2641214-2-2641214-2-26
36120074151113-3-1751113-3-1751113-3-17
Sheet2


Code: 
[COLOR=#ff0000]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
[/COLOR]If Target.Column < 12 Or Target.Column > 22 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 7) / 2) + (j - 7) Mod 2
Union(Columns(k), Columns(Target.Column)).Select
Cancel = True
End Sub

[COLOR=#006400]Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
[/COLOR]If Target.Column < 28 Or Target.Column > 38 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 22) / 2) + (j - 22) Mod 2
Union(Columns(k), Columns(Target.Column)).Select
Cancel = True
End Sub

[COLOR=#0000cd]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[/COLOR]If Target.Column < 41 Or Target.Column > 51 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 36) / 2) + (j - 36) Mod 2
Union(Columns(k), Columns(Target.Column)).Select
Cancel = True
End Sub

Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim j As Long
   Dim k As Long
   
   j = Target.Column
   If Target.Column > 11 And Target.Column < 23 Then
      k = Int((j - 7) / 2) + (j - 7) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   ElseIf Target.Column > 27 And Target.Column < 39 Then
      k = Int((j - 22) / 2) + (j - 22) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   ElseIf Target.Column > 40 And Target.Column < 52 Then
      k = Int((j - 36) / 2) + (j - 36) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   End If
End Sub
 
Upvote 0
Fluff said:
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim j As Long
   Dim k As Long
   
   j = Target.Column
   If Target.Column > 11 And Target.Column < 23 Then
      k = Int((j - 7) / 2) + (j - 7) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   ElseIf Target.Column > 27 And Target.Column < 39 Then
      k = Int((j - 22) / 2) + (j - 22) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   ElseIf Target.Column > 40 And Target.Column < 52 Then
      k = Int((j - 36) / 2) + (j - 36) Mod 2
      Union(Columns(k), Columns(Target.Column)).Select
      Cancel = True
   End If
End Sub
Click to expand...
Fluff, :pray: amazed by your coding it worked 100% as I wanted</SPAN></SPAN>

Thank you for taking the time to help me out. </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti :)</SPAN></SPAN>
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
326
pure vito
P
M
  • Question
Make possible set of 5 numbers using from 3 or 4 groups
Replies
9
Views
557
motilulla
M
K
  • Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
345
kcampbell675
K
D
  • Question
Data in cell wrong way round ie "15.22=1|13.33=3" should be "1=15.22|3=13.33" - must finish with data in 1 cell
Replies
3
Views
305
Fluff
Fluff
V
  • Solved
Error Before Double Click function
Replies
8
Views
381
Vincent88
V

Forum statistics

Threads
1,214,940
Messages
6,122,356
Members
449,080
Latest member
Armadillos

Share this page

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