Before Double Click Event

[gregork]

Hi all,
I have a BeforeDoubleClick on a worksheet that activates a user form for
entering data on to a sheet. What I want is for the value of the cell that I
double clicked to be entered in to a Combo Box (ComboBox1) on my userform.
If it is possible to write a code for this I would greatly appreciate some
help with the code.

Regards
gregork

 

[Rob van Gelder]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim frm As UserForm1

Set frm = New UserForm1
frm.ComboBox1.AddItem Target.Value
frm.Show

Set frm = Nothing
End Sub

 

[gregork]

Many thanks for the reply Rob. I pasted the code in to my worksheet event
and the userform shows when I double click but the cell value is not
transposed in to combobox1.
Any idea where I might be going wrong on this one?

Kind Regards
gregork
p.s -are you a kiwi?

 

[Rob van Gelder]

It may not be immediately visible until you use the Combo dropdown.
This can be tweaked by doing something with the ListIndex property.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim frm As UserForm1

Set frm = New UserForm1
frm.ComboBox1.AddItem Target.Value
With frm.ComboBox1: .ListIndex = .ListCount - 1: End With
frm.Show

Set frm = Nothing
End Sub


Another possibility is that you've got a ControlSource properties set.

The steps I went through:
1. New Workbook
2. Copy the event code to Sheet1
3. New UserForm (UserForm1)
4. New ComboBox on UserForm1 (ComboBox1)
5. Put some data into a cell, then doubleclick it.


Yep, I'm a Kiwi! Living in Whangarei (at least, just outside Whangarei)

 

[gregork]

Thanks heaps Rob, the new code works perfectly. Good to know I can tap in to
some good old 'Kiwi ingenuity' on this newsgroup when I need it ;-)

Kind Regards
gregork