Excel VBA event trigger problem

[Milli]

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

 

[DSC]

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

 

[Ron de Bruin]

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

 

[Milli]

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

*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

Attachment filename: event_trigger_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52264

 

[Milli]

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

Mill