I have this worksheet that will consist of a variable amount of rows. I need
the user to be able to select a cell (thus it would consitute it as if they
were selecting the entire row) and that data to show up on a userform.

I have tried to use:

iRow = ActiveCell.Row

frmUserform.Caption = Caption & " " & Worksheet("Project").Cells(iRow,1)

However I have to click the command button to open the form. Then close the
form, and open it again via the command button; then it will show the correct
data.

The reason I am doing it this way is because I have 256 cells per a row and
wish to create an easy interface to edit the cells.

Does anyone have ideas of what I am doing wrong?

put those two lines into the Userfrom's Initialise event...

Option Explicit
Private iRow As Long
Private Sub UserForm_Initialize()
iRow = ActiveCell.Row
Me.Caption = Me.Caption & " " & Worksheets("Project").Cells(iRow, 1)
End Sub


"Adam" wrote:

I have this worksheet that will consist of a variable amount of rows. I need
the user to be able to select a cell (thus it would consitute it as if they
were selecting the entire row) and that data to show up on a userform.

I have tried to use:

iRow = ActiveCell.Row

frmUserform.Caption = Caption & " " & Worksheet("Project").Cells(iRow,1)

However I have to click the command button to open the form. Then close the
form, and open it again via the command button; then it will show the correct
data.

The reason I am doing it this way is because I have 256 cells per a row and
wish to create an easy interface to edit the cells.

Does anyone have ideas of what I am doing wrong?

Hi Adam

You're on the right track. If you loook at existing forms that do this, they
all have "next" and "previous" buttons (or Up / Down or whatever). So let's
do that: Add Commandbutton1 and Commandbutton2 to the form (or rename the
buttons in my code to match your new buttons). Put this in the userform
code:

'*************** top of module ***************

Option Explicit

Public iRow As Long ' = ActiveCell.Row

'Set up form:

Public Sub SetMeUp(i As Long)
iRow = i
Me.Caption = "Row " & iRow
If iRow 0 Then TextBox1.Text = _
Sheets("Project").Cells(iRow, 1).Text
End Sub

Private Sub CommandButton1_Click() 'UP button
If iRow 1 Then
iRow = iRow - 1
TextBox1.Text = Sheets("Project").Cells(iRow, 1).Text
Me.Caption = "Row " & iRow
If iRow = 1 Then CommandButton1.Enabled = False
End If
End Sub

Private Sub CommandButton2_Click() 'DOWN button
iRow = iRow + 1
TextBox1.Text = Sheets("Project").Cells(iRow, 1).Text
Me.Caption = "Row " & iRow
If iRow 1 Then CommandButton1.Enabled = True
End Sub

'update cells after textbox entry:

Private Sub TextBox1_KeyUp(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
Sheets("Project").Cells(iRow, 1).Value = TextBox1.Text
End Sub

'*************** end of block ***************

and this in a standard module:

'*************** top of module ***************

Option Explicit

Sub ShowUserform()
Call UserForm1.SetMeUp(ActiveCell.Row)
UserForm1.Show
End Sub

'*************** end of block ***************

Set the button captions to p and q and format them with Wingdings3 and you
have your arrows. Marlett, Wingdings and Symbol fonts have similar symbols.

HTH. Best wishes Harald


"Adam" skrev i melding
...
I have this worksheet that will consist of a variable amount of rows. I
need
the user to be able to select a cell (thus it would consitute it as if
they
were selecting the entire row) and that data to show up on a userform.

I have tried to use:

iRow = ActiveCell.Row

frmUserform.Caption = Caption & " " & Worksheet("Project").Cells(iRow,1)

However I have to click the command button to open the form. Then close
the
form, and open it again via the command button; then it will show the
correct
data.

The reason I am doing it this way is because I have 256 cells per a row
and
wish to create an easy interface to edit the cells.

Does anyone have ideas of what I am doing wrong?