Listbox Focus Lost

  • Hello all,


    I make use of a listbox on a worksheet.
    The user can select an item from the listbox, hit a button and an userform shows up.
    On the userform the user can amend the corresponding items hit and the save button.
    After pressing the save button the selected item is still the focus of the listbox.
    This makes it possible for the user to hit a "previous" or "next" button, which will set the focus to the previous or next item.


    However, In some cases, while amending data on the userform, new data may be added from a second userform.
    The problem is that after the save-button is presssed on the second userform the focus on the listbox is lost.
    This while the data of the selected item from the listbox is still shown on the userform, which is still open.


    Is there a way that after hitting the save-button on the sub-userform the focus is brought back?


    This is the code,on the sub-userform, after which the listbox looses focus.


  • Re: Listbox Focus Lost


    Just set the focus back onto the listbox

    Code
    1. Userform.Listbox.SetFocus


    Where userform = name of the userform and listbox = name of listbox. You need to reference the form and the control.

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Listbox Focus Lost


    If you are using Excel 2007 you will need to work with the Shapes object

    Code
    1. Worksheets("Sheet2").Shapes(2).Select


    Where 2 is the index number of the control - if it is listbox2 then use 2 if it is listbox1 use 1

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Listbox Focus Lost


    Quote from smuzoen;594875

    If you are using Excel 2007 you will need to work with the Shapes object

    Code
    1. Worksheets("Sheet2").Shapes(2).Select


    Where 2 is the index number of the control - if it is listbox2 then use 2 if it is listbox1 use 1


    Now it selects the Listbox, but no focus?

  • Re: Listbox Focus Lost


    SetFocus is not a property of a worksheet ListBox so you could just select the first item in the list from the listbox

    Code
    1. Dim LB As MSForms.ListBox
    2. Set ws = Worksheets("Sheet1")
    3. Set LB = ws.OLEObjects("ListBox1").Object
    4. LB.ListIndex = 0


    In this case you do not need to use Shapes object

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Listbox Focus Lost


    Thanks Anthony, but that's not what I was looking for.
    Perhaps there is a way to prevent it from loosing focus?


    Any idea what makes it loose focus in the following code?


  • Re: Listbox Focus Lost


    You activate 2 sheets in a row (why?) and then load a form - I would suggest that the activation of the sheets would cause it to lose focus. Why activate the sheets - it serves no purpose. There is RARELY EVER a need to activate sheets. As long as you reference a sheet correctly you can write/retrieve data without selecting or activating it. Can you post your workbook with no sensitive data and I will have a look for you. I have no idea what the form code is from what you have posted - all I know is it is Initialised. Post the workbook with the code and remove any sensitive data and I (or someone) will be able to help you rather than just suggesting this and that in the hope we answer your problem.

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]