UserForm with multiple selections

[Patrick Simonds]

I use the code below to call a UserForm when I click on the appropriate
cell, and it works fine, but end users are never happy. They want to be able
to select either just one cell, say B5 or a combination of cells such as B5
E6 and H9 and have the UserForm data placed in all selected cells.

If I select only one cell everything is fine, the UserForm comes up, but if
I hold down the control key and select multiple cells the UserForm does not
come up. I can select the first cell, have the UserForm come up, move the
UserForm out of the way and then hold the control key and select the cells,
and then drag the UserForm back into place. But this is very cumbersome and
not popular with the end users.


Any ideas?


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If Union(Target, Range("B5:B10")).Address = Range("B5:B10").Address Then
EmployeeList.Show
If Union(Target, Range("E5:E10")).Address = Range("E5:E10").Address Then
EmployeeList.Show
If Union(Target, Range("H5:H10")).Address = Range("H5:H10").Address Then
EmployeeList.Show

If Union(Target, Range("B13:B19")).Address = Range("B13:B19").Address Then
EmployeeList.Show
If Union(Target, Range("E13:E19")).Address = Range("E13:E19").Address Then
EmployeeList.Show
If Union(Target, Range("H13:H19")).Address = Range("H13:H19").Address Then
EmployeeList.Show


End Sub

 

[Tom Ogilvy]

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If Union(cell, Sh.Range("B5:B10,E5:E10,H5:H10" & _
",B13:B19,E13:E19,H13:H19")).Address <> _
Sh.Range("B5:B10,E5:E10,H5:H10,B13:B19" & _
",E13:E19,H13:H19").Address Then
Unload EmployeeList
Exit Sub
End If
Next
EmployeeList.Show vbModeless
End Sub


Would be a start. When you make your first selection, the userform is going
to come up - how is the event going to decipher your intent to select more
than one cell.

You can position the userform out of the way.
http://www.cpearson.com/excel/FormPosition.htm

 

[Guest]

Hi Patrick,
How about using the Worksheet_BeforeRightClick event

if <all the condition bbased on multiple ranges> then
Cancel=true
EmployeeList.Show
End if

This works because the event does not fire till you do a RightClick.