Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm with multiple selections
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm with multiple selections
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm with multiple selections
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. "Patrick Simonds" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Chart from Userform ListBox selections | Charts and Charting in Excel | |||
Copy Multiple Selections | Excel Discussion (Misc queries) | |||
Multiple selections | Excel Discussion (Misc queries) | |||
Creating Multiple GIFs from Multiple Range selections -- I need a volunteer to test my code to see why it fails | Excel Programming | |||
Multiple Selections in a Combo Box??? | Excel Programming |