Hi again (have been posted in Excel Programming also - sorry)

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

for the write-back, try

ActiveCell.Value = Me.cboPriority.Value

If the cell you need to write back to is not the active (selected) cell,
then be more explicit:
Worksheets("Sheet1").Range("A1").Value = Me.cboPriority.Value
naturally, change the name of the worksheet and cell address as required.

Hope this helps.

"Steen" wrote:

Hi again (have been posted in Excel Programming also - sorry)

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

Hi JLatham

Thanks for the quick answer. What I need is to be able to write data back to
the same row as has been used to activate the Form by dubble_click, but each
data in a specific coloum. It's the "active' Row i can't figure out how to
get...

Can you help me out here?

/Steen

"JLatham" wrote:

for the write-back, try

ActiveCell.Value = Me.cboPriority.Value

If the cell you need to write back to is not the active (selected) cell,
then be more explicit:
Worksheets("Sheet1").Range("A1").Value = Me.cboPriority.Value
naturally, change the name of the worksheet and cell address as required.

Hope this helps.

"Steen" wrote:

Hi again (have been posted in Excel Programming also - sorry)

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

Depends on how you're doing your double-click. If you are double-clicking on
any cell in the row you need the data to go back to, then the ActiveCell will
be on the active row, and ActiveCell.Row will give you the row number.
Since you can only double-click in a single cell, there's not much chance of
error.

In effect Target in the double-click processor is the ActiveCell, so when
you get the form open, ActiveCell is your guy!

"Steen" wrote:

Hi JLatham

Thanks for the quick answer. What I need is to be able to write data back to
the same row as has been used to activate the Form by dubble_click, but each
data in a specific coloum. It's the "active' Row i can't figure out how to
get...

Can you help me out here?

/Steen

"JLatham" wrote:

for the write-back, try

ActiveCell.Value = Me.cboPriority.Value

If the cell you need to write back to is not the active (selected) cell,
then be more explicit:
Worksheets("Sheet1").Range("A1").Value = Me.cboPriority.Value
naturally, change the name of the worksheet and cell address as required.

Hope this helps.

"Steen" wrote:

Hi again (have been posted in Excel Programming also - sorry)

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

Hi again

Implemented - works nice :-)

Is there a simple way to get a pre/next button to work.

Next: ActiveCell.Row = ActiveCell.Row + 1 ?

/Steen

"JLatham" wrote:

Depends on how you're doing your double-click. If you are double-clicking on
any cell in the row you need the data to go back to, then the ActiveCell will
be on the active row, and ActiveCell.Row will give you the row number.
Since you can only double-click in a single cell, there's not much chance of
error.

In effect Target in the double-click processor is the ActiveCell, so when
you get the form open, ActiveCell is your guy!

"Steen" wrote:

Hi JLatham

Thanks for the quick answer. What I need is to be able to write data back to
the same row as has been used to activate the Form by dubble_click, but each
data in a specific coloum. It's the "active' Row i can't figure out how to
get...

Can you help me out here?

/Steen

"JLatham" wrote:

for the write-back, try

ActiveCell.Value = Me.cboPriority.Value

If the cell you need to write back to is not the active (selected) cell,
then be more explicit:
Worksheets("Sheet1").Range("A1").Value = Me.cboPriority.Value
naturally, change the name of the worksheet and cell address as required.

Hope this helps.

"Steen" wrote:

Hi again (have been posted in Excel Programming also - sorry)

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

ActiveCell.Offset(1,0).Activate ' move down 1 row
ActiveCell.Offset(-1,0).Activate ' move up 1 row

You have to make sure you don't 'bump' the cieling or floor - that is, you
can encounter an error if you are on Row 1 and try to move to "previous" or
up 1 row, since there is no row 0, same if you're at last row and trying to
move to "next.

Easy handling this way:

On Error Resume Next ' ignore error for the moment
ActiveCell.Offset(1,0).Activate ' or the (-1,0).Activate statement
If Err<0 Then
Err.Clear ' trash the error
End If
On Error Goto 0 ' resume normal error trapping

"Steen" wrote:

Hi again

Implemented - works nice :-)

Is there a simple way to get a pre/next button to work.

Next: ActiveCell.Row = ActiveCell.Row + 1 ?

/Steen

"JLatham" wrote:

Depends on how you're doing your double-click. If you are double-clicking on
any cell in the row you need the data to go back to, then the ActiveCell will
be on the active row, and ActiveCell.Row will give you the row number.
Since you can only double-click in a single cell, there's not much chance of
error.

In effect Target in the double-click processor is the ActiveCell, so when
you get the form open, ActiveCell is your guy!

"Steen" wrote:

Hi JLatham

Thanks for the quick answer. What I need is to be able to write data back to
the same row as has been used to activate the Form by dubble_click, but each
data in a specific coloum. It's the "active' Row i can't figure out how to
get...

Can you help me out here?

/Steen

"JLatham" wrote:

for the write-back, try

ActiveCell.Value = Me.cboPriority.Value

If the cell you need to write back to is not the active (selected) cell,
then be more explicit:
Worksheets("Sheet1").Range("A1").Value = Me.cboPriority.Value
naturally, change the name of the worksheet and cell address as required.

Hope this helps.

"Steen" wrote:

Hi again (have been posted in Excel Programming also - sorry)

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