selecting an offset range on a different sheet

[andrewb90]

Hello all,

I'm trying to select a range on my sheet "Master Availability" based on my selection in the sheet "Employees"

When my code runs and the active cell in Employees is C6, then I want to select the cells E89:R89 (offset by 83 rows) so that I can call an additional sub to run on those cells, I'm just having trouble making it select those cells properly.

Any help would be greatly appreciated.

 

[My Aswer Is This]

You said:
When my code runs and the active cell in Employees is C6

What happens if the active cell is C12

So in your example the script would select Range("E172:R172")

 

[NARAYANK991]

Hi ,

Something like this :

           Dim activecelladd As String
           Worksheets("Employees").Activate
           activecelladd = ActiveCell.Address
           Worksheets("Master Availability").Activate
           ActiveSheet.Range(activecelladd).Offset(83, 2).Resize(1, 14).Select

 

[andrewb90]

You said:
When my code runs and the active cell in Employees is C6

What happens if the active cell is C12

So in your example the script would select Range("E172:R172")

The range would be E95:R95. The row is offset by 83. so whatever row is selected. I would add 83, and select E:R on the other sheet.

 

[My Aswer Is This]

This will do what you want:
But you should know to do things with Excel Vba it is not necessary to select ranges.
But not sure why you want to select this range.

Sub Select_Column()
 Dim ans As Long
 Sheets("Employees").Activate
 ans = ActiveCell.Row
 Sheets("Master Availability").Activate
 With Sheets("Master Availability")
 Range(.Cells(ans + 83, "E"), .Cells(ans + 83, "R")).Select
 End With
 End Sub

 

[andrewb90]

With that range selected, I'm then calling a sub to run, where normally that sub is run after selecting a cell range then right clicking and selecting the sub from my right click menu

 

[My Aswer Is This]

If it works for you that's great. How do you choose a sub to run from your right click Menu.

 

[andrewb90]

It's a before right click code. The values of the labels are setup on another sheet to allow the user to change what they say, and what each button actually does. There are multiple ranges so that I can assign different right click options to different sections of my sheet.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl


Application.CommandBars("Cell").Reset


If Not Intersect(Target, Range("E89:R106,E108:R125,E127:R144,E146:R163,E194:R207,E165:R167,E169:R169,E171:R171,E173:R173,E175:R175,E177:R177,E179:R179,E181:R181,E183:R183,E185:R185,E187:R187,E189:R189,E191:R191")) Is Nothing Then
    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO"
        .FaceId = 2113
        .Caption = Sheets("Settings").Range("K16") & " " & Sheets("Settings").Range("L16")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_OTHER"
        .FaceId = 1845
        .Caption = Sheets("Settings").Range("K17") & " " & Sheets("Settings").Range("L17")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=3)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_NOTE"
        .FaceId = 916
        .Caption = "CUSTOM NOTES"
        '.Font.Color = vbWhite
        '.Interior.Color = vbBlack
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=4)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_JURY"
        .FaceId = 2131
        .Caption = Sheets("Settings").Range("K18") & " " & Sheets("Settings").Range("L18")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=5)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_MATERNITY"
        .FaceId = 2777
        .Caption = Sheets("Settings").Range("K19") & " " & Sheets("Settings").Range("L19")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=6)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_EVENT"
        .FaceId = 353
        .Caption = Sheets("Settings").Range("K20") & " " & Sheets("Settings").Range("L20")
    End With


    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=7)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_CUSTOM"
        .FaceId = 484
        .Caption = Sheets("Settings").Range("K21") & " " & Sheets("Settings").Range("L21")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=8)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "CLEAR"
        .FaceId = 2087
        .Caption = "*!CLEAR REQUESTS!*"
    End With
    Exit Sub
Else




    


End If


If Not Intersect(Target, Range("D89:D106,D108:D125,D127:D144,D146:D163,D165:D192,D194:D207")) Is Nothing Then
    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    


    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "Repeatsch"
        .FaceId = 346
        .Caption = "Save Repeat Schedule"
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "Clearsch"
        .FaceId = 37
        .Caption = "Clear Scheduling Choice"
    End With
    Exit Sub
Else




End If
Application.CommandBars("Cell").Reset
End Sub

 

[My Aswer Is This]

Very interesting. Glad your getting it to work for you.