PDA

View Full Version : Solved: Spin button scrolling through cell links



xfr79
01-02-2009, 09:31 AM
First off, this is a cross post at http://www.mrexcel.com/forum/showthread.php?t=361806&highlight=spin+button+scrolling+lookup+reference .
I will mark as solved if it's solved at other forum.

I have a couple spin buttons in my worksheet.
What I want each one to do is to scroll through cell links.
For example, cell I4 has a lookup reference of "=LOOKUP(2^20,Sheet3!A:A)". What that does is get data from the very last line on sheet3 column A.
What I want the spin button to do is scroll through the lines in column A.
So if a user decided he/she did not want that data from column A, then all they would have to do is press up or down on the spin button , scrolling through other data that is in column A.
cell lookups:
I4 - =LOOKUP(2^20,Sheet3!A:A) <--- Date
B3 - =LOOKUP(2^20,Sheet3!B:B) <--- Number
B4 - =LOOKUP(2^20,Sheet3!C:C) <--- Number
B5 - =LOOKUP(2^20,Sheet3!d:d) <---- Date

thanks for taking a look!

lucas
01-02-2009, 11:15 AM
Hey Okie, I just checked your link and you're not getting much help. The reason I believe is that no one understands exactly what you are trying to do.

Can you post an example of your data with some notes on the sheet to explain exactly what you are trying to do?

xfr79
01-02-2009, 12:09 PM
Ok, i have attached a excel file. I stripped it down to what i'm trying to figure out.
i have colored coded it. The ones with the same cell color are the ones I want to change with the spin button. So lets use the first spin button, Cell I4. If you noticed the lookup reference, it's looking for the last row with data on sheet "db2IR" column A and displays the data in that cell, the same others that are color coded the same.
So what I wanted to do, is when the spin button is pressed up, it would display the data above the last row it found with data.
Right now on the sheet bd, 12/31/2008 is displayed. If I were to hit the up spin button, it would display 12/30/2008. If you look at the lookup reference sheet "db2IR" you would see the data.
I want the spin button to scroll through all the data on that reference sheet.
Cells, I4, B3, B4, AND B5 are referenced to the same sheet, just different columns. The spin button would change each cell to row above or below.

I hope I wasn't to confusing. :doh:

xfr79
01-02-2009, 12:14 PM
i forgot to add, the reference sheets have data that is imported from an Access database.

Artik
01-03-2009, 08:54 PM
Good idea, xfr79!
Way is good, but the wrong direction. :rotlaugh:

Example for cells B3, B4, B5, I4 and SpinButton1.

Formulas:
in B3 =LOOKUP($I$4,db2IR!A:A,db2IR!B:B)
in B4 =LOOKUP($I$4,db2IR!A:A,db2IR!C:C)
in B5 =LOOKUP($I$4,db2IR!A:A,db2IR!D:d)
in I4 =INDEX(db2IR!A:A,MATCH(MAX(db2IR!A:A),db2IR!A:A,0)-$J$4)

Code for SpinButton1 (in module of sheet "bd"):Option Explicit

Private Sub SpinButton1_SpinDown()
On Error GoTo ResetNum
Me.SpinButton1.Value = [J4].Value - 1
[J4].Value = [J4].Value - 1
ResetNum:
Exit Sub
End Sub

Private Sub SpinButton1_SpinUp()
On Error GoTo ResetNum
Me.SpinButton1.Value = [J4].Value + 1
[J4].Value = [J4].Value + 1
ResetNum:
Exit Sub
End Sub
You still need to work on SpinButton1.Max.

Artik

xfr79
01-06-2009, 07:18 AM
Artik, that worked perfectly!!
I can finally move on with this project!

Thank you very much!! :)
:beerchug: