Edit current row in user form
 

Thanks John
It all seems to work well
Just one more question
I need to restrict data input in some cells to a "P" or a "C"
Any suggestions?
Cheers
Alan


"ajm1949" wrote:

I have 2 questions
1.How can I display the selected row (22 columns) in a user form so the data
can be edited and then saved into the current row?
2.How can I scroll through the rows using a form? (Next and back buttons?)
I already have the form created to enter the data-no problems there. Just
not sure how to add the current row into a form

Many Thanks in advance

ajm1949

Edit current row in user form
 

set the textbox properties to LOCKED for those you don't want users to edit.

Then replace scrollbar code with this version. You will need to adjust the
Case = code to the appropriate textbox number.

Private Sub ScrollBar1_Change()

Dim myrow As Long
Dim i As Integer

myrow = Me.ScrollBar1.Value

'textboxes
For i = 1 To 22

Select Case i

Case Is = 6, 7

Me.Controls("TextBox" & i).Text = "P"

Case Is = 14, 18

Me.Controls("TextBox" & i).Text = "C"

Case Else

Me.Controls("TextBox" & i).Text = _
Dataws.Cells(myrow, i).Value

End Select

Next


End Sub
--
jb


"ajm1949" wrote:

Thanks John
It all seems to work well
Just one more question
I need to restrict data input in some cells to a "P" or a "C"
Any suggestions?
Cheers
Alan


"ajm1949" wrote:

I have 2 questions
1.How can I display the selected row (22 columns) in a user form so the data
can be edited and then saved into the current row?
2.How can I scroll through the rows using a form? (Next and back buttons?)
I already have the form created to enter the data-no problems there. Just
not sure how to add the current row into a form

Many Thanks in advance

ajm1949

Edit current row in user form
 

small update to Lock textboxes programatically

Private Sub ScrollBar1_Change()

Dim myrow As Long
Dim i As Integer

myrow = Me.ScrollBar1.Value

'textboxes
For i = 1 To 22

Select Case i

Case Is = 6, 7

With Me.Controls("TextBox" & i)

.Text = "P"

.Locked = True

End With

Case Is = 14, 18

With Me.Controls("TextBox" & i)

.Text = "C"

.Locked = True

End With

Case Else

With Me.Controls("TextBox" & i)

.Text = Dataws.Cells(myrow, i).Value

.Locked = False

End With

End Select

Next


End Sub
--
jb


"john" wrote:

set the textbox properties to LOCKED for those you don't want users to edit.

Then replace scrollbar code with this version. You will need to adjust the
Case = code to the appropriate textbox number.

Private Sub ScrollBar1_Change()

Dim myrow As Long
Dim i As Integer

myrow = Me.ScrollBar1.Value

'textboxes
For i = 1 To 22

Select Case i

Case Is = 6, 7

Me.Controls("TextBox" & i).Text = "P"

Case Is = 14, 18

Me.Controls("TextBox" & i).Text = "C"

Case Else

Me.Controls("TextBox" & i).Text = _
Dataws.Cells(myrow, i).Value

End Select

Next


End Sub
--
jb


"ajm1949" wrote:

Thanks John
It all seems to work well
Just one more question
I need to restrict data input in some cells to a "P" or a "C"
Any suggestions?
Cheers
Alan


"ajm1949" wrote:

I have 2 questions
1.How can I display the selected row (22 columns) in a user form so the data
can be edited and then saved into the current row?
2.How can I scroll through the rows using a form? (Next and back buttons?)
I already have the form created to enter the data-no problems there. Just
not sure how to add the current row into a form

Many Thanks in advance

ajm1949

Edit current row in user form
 

Thanks John That will be very useful

"john" wrote:

small update to Lock textboxes programatically

Private Sub ScrollBar1_Change()

Dim myrow As Long
Dim i As Integer

myrow = Me.ScrollBar1.Value

'textboxes
For i = 1 To 22

Select Case i

Case Is = 6, 7

With Me.Controls("TextBox" & i)

.Text = "P"

.Locked = True

End With

Case Is = 14, 18

With Me.Controls("TextBox" & i)

.Text = "C"

.Locked = True

End With

Case Else

With Me.Controls("TextBox" & i)

.Text = Dataws.Cells(myrow, i).Value

.Locked = False

End With

End Select

Next


End Sub
--
jb


"john" wrote:

set the textbox properties to LOCKED for those you don't want users to edit.

Then replace scrollbar code with this version. You will need to adjust the
Case = code to the appropriate textbox number.

Private Sub ScrollBar1_Change()

Dim myrow As Long
Dim i As Integer

myrow = Me.ScrollBar1.Value

'textboxes
For i = 1 To 22

Select Case i

Case Is = 6, 7

Me.Controls("TextBox" & i).Text = "P"

Case Is = 14, 18

Me.Controls("TextBox" & i).Text = "C"

Case Else

Me.Controls("TextBox" & i).Text = _
Dataws.Cells(myrow, i).Value

End Select

Next


End Sub
--
jb


"ajm1949" wrote:

Thanks John
It all seems to work well
Just one more question
I need to restrict data input in some cells to a "P" or a "C"
Any suggestions?
Cheers
Alan


"ajm1949" wrote:

I have 2 questions
1.How can I display the selected row (22 columns) in a user form so the data
can be edited and then saved into the current row?
2.How can I scroll through the rows using a form? (Next and back buttons?)
I already have the form created to enter the data-no problems there. Just
not sure how to add the current row into a form

Many Thanks in advance

ajm1949