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

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

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