On Double Click, List Box and Enter Value

P

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
I have some code that runs when the user double clicks. Upon that double click, a list box is displayed with three columns. I want the user to select a row and press OK. Upon pressing OK, I want the value from Column 1 to be populated into the target cell that was originally double clicked on.

I have everything working except for the getting the value selected and then placing into target.

I've used code like this before to figure out what the user selected:

For n = 0 To (CountListArray - 1)
If ExpenseErrorsListBox.Selected(n) = True Then
ItemSelected = ExpenseErrorsListBox.List(n, 1)
End If
Next n

How would I then, but that value into a cell?

I need some guidance.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
I'm able to get the value from the list box I want stored into a variable. I just can't figure out how to get that value back into the cell that was double clicked.
 
Upvote 0
Maybe something like this:
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Value = "The listbox value"
End Sub
 
Upvote 0
I've tried that. It looks like:

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

If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then

Call ShowSubOpListing
Target.Value = ItemSelected

End If

End Sub

But, it doesn't work. The ItemSelected is a Public variable from the ShowSubOpListing function. The ShowSubOpListing function is how I load and display the list. When the user hits OK, that is when I figure out what they selected and set ItemSelected to that number.
 
Last edited:
Upvote 0
Hmmm, I put a watch on the ItemSelected and it doesn't have a value associated with it. How can I get it to carry from the other function?
 
Upvote 0
ItemSelected declared as a Public variable and defined in ShowSubOpListing should work.

But better is to change ShowSubOpListing to a Function which returns the local ItemSelected value.
 
Upvote 0
I got the value doing what I want now, thanks for your help.

The only issue now is that the List Box pops up, but the workbook somehow has lost focus. So, I can't click on the List Box until I click behind it into any other cell in the sheet. It looks like the cell that was double clicked has the flashing cursor. Is there a way to stop the double click event when the List Box is shown but still have the cell selected (but not with the cursor flashing)?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA Userform List Box to Populate upon entering a specific text in Column B, copy data upon selection and paste data in the same Row in another Column
Replies
3
Views
180
arijitirf
A
M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
214
maiatanaka
M
sparky2205
  • Solved
Set default selections on a List Box on Workbook_Open()
Replies
3
Views
115
Domenic
D
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
127
still learning
S
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
125
Akuini
Akuini

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top