How can you extract data from a cell on a sheet (the user has clicked on )
to a userform,
( [adding to the value in the userform] I think I can manage that bit)
then insert that data from the userform back into the sheet?

Thanks in advance

The following placed behind the worksheet of interest will give the value of
the cell the user has entered
The value can be used to set the userform control value property. If you
wish to dtect only certain cells that have change use the second option.

' Value of entered cell (any cell on the spreadsheet
Private Sub Worksheet_Change(ByVal Target As Range)

Target

End Sub

'Value entered from selective cell(s)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Or Target.Address = "$F$6" Then

Target

End If
End Sub

Cheers
N

"John Collins" <oldbutvirile@NO SPAM.btopenworld.com wrote in message
...
How can you extract data from a cell on a sheet (the user has clicked on )
to a userform,
( [adding to the value in the userform] I think I can manage that bit)
then insert that data from the userform back into the sheet?

Thanks in advance






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

To move data from the User Form

Worksheets("QuotePage").Select
Range("a4") = TextBoxEstDate.Text


Just reverse the code to go from the spreadsheet to the Userform
It is best to use the full name of the contol ie UserForm.TextBoxEstDate.Text as opposed to just TextBoxEstDate.Text

UserForm.TextBoxEstDate.Text = Range("a4")

Hope that helps.

TerryK

Your code would fail as 'Target' in a line by itself is
meaningless.
Target is the variable passed by excel to the event
handler when a cell value has changed.
With this then, we can load our data to a form. For
instance, suppose userform1 has a textbox called
textbox1. We set the event handler to create an instance
of the form in memory using load - at which point the
form exists but is not visible. We then populate the
textbox with the value passed through the variable called
Target, and then Show the form

Private Sub Worksheet_Change(ByVal Target As Range)
Load UserForm1
UserForm1.TextBox1.Text = Target.Value
UserForm1.Show
End Sub


To load data from the textbox to a cell, just add a line
like this:-
Worksheets("MySheet").Range("A1").Value = textbox1.text
on the form, say to a command button Click event.
Its easier to read if you define a rang name,and in your
code set a range variable to that cell...

MyTarget.Value = Textbox1.text

However, you must keep in mind that IF you you the change
event to open the form as I did above, then using a form
to change a cell value will fire the event again.!
You may want to add a line like
Application.EnableEvents = False
to switch off the event handler before setting the value,
remember to set it to True after though, to switch event
handling on again.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
The following placed behind the worksheet of interest
will give the value of
the cell the user has entered
The value can be used to set the userform control value
property. If you
wish to dtect only certain cells that have change use
the second option.

' Value of entered cell (any cell on the spreadsheet
Private Sub Worksheet_Change(ByVal Target As Range)

Target

End Sub

'Value entered from selective cell(s)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Or Target.Address = "$F$6"
Then

Target

End If
End Sub

Cheers
N

"John Collins" <oldbutvirile@NO SPAM.btopenworld.com
wrote in message
...
How can you extract data from a cell on a sheet (the
user has clicked on )
to a userform,
( [adding to the value in the userform] I think I can
manage that bit)
then insert that data from the userform back into the
sheet?

Thanks in advance






----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.

Thank you it worked, the only problem I came across was the Worksheet_Change
event, sometimes it would activate by just clicking a cell on the sheet?
I moved the code to the Worksheet_BeforeDoubleClick event, not really what I
was after.
I thought I might try to add two List boxes, one to show the labels from
column1 allowing me to get the row and to show the labels from row1 allowing
me to get the column, then the user clicks a command button to get the cell
value. What do you think?

"Patrick Molloy" wrote in message
...
Your code would fail as 'Target' in a line by itself is
meaningless.
Target is the variable passed by excel to the event
handler when a cell value has changed.
With this then, we can load our data to a form. For
instance, suppose userform1 has a textbox called
textbox1. We set the event handler to create an instance
of the form in memory using load - at which point the
form exists but is not visible. We then populate the
textbox with the value passed through the variable called
Target, and then Show the form

Private Sub Worksheet_Change(ByVal Target As Range)
Load UserForm1
UserForm1.TextBox1.Text = Target.Value
UserForm1.Show
End Sub


To load data from the textbox to a cell, just add a line
like this:-
Worksheets("MySheet").Range("A1").Value = textbox1.text
on the form, say to a command button Click event.
Its easier to read if you define a rang name,and in your
code set a range variable to that cell...

MyTarget.Value = Textbox1.text

However, you must keep in mind that IF you you the change
event to open the form as I did above, then using a form
to change a cell value will fire the event again.!
You may want to add a line like
Application.EnableEvents = False
to switch off the event handler before setting the value,
remember to set it to True after though, to switch event
handling on again.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
The following placed behind the worksheet of interest
will give the value of
the cell the user has entered
The value can be used to set the userform control value
property. If you
wish to dtect only certain cells that have change use
the second option.

' Value of entered cell (any cell on the spreadsheet
Private Sub Worksheet_Change(ByVal Target As Range)

Target

End Sub

'Value entered from selective cell(s)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Or Target.Address = "$F$6"
Then

Target

End If
End Sub

Cheers
N

"John Collins" <oldbutvirile@NO SPAM.btopenworld.com
wrote in message
...
How can you extract data from a cell on a sheet (the
user has clicked on )
to a userform,
( [adding to the value in the userform] I think I can
manage that bit)
then insert that data from the userform back into the
sheet?

Thanks in advance






----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.

Of course it will fail, the pseudo was to illustrate were the value is not
to how to load a userform. As you say in your reply 'for instance' assumes
one use of the 'value' passed by the event handler.

Cheers
N

"Patrick Molloy" wrote in message
...
Your code would fail as 'Target' in a line by itself is
meaningless.
Target is the variable passed by excel to the event
handler when a cell value has changed.
With this then, we can load our data to a form. For
instance, suppose userform1 has a textbox called
textbox1. We set the event handler to create an instance
of the form in memory using load - at which point the
form exists but is not visible. We then populate the
textbox with the value passed through the variable called
Target, and then Show the form

Private Sub Worksheet_Change(ByVal Target As Range)
Load UserForm1
UserForm1.TextBox1.Text = Target.Value
UserForm1.Show
End Sub


To load data from the textbox to a cell, just add a line
like this:-
Worksheets("MySheet").Range("A1").Value = textbox1.text
on the form, say to a command button Click event.
Its easier to read if you define a rang name,and in your
code set a range variable to that cell...

MyTarget.Value = Textbox1.text

However, you must keep in mind that IF you you the change
event to open the form as I did above, then using a form
to change a cell value will fire the event again.!
You may want to add a line like
Application.EnableEvents = False
to switch off the event handler before setting the value,
remember to set it to True after though, to switch event
handling on again.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
The following placed behind the worksheet of interest
will give the value of
the cell the user has entered
The value can be used to set the userform control value
property. If you
wish to dtect only certain cells that have change use
the second option.

' Value of entered cell (any cell on the spreadsheet
Private Sub Worksheet_Change(ByVal Target As Range)

Target

End Sub

'Value entered from selective cell(s)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Or Target.Address = "$F$6"
Then

Target

End If
End Sub

Cheers
N

"John Collins" <oldbutvirile@NO SPAM.btopenworld.com
wrote in message
...
How can you extract data from a cell on a sheet (the
user has clicked on )
to a userform,
( [adding to the value in the userform] I think I can
manage that bit)
then insert that data from the userform back into the
sheet?

Thanks in advance






----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---