Write data back from form to rows

S

Steen

Hi

I have just be writing my first code for a form by using this description
http://www.fontstuff.com/ebooks/free/fsUserForms.pdf and it works fine. Now I
would instead like to fire up the form by dubbel cliking a row and write back
the data to that row upon accept. I have found a way of activating the form
by dubbel click on a row but can't figure out how to write the data back the
the same row .

I would also appriciate some help on how to make a "next/previus" button work.

Any help?

/Steen
 
S

ShaneDevenshire

Hi,

It would have been easier if you showed us your current code. Basically you
are going to capture the row of the Target cell near the beginning of your
code, something like

myRow = Target.Row

I assume you are using the Before_DoubleClick event.

Later on in your code you will be saying something like

Cells(myRow,3) = me.txtName
Cells(myRow,4) = me.txtAddress


If this helps, please click the Yes button.
 
S

Steen

Hi ShaneDevenshire

Thanks for your answer. I can't get it to work and that's probably caused by
the code being in two different places.

Sheet2(Tasklist):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Row > 4 Then
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtSCO_SCR.Value = Target.Offset(0, 1) & " " &
Target.Offset(0, 0) 'SCO/SPR ID
frmTaskList.txtPMR.Value = Target.Offset(0, 2)
frmTaskList.cboPlannedRelease.Value = Target.Offset(0, 3)
frmTaskList.txt_RegDate.Value = Format(Target.Offset(0, 4),
"dd-mm-yy")
frmTaskList.cboTaskOwner.Value = Target.Offset(0, 5)
frmTaskList.cboRequester.Value = Target.Offset(0, 6)
frmTaskList.cboPriority.Value = Target.Offset(0, 7)
frmTaskList.txtHeadLine.Value = Split(Target.Offset(0, 8), vbLf)(0)
frmTaskList.txtDescription.Value = Split(Target.Offset(0, 8),
vbLf)(999)
frmTaskList.cboCQAttach.Value = Target.Offset(0, 9)
frmTaskList.cboStatus.Value = Target.Offset(0, 10)
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yy")
frmTaskList.txtOrg_Deadline.Value = Format(Target.Offset(0, 14),
"dd-mm-yy")
frmTaskList.txtComment.Value = Target.Offset(0, 16)
frmTaskList.cboApprover.Value = Target.Offset(0, 17)

'Call Tasklist form
frmTaskList.Show


Case 14, 15
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
Case 17
If Target.Offset(0, 0) = "" Then
Target.Offset(0, 0) = Format(Date, "yy.mm.dd") & " " &
UCase(Environ("username")) & ": "
Else
Note = Target.Offset(0, 0)
Target.Offset(0, 0) = Format(Date, "yy.mm.dd") & " " &
UCase(Environ("username")) & ": " & vbLf & Note
End If
End Select
End If
End Sub

Forms: frmTasklist:
Private Sub cmdAccept_Click()
If Me.cboRequester.Value = "" Then
MsgBox "Please enter initials for Task Requester.", vbExclamation,
"Task Values"
Me.cboRequester.SetFocus
Exit Sub
End If
If Me.cboPriority.Value = "" Then
MsgBox "Please enter Priority 1, 2 or 3 with 1 critical priority.",
vbExclamation, "Task Values"
Me.cboPriority.SetFocus
Exit Sub
End If
If Me.cboStatus.Value = "" Then
MsgBox "Please enter Status of Task.", vbExclamation, "Task Values"
Me.cboStatus.SetFocus
Exit Sub
End If
If Me.cboPlannedRelease.Value = "" Then
MsgBox "Please enter Planned Release.", vbExclamation, "Task Values"
Me.cboPlannedRelease.SetFocus
Exit Sub
End If
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox "Please enter date in format YY-MM-DD.", vbExclamation, "Task
Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

'write data back to sheet tasklist in the correct row?

End Sub


Hope fore some help again :)

/Steen
 
S

Steen

Hi again

I would like to know how to write back the data from a form upon accept. The
form is activatied from a dubble_click on sheet2 (Tasklist), but I can't
figure out how to write the data back the the same row .

I would also appriciate some help on how to make a "next/previus" button work.

I have simplified the code below to be more illustrative:

Sheet2(Tasklist):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Row > 4 Then
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.cboPriority.Value = Target.Offset(0, 7)
frmTaskList.txtHeadLine.Value = Target.Offset(0, 8)

'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Forms: frmTasklist:

Private Sub cmdAccept_Click()
If Me.cboPriority.Value = "" Then
MsgBox "Please enter Priority 1, 2 or 3 with 1 critical priority.",
vbExclamation, "Task Values"
Me.cboPriority.SetFocus
Exit Sub
End If

'write data back to sheet tasklist in the correct row?
???

End Sub

Hope fore some help again :)

/Steen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top