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
--
Regards,
Tom Ogilvy
"Patrick Simonds" wrote in message
...
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