Free Microsoft Excel 2013 Quick Reference

Excel VBA - listbox click event and a never ending loop!

Hello

I have a useform with a listbox on it. I want to populate the listbox with file names which the user has selected. Initially the listbox is empty, except for a dummy If the user selects this, I call a routine which prompts him to select a file and this is added to the listbox. So now the listbox contains a item as well as the selected file. The user can then carry on adding files or selecting from the list of already added files.

Here's my problem....when setting the listindex property of the listbox, this seems to generate a "click" event. (I can track the listbox click event handler ebing called.) And setting the listindex property within the code called by the click event results in the click event being called recursively! I've tried disabling events but this doesn't seem to work. Any suggestions please - I'm going frantic!

Regards
Hedges

Here is a snippet of the code below.

Code:
Private Sub UserForm_Initialize()
Dim i As Integer
   
   Application.EnableEvents = False
   Set InpCln = New Collection
   ClnInFCln.Add InpCln, ListBox1.Name
   ClnInFCln(ListBox1.Name).Add "", key:=""
   ListBox1.AddItem "", 0
   ListBox1.ListIndex = 0 'Generates a click event!?

   Application.EnableEvents = True
End Sub

'If "" selected, calls routine to select file.
'Adds file name to collection of names and then updates ListBox list with the contents of this collection

Sub ListBoxManager(ctrl As Object)
Dim i As Integer
Dim newfirst As String 'Name new first place filename
Dim newfile As String
   
   Application.EnableEvents = False
   counter = counter + 1
   If ctrl.Value = "" Then
      newfile = FullFileName("C:JohnTest")
      If newfile  "" Then
         ClnInFCln(ctrl.Name).Add newfile, key:=newfile, after:=1
      End If
   Else
      newfirst = ctrl.Value
      ClnInFCln(ctrl.Name).Remove newfirst
      ClnInFCln(ctrl.Name).Add newfirst, key:=newfirst, after:=1
   End If
   
   'Now update contents of ListBox
   ctrl.Clear
   For i = 1 To ClnInFCln(ctrl.Name).count
      ctrl.AddItem ClnInFCln(ctrl.Name).Item(i)
   Next
   ctrl.ListIndex = 0         'Calls ListBox Manager recursively!
   Application.EnableEvents = True
End Sub

Private Sub ListBox1_click()
   Call ListBoxManager(ListBox1)
End Sub


Post your answer or comment

comments powered by Disqus
Someone very kindly gave me this macro
It works great for what I want, but if i enter a job number that does not exsist in the directory it goes into a never ending loop

Can someoone help, I need to error trap this macro so if i enter a number that does not exsist in the directory it will put up a message box telling me, and exit the Macro, or ask me to put a correct number in instead of going into a loop looking for a non exsisting file.

Thanks

MadBRiT

Sub Replace_Job_Number()

    Dim rngOld As Range, rngNew As Range
    
    Set rngOld = Range("A1")   'old job number
    Set rngNew = Range("A2")   'new job number
    
    If rngOld = "" Or rngNew = "" Then
    
        MsgBox "Missing job number. ", , "Cannot Update Links"

    Else

        Cells.Replace What:=rngOld.Value & ".xls", _
                      Replacement:=rngNew.Value & ".xls", _
                      LookAt:=xlPart, _
                      SearchOrder:=xlByRows, _
                      MatchCase:=False
    
        rngOld.Value = rngNew.Value
        rngNew.ClearContents
        
    End If

End Sub


I have a userform on which there is a list box. When the user clicks an item in a multiselect list box I want a total to appear in a textbox on the same form. The problem is that the listbox click event isn't firing. I have a breakpoint set at the click even and it never breaks there.

How is the click event supposed to work?

'The user form
is frmEntry.  There are several fields on the form, 1 listbox
'a textbox for totals and a couple other controls.  The listbox is programatically
'filled when the user clicks a choice in a combobox.

Private Sub UserForm_Initialize()

    With cboAgeGroup
        .AddItem "SJ"
        .AddItem "Jr."
        .AddItem "Sr."
        .AddItem "Adult"
    End With
    cboAgeGroup.Value = ""
End Sub

