I'm building an Excel VBA questionnaire where I use List Box (in forms
to collect answers on questions. My intention is to create a routin
that enters the date and time in a cell when an item is selected in th
ListBox. I've tried:
Private Sub Worksheet_Change(ByVal Target As Range) and OnEntry.

These work perfect as long as I manually enter a value in a cell or us
a varible to insert a value in a cell. However it does not react to
cell being updated with a value as a reaction of a selection in th
ListBox.

Has anybody got a remedy?

Mill

--
Message posted from http://www.ExcelForum.com

Hi Milli

Listboxes have also events.
Run your code in one of this events

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Milli " wrote in message ...
I'm building an Excel VBA questionnaire where I use List Box (in forms)
to collect answers on questions. My intention is to create a routine
that enters the date and time in a cell when an item is selected in the
ListBox. I've tried:
Private Sub Worksheet_Change(ByVal Target As Range) and OnEntry.

These work perfect as long as I manually enter a value in a cell or use
a varible to insert a value in a cell. However it does not react to a
cell being updated with a value as a reaction of a selection in the
ListBox.

Has anybody got a remedy?

Milli


---
Message posted from http://www.ExcelForum.com/

Thanks, that helps me to understand my problem. However:
I produce the questionnaires including ListBoxes from a master routine
so the different questionnaires dosen't really contain any code at all
There's a total of about 1000 questions in a dozen workbooks and som
100 worksheets. So, I don't know the name of the different ListBoxes.
need to find the address of the LinkedCell of the ListBox that i
currently activated, then I can just add a macro to the ListBox.
I can't find a way to read the LinkedCell of the current ListBox into
variable!
I've attached a simplified file that shows my problem.

Milli

Ron de Bruin wrote:
*Hi Milli

Listboxes have also events.
Run your code in one of this events

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Milli " wrote in messag
...
I'm building an Excel VBA questionnaire where I use List Box (i
forms)
to collect answers on questions. My intention is to create
routine
that enters the date and time in a cell when an item is selected i
the
ListBox. I've tried:
Private Sub Worksheet_Change(ByVal Target As Range) and OnEntry.

These work perfect as long as I manually enter a value in a cell o
use
a varible to insert a value in a cell. However it does not react t
a
cell being updated with a value as a reaction of a selection i
the
ListBox.

Has anybody got a remedy?

Milli


---
Message posted from http://www.ExcelForum.com/


Attachment filename: event_trigger_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52264
--
Message posted from http://www.ExcelForum.com

I have solved my problem. I used Application.Caller, looks somethin
like this:
strX = Application.Caller
vntY = DocHasShapes.Shapes(strX).ControlFormat.LinkedCell

Mill

--
Message posted from http://www.ExcelForum.com

How about using the Listbox_Click Event on the Worksheet
you could then run the Subroutine through this event.

i.e. if you already have an instance of Worksheet_Change then
you could add into the Listbox_Click Event

something Like

Private Sub ListBox1_Click()
MyText = ListBox1.Value
Range("A1").Value = MyText
Call Worksheet_Change(Range("A2"))
End Sub

Hope this helps

Davi

--
Message posted from http://www.ExcelForum.com