How can I suppress "ListBox_Change" event triggered by "Cells.Clear" method?

Vote count:

0

I have a List Box with list style as radio button and Row source set to a dynamic data range.

With SearchActionwordForm.SearchResultListBox
.ColumnCount = LastColumn - 1
.ListStyle = fmListStyleOption
.RowSource = DataRange
.Selected(0) = True
End With

ListBox value is populated based on some text entered by user. Each time user enters new search text, the data range will be changed. I have a code to clear the whole sheet before writing new data to sheet.

Set ExcelAppl = CreateObject("Excel.Application")
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
Set ws = wb.Worksheets(1)
ws.Cells.Clear
Dim rng As Range
'code to fetch data from Recordset
Set rng = ws.Range("A1")
lFieldCount = rs.Fields.Count

'copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset rs

'Get the Last Column Number
Dim rLastCell As Range
Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
LastColumn = rLastCell.Column
LastColAdd = rLastCell.Address
LastCellCol = ExtractLetter(LastColAdd)
Set rLastCell = Nothing

'Get the Last Cell Address
Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
LastColumnAddress = rLastCell.Address
LastCellRow = ExtractNumber(LastColumnAddress)
LastColumnAddress = LastCellCol & LastCellRow
DataRange = "B2:" & LastColumnAddress

'Write headers to excel
For i = 0 To LastColumn - 1
 rng.Offset(0, i).Value = rs.Fields(i).Name
Next i

wb.Save

But very strangely "Ws.cells.clear" method trigger "ListBox_Click" event that is supposed to be triggered when I click on the listbox. Is this possible to suppress this event for this particular scenario?

asked 20 secs ago

Raj Kamal

29

How can I suppress "ListBox_Change" event triggered by "Cells.Clear" method?