Filling a User Form with data from a sheet

[TonyBF]

I have a user form which places data into rows on a spreadsheet. Once the data is in there I would like to have another user form which can be used to change that data and replace the old data.

The original form consists of text boxes, combo boxes, check boxes and option buttons as well as calculated fields.

The new form needs to be able to:

1 - select the row containing the data to modify;
2 - place that data into the form in the right boxes to allow changes to be made; and
3 - on completion of the changes place the updated data back in the row it came from.

I think I have point two under control (initilise the form using the data selected) but the data selection part and making sure it goes back onto the right row is something I can't get my head around. I was going to copy the original user form and the associated code and then just modify the bits of the code and controls that would no longer apply.

Any thoughts on how I can do points one and three or places to look for ideas?

 

[Sthrncali]

How will you initially select the row with data to modify? i.e., if you know where the data is coming from, then you know where it need to go to as well.

so when your userform initializes, you will have to have some logic that says:

Userform1.TextBox1.Value = (Some Range Here)

Then with the submit button:

(Some Range Here).value = Userform1.TextBox1.Value

I have a user form which places data into rows on a spreadsheet. Once the data is in there I would like to have another user form which can be used to change that data and replace the old data.

The original form consists of text boxes, combo boxes, check boxes and option buttons as well as calculated fields.

The new form needs to be able to:

1 - select the row containing the data to modify;
2 - place that data into the form in the right boxes to allow changes to be made; and
3 - on completion of the changes place the updated data back in the row it came from.

I think I have point two under control (initilise the form using the data selected) but the data selection part and making sure it goes back onto the right row is something I can't get my head around. I was going to copy the original user form and the associated code and then just modify the bits of the code and controls that would no longer apply.

Any thoughts on how I can do points one and three or places to look for ideas?

 

[Kreszch68]

Hi TonyBF,

if you are looking for a method to select a range from the Userform, simply add the RefEdit control to your userform.
This will enable the user to select a range.
From there you can use the value of the RefEdit control to set a range.

 

[Trevor G]

Welcome to the Forum,

Look at this link to microsoft it will explain how to configure your form and also the code to be used to fill the form etc.

http://msdn.microsoft.com/en-us/library/aa192538%28v=office.11%29.aspx