Excel - Worksheet beforedoublick range question 1

[RamziSaab]

i need to set the range for the beforedoubleclick funtion which is basically the columns 2,5,8,11,14 etc.

I am not sure how i can specify this?

 

[Zathras]

This should do what you are asking for:
[blue]

Option Explicit

Sub test()
  SelectAlternateColumns 2, 40, 3
End Sub

Sub SelectAlternateColumns(FirstColumn As Integer, _
     LastColumn As Integer, Spacing As Integer)
Dim rng As Range
Dim i As Integer
  Set rng = Columns(FirstColumn)
  For i = FirstColumn To LastColumn Step Spacing
    Set rng = Union(rng, Columns(i))
  Next i
  rng.Select
End Sub

[/color]

 

[RamziSaab]

Sub SelectAlternateColumns(FirstColumn As Integer, _
LastColumn As Integer, Spacing As Integer)
Dim rng As Range
Dim i As Integer
Set rng = Columns(FirstColumn)
For i = FirstColumn To LastColumn Step Spacing
Set rng = Union(rng, Columns(i))
Next i
rng.Select
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Long

If Target.Column = SelectAlternateColumns(2, 120, 3) Then



this is what i have but it gives me an error, i dont want to select them, i was want to specify them as the range in that worksheet (i.e. if i double click on column 1,3,4 etc. nothing happens!)

 

[Zathras]

It would be easy to re-write the Sub as a function and have it return a range, but for what you are describing all you need to do is test the column number for the Target range. Something like this:
[blue]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
  If Target.Column Mod 3 = 2 Then
    MsgBox "One of the columns 2,5,8,11,... was double-clicked"
  Else
    MsgBox Target.Address & " is not in one of the columns we want."
End If
End Sub

[/color]
Generally, you will get better answers here if you describe your problem, not just ask for how to implement what you think is a solution to a problem.