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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Userform instead of Data Validation
Try this - enter into workshheet code not genreal module:
Private Sub workSheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:F")) If Not isect Is Nothing Then UserForm1.Show Else MsgBox "Not in Range E:F" End If End Sub HTH "Knut Dahl" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Userform instead of Data Validation
Thanks Toppers,
when you say into the worksheet, do you mean the worksheet of the workbook where the macro is? "Toppers" wrote in message ... Try this - enter into workshheet code not genreal module: Private Sub workSheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:F")) If Not isect Is Nothing Then UserForm1.Show Else MsgBox "Not in Range E:F" End If End Sub HTH "Knut Dahl" wrote: 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 |
Reply |
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 |