Private Sub cboAgeGroup_Change()
Dim rng As Range
    Application.ScreenUpdating = False
    Worksheets("Classes").Activate
    Range("$A$1:$B$32").AutoFilter Field:=2, Criteria1:="=All", _
        Operator:=xlOr, Criteria2:="=" & cboAgeGroup.Value
    Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    For Each c In rng.Rows
        Me.lstClasses.AddItem c.Cells(1, rng.Row).Value
    Next
    Application.ScreenUpdating = True
End Sub

Private Sub lstClasses_Click()
    Static clsCount As Integer
    clsCount = clsCount + 1
    txtClassChg.Value = clsCount * 10
End Sub


I am having a with a controlled loop. If the loop is on the last pass and an error occurs, it goes into a never ending loop. Once the error portion of code is excuted, the code resumes to the same line. I'm not sure how to solve this problem. Any help is appreciated. Thanks. I have attached a sample file with all code if needed.

Private Sub
CommandButton1_Click()
    
    Dim HSDFY As HistoricalStockDataFromYahoo
    Dim rs As ADODB.Recordset
    Dim i As Long
    Dim j As Long
    Dim lastrow As Integer

    On Error GoTo Err_CommandButton1_Click
    
    Range("C2:D" & Rows.Count).ClearContents

    i = 2

    lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow
        Set HSDFY = New HistoricalStockDataFromYahoo
        HSDFY.LineNumber = j
        Set rs = HSDFY.GetHistoricalData(Cells(j, 1).Value, Cells(j, 2).Value, Cells(j, 2).Value)
        rs.MoveFirst
        Cells(j, 3).CopyFromRecordset rs
        i = i + 1
    Next j

Exit Sub

Err_CommandButton1_Click:
    Select Case Err.Number
        Case 10000
            MsgBox Err.Description
            j = j + 1
            i = i + 1
        Case 10001
            'invalid interval
            MsgBox Err.Description
            j = j + 1
            i = i + 1
        Case 10002
            'query failed
            MsgBox Err.Description
            j = j + 1
            i = i + 1
       Case Else
            MsgBox "Invalid Search Parameters or other error.  No data was returned."
            j = j + 1
            i = i + 1
    End Select
    Resume
    
End Sub


Hi,

I have an excel workbook that is caught in a never ending loop when the workbook opens. Is there anyway to open the script editor to modify the code causing the loop?

Thank You, Mike

Hi wonder if someone can help... Below a few lines of code which unfortunatly send me into a never ending loop... What am I doing wrong?

Thanks


	VB:
	
 deleterows() 
     
    Dim LastRow As Long, R As Long 
     
    LastRow = Range("B65536").End(xlUp).Row 
     
    For R = 4 To LastRow 
        If Cells(R, 2) = "" Then 
            ActiveSheet.Cells(R, 2).Select 
            selection.EntireRow.Delete 
            R = R - 1 
        End If 
         
    Next R 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I am creating a macro with Excel VBA that will submit an entry into an online database using information from an Excel spreadsheet. During this entry process, the macro needs to click on a CSS button. It isn't a form button, does not have an input type, no name, no id, and no source image except for a background image. I think my only hopes are either to click on the button based on the background image OR based on the div class. Can anyone help?

I think I might be able to use the getElementsByClassName method, since the button is nested in Divs, but I'm not sure! Attached is the source html of the webpage. I removed some confidential information.

The button is here:

	VB:
	
 
 
 
 
iOS App 
 
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'd really appreciate any help! Thank you!

css_button_source.txt

EDIT
- Some added detail: I can't just navigate to the hyperlink, because the "~/5..0.0.9.7.3..." bit is dynamic and randomly generated on each login. And, clicking the CSS button opens a new webpage.

In my looping macro i'm getting the error
Excel cannot complete this task with available resources. Choose less data or close other applications But in reality it isn't really an error, it will pop up for every item in the list and if I click "OK" to it (everytime) the macro will work how it should.

How can i program the macro to automatically click this and/or not pop up.

I've already tried:

On Error Resume Next
but the error isn't a typical VBA excel error, it seems like its coming from windows itself.

Any ideas?

Using MSOfficePro 2003, winxp proSP2

I want to generate a worksheet by grabbing some sheets from a different
wb - then dumping certain columns from the sheets I grabbed.

