Noob Form To Cell Data Transfer Problem

Schodoodles

New Member
Joined
Jan 25, 2005
Messages
17
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Schodoodles

New Member
Joined
Jan 25, 2005
Messages
17
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
I thought you had controls on a sheet not a userform. Which is it?
 

Schodoodles

New Member
Joined
Jan 25, 2005
Messages
17

ADVERTISEMENT

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")
??? :confused:
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Not that difficult I guess but youve confused me. :unsure:

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...
Code:
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

New Member
Joined
Jan 25, 2005
Messages
17
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. :oops:

Any ideas?
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 

Forum statistics

Threads
1,148,216
Messages
5,745,440
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top