VB: combine macros for do the same thing, different columns hi, i have an exampl

Question

VB: combine macros for do the same thing, different columns

hi, i have an example for macro that lets you do what you want, when you click on a column.

is there a way to have an "OR" (if i got that right), to have 2 different columns activate the same macro. (have been gone a couple of awhiles.. getting back)

i use worker cells as dynamic references to columns (as below).

where L2 is in use, i want to add another column to activate the same macro. this will keep me from needing multiple copies of the same macro.

use any cell reference, EG: L1

where i have:

Set MyRange = Me.RANGE(L2).EntireColumn

i might need something like:

Set MyRange = Me.RANGE(L1).EntireColumn OR Me.RANGE(L2).EntireColumn

- thanks

'Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)
'    Dim L2 As String   'workcell L2 shows: CW:CW
'    L2 = RANGE("L2")

'With Target
'    If .Count > 1 Then Exit Sub
'    Dim MyRange As excel.RANGE

'this vb allows activate a macro on click a cell in specific column.

Set MyRange = Me.RANGE(L2).EntireColumn    '<< PROBLEM LINE
If Target.Cells.Count = 1 Then
    If Target.Row < RANGE(C5).Row Or Cells(ActiveCell.Row, "A").Value = "." Then Exit Sub
    'Application.EnableEvents = False        'EVENTS
    If Not Intersect(Target, MyRange) Is Nothing Then
    
      'Selection.EntireRow.Select        'your / any vb..
      Cells(ActiveCell.Row, L2).Select      'select a cell in a row
    End If
    Application.EnableEvents = True         'EVENTS
End If
'note: i use dynamic work reference cells eg: L2 shows: CW:CW
'cell L2 has: =SUBSTITUTE(SUBSTITUTE(CELL("address",$CW2),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$CW2),"$",""),ROW(),"")

'end with
'end sub

Explanation / Answer

The one line answer you are looking for is:

    Set MyRange = Me.Range(L1 & "," & L2)

If you were doing this "normally" you would write

    Set MyRange = Me.Range("CW:CW,CZ:CZ")

Since you have two strings which contain something like "CW:CW" and "CZ:CZ", you simply need to concatenate those strings together, with a comma in between. The & symbol is used to concatenate (join) strings together. Any text has to be enclosed in " " hence

    L1 & "," & L2

Tidying up your code, removing unnecessary lines, etc. I think you want something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' This runs automatically when a cell in a specific column is selected.
'
    Dim L1 As String, L2 As String, C5 As String
    Dim MyRange As Range
   
    'L2 = Range("L2").Value   'workcell L2 shows: CW:CW
   
   '-------------
    'For testing:
    L2 = "CW:CW"
    L1 = "CZ:CZ"
    C5 = "A5"
   '-------------
   
    If Target.Cells.Count = 1 Then
        If Target.Row < Range(C5).Row Or Cells(ActiveCell.Row, "A").Value = "." Then Exit Sub
       
       'Must disable events if the code below will change which cells
        'are selected, otherwise this procedure will run again!
        On Error GoTo ExitPoint
        Application.EnableEvents = False
       
        Set MyRange = Me.Range(L1 & "," & L2)
        If Not Intersect(Target, MyRange) Is Nothing Then
           
            MsgBox "Your code to run here..."
           
        End If
    End If
ExitPoint:
    Application.EnableEvents = True
End Sub

Note the use of On Error Goto ExitPoint. If your code errors after that line, it will jump to the ExitPoint label and then re-enable events. You only need to disable events if the code that will run will re-trigger this SelectionChange event.

Finally, in the line Set MyRange = Me.Range(L1 & "," & L2) there is no need for .EntireColumn because L1 and L2 already contain references to entire columns (CW:CW). If L1 contained a reference to a single cell, e.g. L1="A1" and L2 = "C1" then you would need to use .EntireColumn:

    Set MyRange = Range("A1,C1").EntireColumn

or
    L1="A1"
    L2 = "C1"

    Set MyRange = Range(L1 & "," & L2).EntireColumn

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.