Noob Form To Cell Data Transfer Problem

[Schodoodles]

Hi,
I created a form with a basic layout where I want the info taken from text boxes and a couple of combo boxes on the form to be sent onto a worksheet (eg."sheet1").
I've got all the places i want text etc going into merged cells on my sheet ready.
However, I am now stuck trying to figure what I do to get that data from the form down onto the sheet. I presume it has something to do with using a command button but obviously am not completely sure.

Any help would be greatly appreciated!
Thanks
Scho

 

[Schodoodles]

I've been trying this bit of code; it didnt work - can anyone help?

Private Sub CommandButton_privateform_ok_Click()
With UserForm_PrivateInput
.ComboBox_private_issues = Range("PrivateIssuesRun").Value
.TextBox_private_advert = Range("PrivateAdverttext").Value
.TextBox_private_name = Range("PrivateName").Value
.TextBox_private_address = Range("PrivateAddress").Value
.TextBox_private_cardnum = Range("Privatecardnumber").Value
.ComboBox_private_cardtype = Range("Privatecardtype").Value
.TextBox_private_cardexpiry = Range("privatecardexpiry").Value
.TextBox_private_cardstart = Range("privatecardstart").Value
End With

End Sub

basic explanation of me descriptions;
.FieldOnForm = Range ("NamedCellonSheet").value

 

[parry]

Hi, you can use the LinkedCell property to do this. i believe from your previous posts that these are ActiveX controls (thats from either the Control Toolbox or Visual Basic Toolbars) so enter design mode and right click the control and select Properties. Scroll down untill you see a property called LinkedCell and enter a cell address (ie Sheet1!A1 to place into sheet1 , cell A1). Note you cant use your mouse to select the cell, you must type this in. You can do the same for both textboxes and comboboxes and the results of these controls will go into the cells you have entered for this property.

 

[parry]

I thought you had controls on a sheet not a userform. Which is it?

 

[Schodoodles]

Ok - I've got the data into the cells.

However once I just did the basic cell number into the box it came up with a problem.
I am using a seperate sheet with control buttons on it as a menu sheet - I have used one of these to show the form, but when i input the data it seems it puts it on my menu sheet and not the proper sheet - how do i put this in the controlsource properties to do it properly?

Is it something like;

ControlSource| Sheets.("name").Range("E9")
???

 

[parry]

Not that difficult I guess but youve confused me.

LinkedCell = where you want the result of a cell to go. So entering "Schodoodles" into a TextBox that has a linkedcell property value of Sheet2!A5
will place the word Schodoodles into cell A5 on Sheet2. Note the sheet reference included to ensure the result goes on a different sheet, and is a single cell.

A control source is where data comes from to populate a control. This is not applicable to a textbox, but is for a listbox or combobox. The property for this is the ListFillRange. So if you have a list in cells A5:A10 in sheet2 you would enter Sheet2!A5:A10. Note that the list must be vertical (ie A1:A5 not A1:E1).

If your wanting to add the values via code you would do this...

Private Sub Workbook_Open()
'Add three values to a combobox
Sheet1.ComboBox1.AddItem = "Item One"
Sheet1.ComboBox1.AddItem = "Item Two"
Sheet1.ComboBox1.AddItem = "Item Three"
End Sub

If you add items via code this is only in the control until you close the book. If you closed the book and opened it again the list is no longer there. Thats why the code needs to be in your workbook_open event to build the list each time the books opened. This is not the case if you use the ListFillRange property as it just looks in cells to get its values.

hth

 

[Schodoodles]

That sounds about right for the combo boxes and text boxes that are based in Excel - but I can't find the 'LinkedCell' box under properties for a textbox on a user form.

All I can see are 'Text' which is what is shown in the box, and Control Source that links the data to the cell for me - but I tried the Sheet2!A1 kind of value but it came back as wrong.

Any ideas?

 

[parry]

ControlSource doesnt link data to a cell - its putting values into the control. You never answered my question whether the control was on a userform or not, but now I guess that its a userform. LinkedCell is in regards to a control on a sheet so no you wont find it if its on a userform. Properties change dependant upon where it is.

To put the value from a control, into a cell...

Sheets("Sheet1").Range("a1").value = Userform1.TextBox1.Value