SelectionChange fail to work sometimes

[JackkG]

Hi,


I wonder what's wrong with this code. Whenever i click on the cell range the list should populate in listbox. But sometimes the SelectionChange event of worksheet works fine but sometimes it fails to work. So, I need to close the workbook and open it again in order for it to work. Can anyone explain what's wrong with this, or is there an alternative for SelectionChange? Here is the code used in worksheet.


Please help!!


-------------------

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrow As Long
Application.EnableEvents = False
Lastrow = Cells(Rows.Count, 2).End(xlUp).Row
If Selection.Count = 1 Then
If Not Intersect(Target, Range("A4:A" & Lastrow)) Is Nothing Then
Author = Target.Value
authorclick
End If
End If
Application.EnableEvents = True
End Sub

[Aussiebear]

After having a quick look, my first thoughts are that there appears to be a conflict between the last row criteria and the target range criteria. Perhaps I'm wrong but the last row starts at row 2 whilst the target range starts at row 4.

[mikerickson]

Cross posted
http://www.mrexcel.com/forum/excel-questions/832701-selectionchange-event-not-working.html
http://www.excelforum.com/excel-programming-vba-macros/1063757-why-selectionchange-doesnt-work.html

The thing that merits more than a simple notification of cross-posting is that in both threads the OP has been asked to post the code for authorclick and they have responded to neither request.

BTW, if the OP wants to know why cross posting is annoying, please read this http://www.excelguru.ca/content.php?184

[Aussiebear]

Good pickup Mike.

[JackkG]

Hi Aussiebear,

Column A contains Author names, so it starts from row 5, row 4 contains headers, row 1-3 are input box and command button. So, the author list starts from row 5. So, when i click on any author name cell in column A, it displays the details of that particular author in listbox, the details of that particular author is listed in List sheet. I can accomplish this with SelectionChange, but sometimes it doesn't work. So looking for any alternative for it or any change in code.


Thanks

[Aussiebear]

Sorry JackkG but it seems you like crossposting without notifying other forums, so I won't be assisting you in this thread

[JackkG]

Oh sorry for this, but I dunno how to inform other forums about crossposting?? Pls help..