I have two Listbox on a sheet I can get both to move as user selects a cell from either range rng1 or rng2 from here: Moving Shapes as user scrolls right in Excel(VBA)
I want both to move simultaneously when a user selects a cell in either rng1 or rng2
But I can't get the If statement right
Thanks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh As Shape, r As Range, rng1 As Range, rng2 As Range
'Range is Column E
Set rng1 = GetRange("Connections", "A", "E", "E", 2, False)
'Range is Column G
Set rng2 = GetRange("Connections", "A", "G", "G", 2, False)
If InRange(ActiveCell, rng1) = True Then
Set sh = ActiveSheet.Shapes("listbox1")
Set r = ActiveCell
sh.Top = r.Offset(0, 1).Top
sh.Left = r.Offset(0, 1).Left
ElseIf InRange(ActiveCell, rng2) = True Then
Set sh = ActiveSheet.Shapes("listbox2")
Set r = ActiveCell
sh.Top = r.Offset(0, 1).Top
sh.Left = r.Offset(0, 1).Left
End If
End Sub
And
Function InRange(Range1 As Range, Range2 As Range) As Boolean
'returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function
Aucun commentaire:
Enregistrer un commentaire