PDA

View Full Version : transfer of data from userform to sheet



rmpaswan
04-12-2007, 05:09 PM
Hi,
I have a userform, which have a command button and five pages to take inputs so that i cud put these inputs to a sheet. on one page of the userform, I have ten check boxes and corresponding to each check box some data. based on the checked box checked, the corresponding data will be written to sheet in a rectangular chart. I dont know how to put the condition so that rectangle formed having equal number of column as the number of check box is checked. so that corresponding data could be put in the columns. Second, the command button which i will use to transfer the data from the form to the sheet, will be used repeatedly therefore, every next time the new input will get added below to the previous inputs alreadly on the sheet.................
I really appreciate a help.......................I would love any body can help me ..................my hotmail id is rmpaswan786@hotmail.com so i would appreciate if some on pas me his msn email so tat i could chat with him n solve my prob.
thanx
ram:doh: :banghead:

feathers212
04-12-2007, 06:55 PM
I'm not quite sure what you are asking. How exactly do you want this data put into the sheet? Do you want:

If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 3. OR
If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 2.If you want the first you could use:


If CheckBox1.Value = True Then
Cells (2, 1).Value = TextBox1.Value
Else: End If

If CheckBox2.Value = True Then
Cells (2, 2).Value = TextBox2.Value
Else: End If


Either way, we need a little more information on how you want this to work.

mdmackillop
04-13-2007, 09:04 AM
Hi Ram,
If you can put some sample data in a workbook and post it, together with your form, it might be clearer what you're after. You can post a workbook using Manage Attachments in the Go Advanced section

rmpaswan
04-13-2007, 09:50 AM
Hi Feathers.
Thanks very much. I guess ur second choice is appropriate to my problem.
Next, I have a command button on form, which every time it clicked added the input data from the form to the sheet, just below the previous datas on the sheet, without deleting previous one. How could I write the code for button so that it will transfer the data to sheet, but not delete the previous data?

Paswan





I'm not quite sure what you are asking. How exactly do you want this data put into the sheet? Do you want:

If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 3. OR
If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 2.If you want the first you could use:


If CheckBox1.Value = True Then
Cells (2, 1).Value = TextBox1.Value
Else: End If

If CheckBox2.Value = True Then
Cells (2, 2).Value = TextBox2.Value
Else: End If


Either way, we need a little more information on how you want this to work.

feathers212
04-13-2007, 10:05 AM
To write into the next row:
Private Sub CommandButton1_Click()
Dim lastrow As Long
Dim blankrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
blankrow = lastrow + 1
ActiveSheet.Cells(blankrow, 1) = TextBox1.Value
End Sub

rmpaswan
04-13-2007, 10:52 AM
Hi ,
I am attaching my userform here so that you will better understand my problem.
I have a userform, having five page to input datas (as u can see the form attached). I want five page input datas get transfered into five rectangular box on the sheet. (so i need to code to creat appropriate size of rectangular boxes also)

whenever first time i will click the calculate button, all the input datas with its level will be transfered to the sheet. Now, the user will change the inputs, but not the first page (user informatio). and then will click again the calculate button, so the second click of calculate button will just transfer four pages of data but not the user information page data (since user have been not changed ). Now third four .......all later click of calculate button will just transfer four page of data from the user form.
I hope u will understand my english.
I will really appreciate your help. I am just a beginner to VBA. so plz help me solving this prob.

thankx
Paswan

mdmackillop
04-13-2007, 11:34 AM
Hi Paswan,
I think I understand, but to be sure, can you repost showing the results as you would want to see them.

rmpaswan
04-13-2007, 11:57 AM
Hi mdmackillp,
thankx for your help. the output sheet is attached herewith



Hi Paswan,
I think I understand, but to be sure, can you repost showing the results as you would want to see them.

mdmackillop
04-13-2007, 01:23 PM
Here's a solution to try. I've added a sheet called Placement where you can add the control names and the target address for the data. It simplifies the coding greatly and makes for easy adjustment. Please note that you only have to add the addresses for the first calculation. You'll need to adjust the range names to suit. A1 on Results is used for storing the calculation count.
Here's the basic code
Private Sub Units_button_Click()

Dim cel As Range, Cnt As Long

Sheets("Results").Range("A1") = Sheets("Results").Range("A1") + 1
Cnt = Sheets("Results").Range("A1") - 1

'Add UserInfo and border
If Cnt = 0 Then
Sheets("Results").Range("G5:M14").BorderAround xlContinuous
For Each cel In Range("UserInfo")
Sheets("Results").Range(cel.Offset(, 1).Value) = Me.Controls(cel.Value)
Next
End If

'Rectangle draw
For Each cel In Range("Borders")
Sheets("Results").Range(cel.Value).Offset(Cnt * 40).BorderAround xlContinuous
Next

'Add Data
For Each cel In Range("Zones")
Sheets("Results").Range(cel.Offset(, 1).Value).Offset(Cnt * 40) = Me.Controls(cel.Value)
Next

'Unload Me
End Sub

rmpaswan
04-13-2007, 02:09 PM
Hi mdmackillop,
thank u very much for your help and ur time. I am new memeber here so i cant see the attached files you sent to me. Therefore, please sent the attached file to me on rmpaswan@gmail.com
also i tried to run the code u sent to me an i am getting error in line

For Each cel In Range("UserInform")

Paswan:banghead:

