Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Userform instead of Data Validation
Hi everyone.
I have an overview sheet in a workbook, that compares data. In column A I have a unique order reference number and in column B are the corresponding volumes ordered. Column C is again for the order reference number and column D is the actual invoiced volume. I have a Gain/loss calculation in Column G which basically just does a sum of the preceding volumes. Now I have another 2 columns with additional data in I and J. These are volumes that have been booked with incorrect order references (so ref number in column I and the volume in G). Currently my VBA code adds a data validation to all rows where the gain/loss is not 0 in column E. The user can then choose from the drop-down list and the volume gets updated automatically by a vlookup. This data validation picks the values from the list in columns I and J. Now I wanted to make this whole procedure a bit more elegant. Instead of using the data validation I would like to create a user-form which pops up whenever the user double-clicks in one cell in column E or F. This user-form then takes it's values from the list that is currently in columns I and J. When the user makes his choice, the actual choice will be moved from the list over to the cell that he double-clicked (so the actual choice of ref and volume will be cut from the list and pasted to the cell he double-clicked). Now my question here is: I only want the userform to appear when the user actually clicks in an appropriate cell (in columns E or F) and not when he double-clicks anywhere else in the sheet. What would be the best way to do this? I thought that I might do a check to see if the intersection between the double-clicked cell and range E:F is true. If yes show the userform and if not display a msgbox. But I'm not sure if this is the best way to go. Is there any easier way to define a range where the double-click actually is permitted? Thanks very much in advance for any comments or tips. Kj |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Userform with validation... | Excel Programming | |||
UserForm Date Validation | Excel Programming | |||
Userform Validation | Excel Programming | |||
Userform Date validation | Excel Programming |