excel-vba,activex , ActiveX TextBox_Change with ListBox_Click then clear TextBox causes recursive behavior

Question:

Tag: excel-vba,activex

I have an ActiveX TextBox with TextBox_Click code behind it such that each character I type triggers another sub to execute which then updates a table by updating a named cell that as a formula in it which drives the table. Then I have a ListBox that has ListFillRange linked to that table. The result is each character I type in the TextBox causes the ListBox to update. This is working fine.

The next thing I put in place was ListBox_Click code so that when I click on an item in the ListBox it updates a Label.Caption then sets ListBox.Value="". This is also working fine.

What I would like to now is have the TextBox clear after the Label.Caption is assigned. The problem is the TextBox clear causes the ListBox to update and the Label then gets the wrong value. This seems odd because the command assign the caption occurs before setting the TextBox = "" which resets the ListBox. But it doesnt seem to matter that the caption assignment occurrs first. The ListBox seems still selected so the update of the TextBox updates the ListBox so the Label gets the wrong value.

Any thoughts are greatly appreciated.

This is in the Sheet's module

Private Sub TextBox5_Change()

Call UpdateValues(TextBox5.Value)

End Sub


This is in a general module

Sub UpdateValues(SearchString As String)
Range("mySearchString").Value = SearchString
Range("myStartPosition").Value = Range("myOverwriteProtection").Value
End Sub


This is in the Sheet's module

Private Sub ListBox1_Click()

ActiveSheet.OLEObjects("Label33").Object.Caption = ActiveSheet.OLEObjects("ListBox1").Object.Value

ActiveSheet.OLEObjects("ListBox1").Object.Value = ""

ActiveSheet.OLEObjects("TextBox5").Object.Text = ""

End Sub


There are two options really.

1. Don't use ListFillRange. Unless you need column headers, there is no need to use this property - you're already using code, so you can repopulate the list in code using code like:

control_name.List = Sheets("Sheet name").Range("some range").Value

2. Use a Boolean flag to stop the event. As the first line of each event, use:

If flag_variable then exit sub

Then whenever you need to do something and don't want to run control events, simply set the variable to True, perform your actions and then set it back to False. (it's a bit like using Application.EnableEvents, which doesn't work for ActiveX control events)

Related:

Date formatting - making dd/m/yyyy into dd/mm/yyyy

excel-vba,date,format
I got a pretty simple question (but yet I've been stuck at it for some time now). Does anyone know how to make the date value from dd/m/yyyy into dd/mm/yyyy in a variable? dim lastdaylastmonth as date lastdaylastmonth = DateSerial(Year(Date), Month(Date), 0) So this code, as of now, would return...

VBA Debugger shows only 256 elements of a collection

excel-vba,debugging,collections
the title explains my problem. Im working on an excelmakro and my questions is, is there an option that the debugger shows all elements like it does with an array? And if not, is the only workaround the storage of my classobjects in an array after inflating the collection? I...

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Doing a same action to all the subfolders in a folder

excel,excel-vba
Given below code converts all xlsx files inside "C:\Files\Bangalore" to csv files. Sub xlsxTOcsv() Dim sPathInp As String Dim sPathOut As String Dim sFile As String sPathInp = "C:\Files\Bangalore\" sPathOut = "C:\Files\Bangalore" Application.DisplayAlerts = False sFile = Dir(sPathInp & "*.xlsx") Do While Len(sFile) With Workbooks.Open(fileName:=sPathInp & sFile) .SaveAs fileName:=sPathOut &...

Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba
All I want to do is copy a range from Excel and then paste this range into PowerPoint. When my range is manually copied from Excel to the clipboard... If I right click on a blank slide when pasting into PowerPoint, it gives me the option to paste "using destination...

Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting
I have 2 worksheets with the same headers in different orders. Headers are I.D, Name, Department, Sales, Start date, End Date and a few others. What I am aiming to do is search through the workbooks in which the headers may be in different orders, find the column which has...

Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format
I am in process of creating a macro that will save the current workbook, create a new outlook message and attach the file to the message. My macro does that but I can not format the text in the body of the email to my liking. Dim OutApp As Object...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function
I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

Exit Sub And Call another Sub

vba,excel-vba,call
I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...

Excel - Open a named userform

excel-vba,userform
Sure this is very basic, I had userforms that I had not named and I called these by using UserForm1.Show. Now I have named these eg. ProgressBar. I call this one by UserForms.Add("ProgressBar").Show vbModeless But i cant figure out how to make this bit work: Private Sub UserForm_Initialize() 'With UserForms("ProgressBar")...

Changing the active cell

excel,vba,excel-vba,excel-2007