In Cell A2 of my data worksheet I want this value to be placed in a userform
(frmEdit) so that it can be edited, once changed the user 'submits' the data
and it is placed into cell A2 replacing what was there before....

any ideas how this can be done??
THanks in advance

Hi

Look at the code below. The UserForm_Initialize will fire when you "run"
your form. I assume that you have a commandbutton named cmbSubmit on your
userform. The code needs to be copied to the codesheet for the userform.

Private Sub cmbSubmit_Click()
Sheets("Sheet1").Range("A2").Value = Me.TextBox1.Value
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Value = Range("A2").Value
End Sub

Regards,
Per

"Anthony" skrev i meddelelsen
...
In Cell A2 of my data worksheet I want this value to be placed in a
userform
(frmEdit) so that it can be edited, once changed the user 'submits' the
data
and it is placed into cell A2 replacing what was there before....

any ideas how this can be done??
THanks in advance

Private Sub Userform_Activate()
Me.TextBox1.Text = Worksheets("Sheet1").Range("A2").Value
End Sub

Private Sub TextBox1_AfterUpdate
Worksheets("Sheet1").Range("A2").Value = Me.TextBox1.Text
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Anthony" wrote in message
...
In Cell A2 of my data worksheet I want this value to be placed in a
userform
(frmEdit) so that it can be edited, once changed the user 'submits' the
data
and it is placed into cell A2 replacing what was there before....

any ideas how this can be done??
THanks in advance

Thanks for ur replys - can I expand on my inital question?
My userform frmEditjobref is displayed when the user clicks on the 'edit
job' button.
They input a specific job ref number.
I want column A of workshet DATA to be searched (cells A12:A100) for the
number entered into the userform, if found copy/paste this rows data into row
A2 of the EDIT JOB RESULTS worksheet.
each value A2,B2,C2,D3 etc will then be placed into the form frmEditJob so
that they can be edited.
So data in A2 should be placed into userform 'txtctc' box
data in A3 placed into 'txttime' box, etc etc

Once the user has finished editing the data shown in the userform they add
the data (command button) back to the DATA worksheet, replacing what was
there originaly

Oh and if the original ref can't be found through column A of the DATA
worksheet then a msg pop up to advise so...........


etc etc

"Per Jessen" wrote:

Hi

Look at the code below. The UserForm_Initialize will fire when you "run"
your form. I assume that you have a commandbutton named cmbSubmit on your
userform. The code needs to be copied to the codesheet for the userform.

Private Sub cmbSubmit_Click()
Sheets("Sheet1").Range("A2").Value = Me.TextBox1.Value
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Value = Range("A2").Value
End Sub

Regards,
Per

"Anthony" skrev i meddelelsen
...
In Cell A2 of my data worksheet I want this value to be placed in a
userform
(frmEdit) so that it can be edited, once changed the user 'submits' the
data
and it is placed into cell A2 replacing what was there before....

any ideas how this can be done??
THanks in advance

Hi

Look at the codes below (untested). Just make sure that the subs goes into
the right userforms.

Dim TargetCells As Range
Dim DestRow As Integer
Dim ws As Worksheet
Dim ws1 As Worksheet

Private Sub cmbFindJobRef_Click()
'frmEditJobRef
Set ws = Worksheets("Data")
TargetCells = Range("A12:A100")

Set DestRow = ws.TargetCells.Find(what:=JobID, LookIn:=xlValues, _
Lookat:=xlWhole, searchdirection:=xlNext).Row
If DestRow Is Nothing Then
msg = MsgBox("JobID was not found!", vbExclamation, "Warning")
Exit Sub
End If
ws.Rows(DestRow).Copy Destination:=Sheets("EDIT JOB RESULTS").Range("A2")
frmEditJob.Show
End Sub

Private Sub CommandButton1_Click()
'frmEditJob

ws.Cells(DestRow, "A") = Me.txtctc.Value
ws.Cells(DestRow, "B") = Me.txttime.Value
' etc.
End Sub

Private Sub UserForm_Activate()
' frmEditJob
Dim ws1 As Worksheet
Set ws1 = Worksheets("EDIT JOB RESULTS")
Me.txtctc.Value = ws1.Range("A2").Value
Me.txttime.Value = ws1.Range("B2").Value
' Etc.
End Sub

Regards,
Per

"Anthony" skrev i meddelelsen
...
Thanks for ur replys - can I expand on my inital question?
My userform frmEditjobref is displayed when the user clicks on the 'edit
job' button.
They input a specific job ref number.
I want column A of workshet DATA to be searched (cells A12:A100) for the
number entered into the userform, if found copy/paste this rows data into
row
A2 of the EDIT JOB RESULTS worksheet.
each value A2,B2,C2,D3 etc will then be placed into the form frmEditJob so
that they can be edited.
So data in A2 should be placed into userform 'txtctc' box
data in A3 placed into 'txttime' box, etc etc

Once the user has finished editing the data shown in the userform they add
the data (command button) back to the DATA worksheet, replacing what was
there originaly

Oh and if the original ref can't be found through column A of the DATA
worksheet then a msg pop up to advise so...........


etc etc

"Per Jessen" wrote:

Hi

Look at the code below. The UserForm_Initialize will fire when you "run"
your form. I assume that you have a commandbutton named cmbSubmit on your
userform. The code needs to be copied to the codesheet for the userform.

Private Sub cmbSubmit_Click()
Sheets("Sheet1").Range("A2").Value = Me.TextBox1.Value
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Value = Range("A2").Value
End Sub

Regards,
Per

"Anthony" skrev i meddelelsen
...
In Cell A2 of my data worksheet I want this value to be placed in a
userform
(frmEdit) so that it can be edited, once changed the user 'submits' the
data
and it is placed into cell A2 replacing what was there before....

any ideas how this can be done??
THanks in advance