RE: Using a Userform instead of Data Validation

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 *** 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 ***. 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
>
>
>
.

Quantcast