mdmackillop
04-13-2007, 02:25 PM
I don't believe there is such a restriction. Check your download location.
You will get an error as your sample does not have the named ranges.

rmpaswan
04-13-2007, 02:34 PM
how cum i can see download location?

mdmackillop
04-13-2007, 02:38 PM
In Firefox, its noted in Tools/Options/Main

rmpaswan
04-13-2007, 05:05 PM
sorry i cud not find firefox .................plz send it to me on rmpaswan@gmail.com
its important so plz send it to me to my email......




In Firefox, its noted in Tools/Options/Main

rmpaswan
04-14-2007, 02:47 AM
I am completly new to VBA, so cud not understand .................how placement sheet is working and how do i have set the control and its adress on this sheet............I will appreciate ..........if you will explain bit more ....................
One more thing, I want to save and then erase the data on Result sheet when i closes the excel workpage.
How i will i code for this?

Paswan

mdmackillop
04-14-2007, 03:11 AM
I added a temporary button to your userform which will run this code (note I put an x in the function name to disable it in case of "accidents"). This will print out all the control names on the sheet 3.
Private Sub CommandButton1_Click()
For Each ctrl In Me.Controls
i = i + 1
Sheets(3).Cells(i, 1) = ctrl.Name
Next
End Sub
In Column A, put the field names that refer to the User details
In B, insert the cell references for sheet results for each field
In Column D put the field names for all the other fields
In E, insert the cell references for sheet results for each field
In Column G, put the names of the areas you want outlined
In H, insert the cell references for the area

Add range names for the data in Columns A, D and H (not G)

The code works by looing through each cell in the named range, reading the value, and looking up that control on the form. The result is written to the corresponding cell address.
For outlining, it reads the area addresses directly and draws the rectangles.

Each time you run the code, it increments the value in A1 on the Results sheet and offsets the position the results are written by 40 rows.

Please add some data to the userform and step through the code sample to see what's happening.


One more thing, I want to save and then erase the data on Result sheet Please be specific.

rmpaswan
04-14-2007, 03:33 AM
thankx very much ..........for your time.........:thumb .............i mean it..............

when i will open my project (paswan_place_it) and start working using userform, I will take data for many time and get the results corresponding to these datas. After that I will close the my excel project and ................I want the result will get saved and result sheet became clean...................so that next time when some one will open the project, he will find clean result sheet.
I guess u will understand my english..............

Paswan

P.S.: dont u sleep????????..................u r highly hard working person

mdmackillop
04-14-2007, 03:50 AM
This will add the date to a copy of the Results sheet. If this is not apprpriate, let us know.
Sub CopyClearResults()
Sheets("Results").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Results " & Format(Date, "dd.mm.yy")
With Sheets("Results").Cells
.ClearContents
.Borders.LineStyle = xlNone
End With
End Sub

rmpaswan
04-14-2007, 02:16 PM
How will add range names for the data in column A, D and H ?

paswan

mdmackillop
04-14-2007, 02:42 PM
Insert/Name/Define Have a look in Excel help for further info.

rmpaswan
04-14-2007, 02:53 PM
Hi mdmackillop,
I have done with all u suggested in #16, except defining range name. but still the code is not working. I am attaching my excel project .........plz check it.

Paswan

mdmackillop
04-14-2007, 03:09 PM
Try this. Btw, I've deleted Sheet 1 as it is just taking up space.

rmpaswan
04-14-2007, 04:57 PM
Hi mdmackillop,
I am attaching the input data file and expected output data file herewith. plz have a look.

Thankx in advance,

BTW, I cant download the file from this site bcoz i m new member here therefore, i will request to send to my email.

Paswan

rmpaswan
04-15-2007, 02:07 AM
Hi mdmackillop,
A copy of my project u attached with #22, is working well. One hurdle I am facing is not all the datas in the result sheet are labeled. I am attaching one copy of the project where I runned the code for a input data set, so plz look the result sheet. you will find label names for the datas in User Information and Zone Rectangular boxes but not in the remaining rectangular boxes.
I added the label names in Column D of placement sheet (D122:142), but still not putting the label names in the result sheet.
Plz suggest me a solution.

Thankx
Paswan

mdmackillop
04-15-2007, 02:53 AM
I've changed the range names to dynamic, which should cater for future changes. Creating range names is a basic excel function you need to master.
I've changed the first zone field to a combobox, You could also consider a textbox.
The code is failing because the the names in Placement are not correctly entered. I'll leave you to sort that out.

rmpaswan
04-15-2007, 03:00 AM
I guess u have forgott to attach the changed project
plz attache it
paswan

mdmackillop
04-15-2007, 03:19 AM
Here it is

rmpaswan
04-15-2007, 05:51 AM
Its showing error in line

Sheets("Results").Range(cel.Offset(, 1).Value).Offset(Cnt * 40) = Me.Controls(cel.Value)

Plz check this. I have corrected the name but still not putting label names
Paswan

P

rmpaswan
04-15-2007, 06:12 AM
Hi mdmackillop,
I am strugling to solve this prob............:banghead: ..........................plz ..............check the code with input data I have provide, Just get two solutions.

Paswan

P.S: I cud not go for the date bcoz of this prob.............:banghead:

mdmackillop
04-15-2007, 07:54 AM
These are wrong. They must be identical to your form names.
Well_dia_label Frac_lengh_label
I provided you with the code to get all the field names from the form. Using this would avoid such errors.