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.

    Code
    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.

    Code
    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.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    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()
    Macro1
    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!

Register Yourself Login