dimanche 12 juin 2016

get two listbox to move down page simultaneously

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