ComboBox, ListBox— but it's really about the spinbutton

[Nick Vittum]

My Userform has a Combobox (Combo_sheets) to move the form's focus from one worksheet to another. I'm finding that a bit awkward as I use it, and decided a ListBox with a Spinbutton might work better. But in trying to set it up, I'm running into errors. I haven't pinpointed the error yet, but I'm pretty sure it occurs in the form's initialize event, so before I dig any deeper, I want to ask a couple of questions at that starting point.

I'm using the code that populated the Combobox as a model. The sheets it is populated with are named for the months, so the original code and my attempt at the new code look like this:

Code: Select all

    Me.Combo_sheets.List = Application.GetCustomListContents(3)         'remove once listBox is working
    Me.ListBoxSheets.List = Application.GetCustomListContents(3)

    Is that wrong? Should the code be different for a Listbox than for a Conbobox? Or should I not even use a Listbox, and use a textbox instead?
     Or, if this code looks okay, I'll have a clearer idea where to go next with the troubleshooting.

Thanks!

[HansV]

That should work to get the list box populated - what happens if you try it?

I don't see the need for a spin button.

[snb]

Start with

1. outcomment all procedures in the Userform's codemodule, except the one you want to scrutinize; in this case Userform_Initialize.
2. Avoid as much as possible event procedures in a Userform, because they cannot be disabled like the eventprocedures in a workbook (by Application.enableevents=false).
3. step through the procedure using F8 (step-by-step)
4. use the initialize procedure only to populate listboxes and comboboxes; everything else should have been done in design mode.
5. the activate procedure is only meant to restore the userform's state after hiding it with 'hide'
6. the principle 'less is more' isn't bad when programming. Initially programmer are very much inclined to add code to solve a problem, although rethinking might be more proficient.
7. the amount of code is dependent of the sleekness of the datastructure: the better the structure the less code. So if you encounter a problem: (re)structuring precedes coding.


The populating of a Listbox is identical to the populating of a combobox.
In both cases use the property .List to fill them with a 1-dimensional or multidimensional array.
A spinbutton is redundant and can only lead to circular code and damage the performance of the userform.

Since we do not know which eventprocedures you introduced in the userform

[Nick Vittum]

That should work to get the list box populated - what happens if you try it?

Well, nothing happens, apparently there's something wrong elsewhere in my code that I haven't found yet. (I get an error, "Invalid use of null property", or something like that.) I am intentionally not digging into it today. because I have too much else I have to get done, and I know if I start with this I'll be there all day. It's become an addiction :~) However, I did create a dummy form in my experiments workbook, and the code works there.

I don't see the need for a spin button.

You are so right. I guess I never used a listbox before. I never realized that it has a spin button built into it. Unfortunately, I need the window to be quite small (about 18pt (H) by 38pt(W)) which makes the spin button so small it's quite hard to use.

Once again, I've started something I thought would be quite simple, and discovered when I got into it that I don't know what I'm doing. I'm quite willing to let go of the idea if it turns out to be complex; but if you are inclined to look at it, I've attached the dummy copy so you can see what I'm toying with.

• As I said, the list box appears to be semi-practical at best for this situation
  I could use a combobox with an attached spinbutton, if there were a way to disable the dropdown arrow. But I can't find one; also, I can't remember how to populate the second column, which I believe i'd need to bind the spinbutton to. Probably also an impractical endeavor.
  The ideal would be the textbox with an attached spinbutton, but I can't figure out any way to insert a selectable list into a textbox, let alone any way to use a spinbutton with it. But it at least shows you the "look" I'm trying for.

Thanks for you time.

[Nick Vittum]

Start with
1. outcomment all procedures in the Userform's codemodule, except the one you want to scrutinize; in this case Userform_Initialize. . .

Thank you so much for these guideline. They will be very helpful to me.

A spinbutton is redundant and can only lead to circular code and damage the performance of the userform.

Yes. I guess I'd never used a listbox before. I didn't realize it has its own built-in button. (More above)

[HansV]

Making a list box one row tall is, uh, unusual. The idea is to display several rows so that the user can select an item by clicking on it.
Otherwise, I'd stick with a combo box.

[Nick Vittum]

Making a list box one row tall is, uh, unusual. The idea is to display several rows so that the user can select an item by clicking on it.
Otherwise, I'd stick with a combo box.

Right, I could see that once I realized what it looked like. I'd thought it was like a textbox in appearance, but one designed specifically for lists. Thanks

[Nick Vittum]

Perhaps I should be starting a new thread, but— When I was trying to sleep last night I realized I'm asking the wrong question here. A textbox could easily suffice, I'm sure. The real question is about the spinbuttons. I don't understand very well how they work, and there is surprisingly little information out there, either in my book or that I can find on the web.

But I do understand (I think) that they deal with numbers. So if my worksheets are numbered (01-12) then it seems in theory that it ought to be possible to set up a spinbutton to move from one worksheet to another. And it seems to me that making that possible must be in the controlsource setting. But I just can't figure it out. Is it possible?

[HansV]

A spinbutton control has a numeric value, but it's up to you to decide to do with that value. But I don't see the point of using a text box and a spin button to select a worksheet...

[Nick Vittum]

The why, in this situation, is because the userform is the primary interface, and the workbook is hidden most of the time (always during data entry, unless there's a problem). I already do use a "button" to navigate through the sheets— but it's the "button" on a combobox. When actually using the workbook, I've found this a little awkward, and thought "there ought to be a different way."

But in the process of thinking about it, the specific situation has become much less important, and it has become more of a quest to understand spinbuttons—which seem to me like a very useful tool, and if I'm to judge by the amount of information I've been able to find so far, perhaps a rather underutilized one. (Or, perhaps I'm wrong. Maybe they're just so easy to use, if you understand how to connect them with whatever numbers you're dealing with, that no one bothers to talk about them.)

[snb]

See the illustration attachment.

You can activate a sheet by sheet.name or sheet.listindex.
The sheetname is a string, the index is a number.

If a listbox is filled with the customlist(3), its values are monthnames i.e. strings.
So the value of this listbox can be used to activate a sheet by its name.

A listbox has a property .listindex; that is a number.
You can use the .listindex to activate a sheet by its indexnumber.

A spinbutton can only have numerical values.
That is why it is only fit to activate a sheet by its indexnumber.


NB. I introduced 3 lines of code to give the sheets the names of english monthnames instead of dutch ones
I gave the first sheet ( = index(1)) the monthname 'july' on pupose to illustrate the difference between sheet indexnumber, Listbox.listindex and the index in the array customlists(3)

[Nick Vittum]

Wow! So simple as this?—

Code: Select all

Private Sub SpinButton1_Change()       '        use the sheetindex
    Sheets(SpinButton1).Activate
End Sub

Thank you! I'm working outside today, on this rare spring day. But I can hardly wait to experiment with this later. Again, my thanks