Using a Userform instead of Data Validation

[Knut Dahl]

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

 

[Guest]

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]

Thanks Toppers,
when you say into the worksheet, do you mean the worksheet of the workbook
where the macro is?