i'm experimenting with 'spin buttons' and want each click of the button to
display an array of cells from another sheet .e.g click 1 displays A1:G15
from sheet1 and displays in sheet2, next click dislpays A16:G31 and so on. In
other words 'scrolling'
I can get it to work once but for the first click but not the second click.
What is the secret to the second, third etc. click to get the function i
require?
Am i using the correct button?
Ahhhhhh!

--
Browny

Hi Browny,

In the worksheet module,
try something like:

'==========
Option Explicit
Private iOffset As Long

'--------------
Private Sub SpinButton1_SpinDown()
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set Rng = SH.Range("A1:G15")
i = Rng.Rows.Count
Set Rng2 = Rng.Offset(i * iOffset)

Application.Goto Reference:=Rng2, _
Scroll:=True

iOffset = iOffset + 1

End Sub

'--------------
Private Sub SpinButton1_SpinUp()
Dim SH As Worksheet
Dim Rng As Range
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1:G15")
i = Rng.Rows.Count

If Not iOffset = 0 Then
iOffset = iOffset - 1
End If
Set Rng2 = Rng.Offset(i * iOffset)

Application.Goto Reference:=Rng2, _
Scroll:=True

End Sub
'<<==========



---
Regards.
Norman
"Browny" wrote in message
...
i'm experimenting with 'spin buttons' and want each click of the button to
display an array of cells from another sheet .e.g click 1 displays A1:G15
from sheet1 and displays in sheet2, next click dislpays A16:G31 and so on.
In
other words 'scrolling'
I can get it to work once but for the first click but not the second
click.
What is the secret to the second, third etc. click to get the function i
require?
Am i using the correct button?
Ahhhhhh!

--
Browny

Hi Norman

Im having trouble with it. Would it be too much to ask for a greater
explanation?

Im not sure where to substitute my data for that required by Vbasic.

To explain,

My spin button is in sheet2

By clicking on the spin button (up) I want to copy a selected group of cells
A1:Q12 from sheet1 and paste into sheet2 A5:Q17, when I spin (down) I want
another group of cells to be copied.

Ultimately Im looking to browse thru sheet1 by clicking the spin button
up/dwn and read the selected group of cells (from sheet1) in sheet2.

Im not even sure Im using the correct command (spin button) maybe theres
a better way,

Can you advise

--
Browny


"Norman Jones" wrote:

Hi Browny,

In the worksheet module,
try something like:

'==========
Option Explicit
Private iOffset As Long

'--------------
Private Sub SpinButton1_SpinDown()
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set Rng = SH.Range("A1:G15")
i = Rng.Rows.Count
Set Rng2 = Rng.Offset(i * iOffset)

Application.Goto Reference:=Rng2, _
Scroll:=True

iOffset = iOffset + 1

End Sub

'--------------
Private Sub SpinButton1_SpinUp()
Dim SH As Worksheet
Dim Rng As Range
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1:G15")
i = Rng.Rows.Count

If Not iOffset = 0 Then
iOffset = iOffset - 1
End If
Set Rng2 = Rng.Offset(i * iOffset)

Application.Goto Reference:=Rng2, _
Scroll:=True

End Sub
'<<==========



---
Regards.
Norman
"Browny" wrote in message
...
i'm experimenting with 'spin buttons' and want each click of the button to
display an array of cells from another sheet .e.g click 1 displays A1:G15
from sheet1 and displays in sheet2, next click dislpays A16:G31 and so on.
In
other words 'scrolling'
I can get it to work once but for the first click but not the second
click.
What is the secret to the second, third etc. click to get the function i
require?
Am i using the correct button?
Ahhhhhh!

--
Browny