List box activity

  • I want to make a macro run only after a change has taken place. The problem is in what I think a change is.

    Scrolling is not a change IMO.

    I want to just have the macro run on click within the listbox.

    This is a normal spread sheet list box.

  • I tried stuff like
    Private Sub listbox3_change()
    in the worksheet section of the project.

    that did not work.

    Currently i am using the assign to macro function.

  • With the Forms Listbox you need to check whether the linked cell actually changed. In the example below, the linked cell is named Pick. Another cell out of the way or on a hidden sheet is named OldPick.

    Sub Listbox1_Change()
        If Range("Pick") = Range("OldPick") Then Exit Sub
        Range("OldPick") = Range("Pick")
        MsgBox "Change"
    End Sub
  • If you use the forms listbox and assign a macro to it then as soon as you click the listbox, items or scrollbar, the macro will be run.
    It works the same as if the listbox where an autoshape.

    Maybe use the listbox from the Control Toolbox instead. This does have a true Click event.

    If you really have to use the forms listbox then maybe try this approach.

    Listbox property Cell Link is A1 and the macro is MyMacro.

    your code would be something like this.

    Dim intLastChoice as integer
    Sub MyMacro()
       If Range("A1").value <> intLastChoice then
           ' do your code here
           intLastChoice = Range("A1")
       end if
    End Sub
    Sub MySetup()
         intLastChoice = Range("A1")
    End Sub

    You will need to run the MySetup macro when the workbook first opens, otherwise scrolling the listbox will run your code.



  • Well I guess the selection of form and control makes a difference.

    I converted it from a form list box to controll

    Private Sub ListBox1_Click()
    End Sub

    is the only code required for it.

    thanks guys

  • oops I bring this one back to life.

    After routing my click in the list box to the other macro
    it seems as if the item is never hilited. It does change the information going into the macro but it does not show any change unless you refresh the sheet by clicking another sheet and returning again.

    any suggestions as to how I can insure that the clicked item highlites.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!