| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write data back from form to rows
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write data back from form to rows
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. -- Thanks, Shane Devenshire "Steen" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write data back from form to rows
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 "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Steen" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write data back from form to rows
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 "Steen" wrote: 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 "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Steen" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Write data back from a form to sheet | Excel Discussion (Misc queries) | |||
| Data back to Form??? | Excel Programming | |||
| is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
| How to read a SQL Table into Excel change the data and write back into SQL | Excel Programming | |||
| Passing data back from a form | Excel Programming | |||
Linear Mode
