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

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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 Question
ListBox Click to textbox & delete row
Replies
0
Views
607
pure vito
P
M
  • Question Question
Make possible set of 5 numbers using from 3 or 4 groups
Replies
9
Views
735
motilulla
M
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs
K
  • Question Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
517
kcampbell675
K
D
  • Question 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
466
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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