Cycle betrween rows

[cmonthetic]

Hi,

I have created a form for inputting data to Excel 2003 and I am now adding some basic features.

The feature I would like is a button on the form that will move up 1 row at a time and display the data on the form for the user to review.

Appreciate any help.

 

[Loomah]

Hi
Move up one row from what? What is your starting point?
How much data is to be displayed; 2 cells, 3 cells, a whole row?

I think you're gonna have to give a little more detail for your requirement

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[cmonthetic]

This spreadsheet has multiple rows and uses columns A - G.

The button on the form when pressed will move the focus up one row from its current row, which could be any row in the sheet, thus displaying the information on that row from column A to G.

The Userform will be refreshed to reflect the row that currently is active.

I beleive I could use the Activecell.Offset command for this but am unsure on the implementation.

TIA

 

[xlbo]

Have you read the help file for OFFSET ???

what are you unsure of ??

This all depends on how you are currently displaying your data. Please show us the code you are using to display the data (presumably in text boxes on your form). Only then will we be able to advise how to mod it.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

If you are moving the actual active cell, then use the ActiveCell object itself, yes. Or you could set the row to a variable and use it that way if you feel more comfortable. Example ..

i = ActiveCell.Row
TextBox1.Value = Range("A" & i).Value
Textbox2.Value = Range("B" & i).value

HTH

-----------
Regards,
Zack Barresse

 

[Loomah]

Hi again
Without ignoring the recent suggestions this is what I've been working on - but it had to wait until I got home so I could use a more sensible version of xl than 97!!

You could have 2 options (actually you could probably have more than 2 but these are the ones I've gone for!)

1) concatenate your data into one textbox
2) display data in a flexgrid (like a database on your form)

Both use the same code below. I have created a userform with the following controls for this pupose. All controls have their default names.

Private Sub CommandButton1_Click()
Dim lRow As Long
Dim i As Integer

Dim myArr(6) As Variant

If Label1.Caption = "" Then
    Label1.Caption = ActiveCell.Row
ElseIf ActiveCell.Row = 1 Or Label1.Caption = 1 Then
    Label1.Caption = 1
Else
    Label1.Caption = Label1.Caption - 1
End If
lRow = Label1.Caption

For i = 0 To 6
    Me.MSFlexGrid1.TextArray(i) = Cells(lRow, i + 1)
    'array to add to textbox
    myArr(i) = Cells(lRow, i + 1)
Next

Me.TextBox1 = Join(myArr(), ";")

End Sub

This is necessary if you want to use the flexgrid in the same way I have.

Private Sub UserForm_Initialize()
With Me.MSFlexGrid1
    .FixedCols = 0
    .FixedRows = 0
    .Rows = 1
    .Cols = 7
End With
End Sub

Note: If you are unfortunate enough to still be using xl97 the flexgrid method should work but the join function didn't exist so that method won't work!

Good Luck, but as I suggested at the beginning of this post, this solution isn't instead of what xlbo & firefytr have suggested. It's in addition to!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Oooopppppsss

The controls I mentioned are
command button
textbox
label
flexgrid


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​