Spinbutton to cycle through named ranges

Hi,

I will try to explain my problem.

I have a bunch of named ranges

Range1, Range2, Range3, Range 4, ...


I can sort the named ranges. So depending on how they are sorted, they are in a different order.

Now I want to create a form to display the information in the ranges. And I want to use a spinbutton to move forward or backwards through the ranges.


Can anybody help me with the code I have to use for the spinbutton?

Thanks!

Here is the general idea. Where I have put code to display the name and range address, you will need to replace it with code to "display the information in the ranges".

Please Login or Register  to view this content.

I see. But you use labels? I have no labels. I will explain how I tried to make this work.

I have about 100 ranges. Rng1, Rng2, Rng3, Rng4,...

And I want a spinbutton to cycle through and display the information of these ranges in another range, called printrng for example.
Which is linked to another sheet for printing.

Does this clarify it a little bit?

No, I'm sorry, it doesn't. Because I still don't know what "display the information of these ranges in another range" means.

You started out by saying you wanted "a form to display the information". Do you now mean that you want to copy the range selected by the spin button and then paste it somewhere else on a worksheet, rather than display on the form?

My labels are just placeholders to show that something happens when you click the spin button. You need to replace the code in SpinButton1_Change to copy from Application.Names(Me.SpinButton1).RefersToRange and paste it wherever you want it.

I'm not doing a good job explaining this...

Now I'm trying it without a form. Just a spin button on the sheet and copy the selected range and paste it to somewhere else as you mentioned above.

I thought this would be an easier way. But I still havent figured it out.

Originally Posted by ymmotteov

I'm not doing a good job explaining this...

Now I'm trying it without a form. Just a spin button on the sheet and copy the selected range and paste it to somewhere else as you mentioned above.

I thought this would be an easier way. But I still havent figured it out.

If protonLeah's solution still does not answer your question then please attach your file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Without sample sheets we are all guessing what you are trying to achieve.
Two sample sheets, are all that is required, what your source data looks like and what end product do you want.
I have attached a couple of examples of what I envisage you are trying to do.
Both are elements from far more complex application I have used in the past and I have just extracted the bare bones.
The KPI file is used to extract data for interactive charts in PowerPoint utalising data import from Sage accounts.
The MEMBER file is adapted from one I used in a club admin app, in both cases the source data has been desensitised for data protection.
The KPI file is entirely formula driven, the MEMBER file has a FORM and VBA backed, to enter new data press CLEAR first, to edit existing select from listbox.
Hopefully you can then develop a clearer picture as to which direction you wish to take.

That is how it should work. But when I change the time in one of the ranges. The macro for the sorting gives an error message...

Ok, I've made a few changes and it works!

Learned something new again, thanks for the lesson! ;-)