transfer of data from userform to sheet

[rmpaswan]

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

[feathers212]

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

1. If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 3. OR
2. 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:

[vba]
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
[/vba]

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

[mdmackillop]

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]

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:

1. If check box 1 and 3 are checked, data for 1 goes in column 1 and data for 3 goes in column 3. OR
2. 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:

[vba]
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
[/vba]

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

[feathers212]

To write into the next row:
[VBA]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
[/VBA]

[rmpaswan]

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]

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

[rmpaswan]

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]

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
[VBA]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[/VBA]

[rmpaswan]

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

[mdmackillop]

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]

how cum i can see download location?

[mdmackillop]

In Firefox, its noted in Tools/Options/Main

[rmpaswan]

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]

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]

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.
[vba]Private Sub CommandButton1_Click()
For Each ctrl In Me.Controls
i = i + 1
Sheets(3).Cells(i, 1) = ctrl.Name
Next
End Sub
[/vba] 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]

thankx very much ..........for your time......... .............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]

This will add the date to a copy of the Results sheet. If this is not apprpriate, let us know.
[vba]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

[/vba]

[rmpaswan]

How will add range names for the data in column A, D and H ?

paswan

[mdmackillop]

Insert/Name/Define Have a look in Excel help for further info.