I have a table that contains CustName, CustStreet, CustCity, Vendor1,
Vendor2, Vendor3 (etc - it will vary by customer and the purpose of this
is to tell me which sheets to grab from the other wb) and I will always
be grabbing ColA, ColB and ColC plus one more column from the sheets I
keep from the original.

This table loads in a listbox so the user can make the choice which
customer they need to work with. The list box shows only the first 3
columns, but when the user clicks on a choice, I will need to capture
the information for the entire record.

My questions a
How (and where in the wb does the code go) do I capture the choice the
user makes. I think the code will go in the lstBox.click event and the
choice will be referenced by List1.ListIndex. That is about all I know
right now, and I am really wondering if the List1.ListIndex entry will
contain all of the fields present in the record - if it does, how do I
access the different fields in the record so that I can use them to
manipulate the dumping of certain sheets and columns in the original
workbook so the user gets only the relevant info?

If you know of a good example on the net or amongst the excel mvps that
I could study to help myself, I sure would appreciate being pointed in
that direction.

As always, thank you for your time and expertise
Joanne

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

Explanation:
The source data the listbox contains is in columns A, B and C of Sheet1. I
use the rowsource property so the listbox list can have headers.
"WorkList" is a dynamically updating named range. When a selection is made
in Listbox1, the selection is moved to the top of the list on Sheet1 and the
rest of the list is sorted. Everything works great to that point, the
problem is that when I reset the RowSource property, the Listbox1 list
doesn't get updated. I assume this is because it is from Listbox1's click
event, as it works if I do the same thing from another procedure. Am I
doing something wrong, or is there possibly another way to accomplish this?

Thanks!
-Jeremy

----------------------------------------------------------------------------
Private Sub Listbox1_Click()
Dim rng As Range

If DisableFormEvents Then Exit Sub

With Worksheets("Sheet1")
Set rng = .Columns(1).Find(What:=Me.Listbox1, LookIn:=xlValues)
Range(rng, rng(1, 3)).Cut
.Range("A2").Insert Shift:=xlDown
With Range(.Range("A3"), .Cells(.Rows.Count, 26).End(xlUp))
.Sort Key1:=Range("A3"), Order1:=xlAscending,
End With
DisableFormEvents = True
Me.Listbox1.RowSource = _
.Names("WorkList").RefersToRange.Address(External: =True)
DisableFormEvents = False
End With

End Sub
----------------------------------------------------------------------------

I have an application in which as many as 30 worksheets experience externally triggered Worksheet_Calculate events. The real time triggers come via a COM interface link, and are completely random. Typically there are about 200,000 events in a 6.5 hour period, so the probability that some events will be virtually simultaneous is high. Each event runs worksheet_calc code in its own sheet and calls another routine, common to all worksheets, as well.

Although the application works quite well I have concerns about concurrent events. What happens if a new calc event occurs while VBA is still executing the code triggered by the most recent prior event? Does Excel queue the events for orderly disposal? Or does the newest one interrupt any ongoing operation from the previous event? If events are not queued, what happens to them? If they are queued, how many can be stacked? Is it a FIFO or FILO queue?

I would like to understand just how Excel & VBA deal with this situation. An explanation longer than a brief overview might well be too long for the forum. So I would be happy if someone could point me to an info source, something like a Microsoft knowledge base article or the like.

Thanks for any words of wisdom you can offer.

I have Excel VBA code that opens and passes a worksheet to SPSS. This
has not been a problem. Right now, Excel now tells the user to open and
run a file called Run.sbs once SPSS opens. I would like to skip this.
In other words, how would I program Excel to open SPSS and then run the
syntax with no user operations?

Thanks,
Jeff Miller

Hi all,

Gotta love how one problem resolution leads to others..

With the ability to highlight a listbox item with a mouse scroll over has presented a new
challenge. I've created a listbox_click event where I would like to be able to perform
other actions depending on which item list is selected. The issue is the selection
occurs with the mouse over and not a mouse click. Is there a way to differentiate the two? I need the event to activate another userform when a listbox item is selected with a
mouse button click then trigger an event with mouse over.

Please don't tell me API is my only solution.

