Write data back from a form to sheet

S

Steen

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
 
J

JLatham

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.
 
S

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
 
J

JLatham

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!
 
S

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
 
J

JLatham

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
 
S

Steen

Hi JLatham

Great - I can now step up and down my list. :)

The only thing missing is display of the new row data in the form. I have
made the initialisation in the Tasklist sheet when dubbelclikking. How do I
make this happen when pressing the Next/Back button?

Private Sub cmdNext_Click()
On Error Resume Next ' ignore error for the moment
ActiveCell.Offset(1, 0).Activate
If Err <> 0 Then
Err.Clear ' trash the error
End If
On Error GoTo 0 ' resume normal error trapping
End Sub

/Steen
 
J

JLatham

Not sure how you're building it now. You could either add the code
immediately to the end of your 'Next'/'Previous' buttons (better to write a
separate routine and just call it from both. And set up your list where ever
it goes - I'm not clear where your list is coming from, or where you want it
to appear.
 
S

Steen

Hi JLatham

Thanks for all your help - I have it all working now - great. :)

Use the idee to make a separate routine.

/Steen
 
J

JLatham

Glad that it is all working for you now.

In cases like this, it's better for you in the long run to take the small
amount of overhead time added to call a routine than to duplicate the code in
multiple locations. This way if you need to change the way that process
works, or (heaven forbid actually fix a bug), then you only need to do it in
one place rather than trying to remember every location in the code that
performs that process.
 

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