User forms

[Dundee Lad]

I am new to using the userform and was looking for some help. I have data in some cells which i want the userform to pick up. I also have some cells which I want populated by the userform input. I have managed to create the userforms but this is as far as |i have got. searched on the board but did not find anything as basic as this. can you help me?

 

[Jon von der Heyden]

Hi

Have you looked into Built-in forms for Excel data?

These are standard forms that can apply to data ranges. Go to Data -> Forms. Perhaps worth having a quick read on Excel help - it may well serve what you are after.

Regards
Jon

 

[RoryA]

In the userform's Activate event (or Initialize event) you can use code like:

Me.Textbox1.Text = sheets("Sheet1").Range("A2").Value

and to reverse the process, just swap it round:

sheets("Sheet1").Range("A2").Value = Me.Textbox1.Text

You can also set the controlsource property of the controls on the userform to a range address, but I prefer to do it all through code.
HTH

 

[RoryA]

Oh, and you might also have a look at John Walkenbach's Data Form add-in.

 

[Jon von der Heyden]

And Debra has a nice tutorial available here.

 

[Dundee Lad]

thanks for everyones help I will have a try at all of these

 

[Dundee Lad]

Rorya, I have tried your code

Private Sub UserForm_Activate()
Sheets("Sheet2").Range("c7").Value = Me.TextBox62.Text
Sheets("Sheet2").Range("d7").Value = Me.TextBox61.Text
End Sub

but when I check sheet 2 my data is missing and there is no data in the userform?

 

[RoryA]

Normally you would use the activate event to load data into your userform rather than the other way around because it is triggered when you show the userform. Your code is assigning the textbox values to the sheet ranges but the textboxes are probably blank at that point so you effectively clear the sheet ranges.

 

[Dundee Lad]

great, I understand it now. thanks for your help again!

 

[Dundee Lad]

Rorya, when I use the userform deactivate, it does not update the cells in excel? what am I doing wrong?

Code:
Private Sub UserForm_Deactivate()
Sheets("sheet3").Range("c4").Value = Me.TextBox62.Text
Sheets("sheet3").Range("d4").Value = Me.TextBox61.Text
End Sub