Anyone willing or able to help me resolve this challenge is again appreciated.

Thanks,

BDB

Hi,

For those who are after the invoicing and purchasing management solution - here's the solution demo just for your review and comment.

The solution is based on Excel/VBA - and the language can be selected between Finnish and English. Today the solution is used in some Finish small companies, but may be suitable for abroads as well? Or is it?...

Feel free to try. The only demo issue is that the company name setup on forms is password protected...but can be solved in case more interest.

Noteable / Just click the English flag in startup page to swithch the language into English mode...

So - enjoy. All Comments appreciated,

Excel Logical Functions. IF, OR, AND explained. VBA range names

The IF, OR and AND Excel formulas Explained. Using named ranges in Excel VBA. How to tell if a range is within a named range

I have a userform that has a listbox that contains data in 7 columns. I also have a command button that when clicked allows the user to edit the data in whatever row of the list box is highlighted. This works fine.

I decided to have a double click in the listbox on a row do the same thing. So I simply picked the double click event and called the command button sub. When I try this, it runs the command button sub, but then it runs the userform Activate sub. This is a problem as it does things that I do not want to occur again.

I then decided to put the command buttom code in a separate sub and then call that sub on the double click -- same result.

Then I just put the exact code that I have in the command button into the double click event -- still does it.

Anyone know why this happens, I see no reason for it. It works great in the command button, but with double-click it run the userform Activate sub when it is done.

Here is the code that runs (if called by a command button it works fine, if called by a double_click in the listbox, it runs the userform_activate sub):


	VB:
	
 Edit() 
    SetCell = "d" & ListBox1.ListIndex + 5 
    Range(SetCell).Select 
     
    Range("AA1").Value = 1 
    ADD_ODC.Show 
     
    NextRow = Range("D65536").End(xlUp).Row 
    EndARR = "J" & NextRow 
    ODCData = Range("D5", EndARR) 
     
    ListBox1.Column = WorksheetFunction.Transpose(ODCData) 
     
    Range("AA1") = "" 
    ListBox1.ListIndex = -1 
    Filling 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


on a worksheet change event routine, I am opening up a userform that is linked to the sheet...if the user makes a change, than the sheet is updated and errors out since the userform is already open...

How do I check first in the routine to see if the userform is up and exit the routine if that is true?

Thanks

I recently purchased the Excel VBA bundle. I finally got around to trying the Slide your way through excel VBA. I installed it and tried to run it. The problem is when I click To Proceed click here. The button responds but nothing happens. I have Office Professional 2003 and power point is installed. I even tried installing the power point viewer with the same results. The help button works but I cannot start a lesson. I very frustrated because when you purchase something, you expect it to work. Any suggestions on how to fix this? I uninstalled the excel VBA software and then reinstalled it, all with the same results.

how to call a function on control click event and put the function result on
another control. in userform.

--
Message posted via http://www.officekb.com

how to call a function on control click event and put the function result on
another control. in userform.

--
Message posted via http://www.officekb.com

Hey All,

I am having a brain fart at the moment lol, is there a way that when I open my excel document, it performs the click event of a command button?

The command button was created using the Active X controls in excel 2007 and the button is located on sheet1.

I can create stand alone modules in Excel vba by clicking File New Module or
some such.

How can I run the code in those stand alone modules from another module or a
Form that has its own code?

Thanks for any help.

tmb

I have a userform that has a listbox that contains data in 7 columns. I also have a command button that when clicked allows the user to edit the data in whatever row of the list box is highlighted. This works fine.

I decided to have a double click in the listbox on a row do the same thing. So I simply picked the double click event and called the command button sub. When I try this, it runs the command button sub, but then it runs the userform initialize sub. This is a problem as it does thing that I do not want to occur again.

I then decided to put the command buttom code in a separate sub and then call that sub on the double click -- same result.

Anyone know why this happens, I see no reason for it.

Hi

I have a sheet1 with a command button with some code in its CommandButton1_Click() procedure (or is it called event? ).

I have another button (cmdResource) on another sheet (sheet2) in the same excel file. Upon clicking this button, I want to execute the code of sheet 1's CommandButton1_Click().

How can I achieve this?

tia
ajay


No luck finding an answer? You could always try Google.