Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resetting the ListBox (2000)

    Okay. I've sorted out my "entering data to a form via a data entry worksheet" problem from yesterday, thanks for all your help. (Check out MSKBA 213749 - "XL2000: How to Use a UserForm for Entering Data" if you're interested.)

    Using the code provided by MS, I'm able to reset all of the fields except the ListBox. I've been able to reset the ListBox to the first value but it's still selected--is there a way to reset it so that the user makes the selection? Also, since my first control is the ListBox, is there a way to set focus with that control once the form has been reset? The VBA editor said that it wasn't an option with the ListBox.

    Any ideas?

    Thanks for all your help!

    S.O.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resetting the ListBox (2000)

    To "reset" the list box, you can set its ListIndex property to -1; this indicates that no item is selected.

    You can also use the ListIndex property to check whether the user has selected an item: if ListIndex = -1, no item is selected; if ListIndex is 0 or more, it is the zero-based index of the selected item (first item = 0, second item = 1, etc.)

    The SetFocus method should work for a list box too: ListBox1.SetFocus (with the appropriate name substituted). Note: if you combine this with setting ListIndex = -1, you will see a dotted rectangle around the item that was last selected.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting the ListBox (2000)

    Hello Hans:

    Thanks for your suggestion. Unfortunately, the SetFocus method still doesn't work. I'm slightly alarmed because I don't seem to have any properties to control the TabOrder (which would really be the end of my 33 field/control form!). I'm using the Control Toolbox controls (not Forms Toolbar) on a worksheet. The whole thing works fine (writing to a second worksheet) but there's no View-->Tab Order available or a TabOrder property. Would this have to do with any properties that I set for the worksheet?

    In a related question (polishing up the project, that is), is it possible to deactivate selection of the cells on my "Form" worksheet? I'd prefer the user just be able to select/activate controls, instead of the cells behind them.

    So once I get the Reset Listbox, SetFocus, TabOrder stuff sorted, I'm almost done!

    Thanks for your help.

    Cheers!

    S.O.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resetting the ListBox (2000)

    Aha, now I understand. Since you mentioned "userform", I had assumed (wrongly) that you were working with a UserForm created in the Visual Basic Editor (that's what the MSKB article is about).

    Others will no doubt correct me if I'm wrong, but I don't think that you can use SetFocus on a control placed on a worksheet (or a tab order for controls).

    I would suggest looking into UserForms. In the first place, you have much more control over what happens on a UserForm. In the second place, a UserForm looks better than controls placed on a worksheet. To create one:
    <UL><LI>Activate the Visual Basic Editor
    <LI>Select Insert/UserForm
    <LI>Make sure that Project Explorer (Ctrl+R), Properties (F4) and Toolset are visible.
    <LI>Use the toolset to create controls on the form. Switch to code view to add VBA code.
    <LI>Use code like UserForm1.Show to display the form. You can create a macro in a standard module that calls this code, and assign this macro to a keyboard shortcut, a custom toolbar button or a command button on a worksheet.[/list]Although it'll take time to get to grips with userforms, I think it is well worth it.

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting the ListBox (2000)

    Oh dear. I was worried you would say that. I'm familiar with UserForms, I just thought that the worksheet form was best for the sake of user familiarity (a big issue with this project).

    I'll just have to redo the whole thing. I'll hold off for about 30 minutes in case someone else pipes up with a solution.

    Thanks Hans!

    S.O.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •