VBA SpinButton_SpinUp Question

[chris]

Hi VBA-Gurus!

I've a problem and hope that somebody can help me figure it out.

From a database i recive a RecordSet and the number of rows. Now i
wish, that every user can sort the result by himself (without of any
rules). So i've done this with SpinButtons, and it works pretty fine.
Only one thing, i don't know the number of rows yet, and each query
are diffrent. so i don't know how much SpinButtons i need actually.
You can see my problem at the end of the following Code:

is there any easier way to do this - someting like

Private Sub SpinButton(i) ()
Call Change(SpinButton(i), 2)
End Sub

thx a lot
chris maier
(austria)
--- code ---

Const maxcount = 10 'select count(*) aus database

Sub start()
Call addSpin(2, maxcount)
End Sub

Private Sub Change(oSB As Object, anderung As Integer)
Const peek = 2 'oberster punkt
Const level = maxcount 'unterster punkt

'zeile ermitteln und kontrolle ob nicht zuweit nach oben oder
unten gescrolled wird
sZeile = Range(oSB.LinkedCell).Row
If sZeile + anderung < peek Then Exit Sub
If sZeile + anderung - peek > level Then Exit Sub

'zeile ausschneiden
Rows(sZeile & ":" & sZeile).Cut
'und einfügen
Rows(sZeile + anderung & ":" & sZeile + anderung).Select
Selection.Insert Shift:=xlDown
'SpinButton nachziehen
If anderung < 0 Then oSB.Top = Cells(sZeile - 1, 1).Top + 5
If anderung > 0 Then oSB.Top = Cells(sZeile + 1, 1).Top + 5
'Zeilenhöhe anpassen
Rows(Range(oSB.LinkedCell).Row & ":" &
Range(oSB.LinkedCell).Row).RowHeight = oSB.Height + 10
'Fokus aufs Objekt für Tastatusfreaks
oSB.Activate
End Sub

Private Sub addSpin(zeile As Integer, anzahl As Integer)
Dim oSB() As Object
Const iLeft = 140, _
iWidth = 30, _
iHeight = 30

zeile = zeile - 1
If zeile < 0 Then
Debug.Print ("Zeilen start unter 0!")
Exit Sub
End If

ReDim oSB(anzahl)

For i = 1 To UBound(oSB)
'Einfügeort bestimmen
iTop = Cells(zeile + i, 1).Top + 5

'bestehene löschen
For Each xShapes In Shapes
If xShapes.Name = "SpinButton" & i Then xShapes.Delete
Next xShapes

'neu hinzufügen
Set oSB(i) = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1",
Link:=False, _
DisplayAsIcon:=False, _
Left:=iLeft, _
Top:=iTop, _
Width:=iWidth, _
Height:=iHeight)
oSB(i).Name = "SpinButton" & i
oSB(i).LinkedCell = Cells(zeile + i, 3).Address
Rows(Range(oSB(i).LinkedCell).Row & ":" &
Range(oSB(i).LinkedCell).Row).RowHeight = oSB(i).Height + 10
Next i
End Sub

Private Sub SpinButton1_SpinUp()
Call Change(SpinButton1, -1)
End Sub

Private Sub SpinButton1_SpinDown()
Call Change(SpinButton1, 2)
End Sub

Private Sub SpinButton2_SpinUp()
Call Change(SpinButton2, -1)
End Sub

Private Sub SpinButton2_SpinDown()
Call Change(SpinButton2, 2)
End Sub

Private Sub SpinButton3_SpinUp()
Call Change(SpinButton3, -1)
End Sub
:
Private Sub SpinButton100 ....
:
Private Sub SpinButton550 ....
...and so on ..

 

[Tom Ogilvy]

I am not sure why you would need more than 1 spin button, but


set rng = Range("A1").CurrentRegion

lastrow = rng.rows(rng.rows.count).row

Assuming your data starts in A1.