PDA

View Full Version : Help With Vba Code! Please



lovett10
08-19-2011, 04:32 AM
Hi i need help writing code for my excel document.

I have attached the document: 6441

What i want it to be able to do:

1. In the "Boiler" Worksheet, i want the information people enter to be stored on another sheet "CollectedInfo". and then the information to reset on the "Boiler" sheet so they can add another item if they need to.

An explanation would be amazing as i could then change the code to work with other sheets im going to make.

Thanks in advanced

Bob Phillips
08-19-2011, 04:42 AM
What are those boxes around Switches meant to be, checkboxes or textboxes?

lovett10
08-19-2011, 04:48 AM
Theyre text boxes to enter quantity

Bob Phillips
08-19-2011, 04:54 AM
Sub Test()
Dim cbo As OLEObject
Dim ws As Worksheet
Dim NextRow As Long

Set ws = Worksheets("CollectedInfo")
If ws.Range("A1").Value2 = "" Then

ws.Range("A1:N1").Value = Array("Boiler", "Qty", "2Pos", "3Pos", "On", "Off", "2Pos", "3Pos", "On", "Off", "2Pos", "3Pos", "On", "Off")
NextRow = 2
Else

NextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
End If

With Worksheets("Boiler")

ws.Cells(NextRow, "A").Value = .OLEObjects("ComboBox1").Object.Value
ws.Cells(NextRow, "B").Value = .OLEObjects("TextBox1").Object.Value
ws.Cells(NextRow, "C").Value = .OLEObjects("TextBox2").Object.Value
ws.Cells(NextRow, "D").Value = .OLEObjects("TextBox3").Object.Value
ws.Cells(NextRow, "E").Value = .OLEObjects("TextBox4").Object.Value
ws.Cells(NextRow, "F").Value = .OLEObjects("TextBox5").Object.Value
ws.Cells(NextRow, "G").Value = .OLEObjects("TextBox6").Object.Value
ws.Cells(NextRow, "H").Value = .OLEObjects("TextBox7").Object.Value
ws.Cells(NextRow, "I").Value = .OLEObjects("TextBox8").Object.Value
ws.Cells(NextRow, "J").Value = .OLEObjects("TextBox9").Object.Value
ws.Cells(NextRow, "K").Value = .OLEObjects("TextBox10").Object.Value
ws.Cells(NextRow, "L").Value = .OLEObjects("TextBox11").Object.Value
ws.Cells(NextRow, "M").Value = .OLEObjects("TextBox12").Object.Value
ws.Cells(NextRow, "N").Value = .OLEObjects("TextBox12").Object.Value
End With
End Sub

lovett10
08-19-2011, 05:01 AM
Oh my god i love you :)... one thing what would i have to change in the code to allow for other sheets such as the "Conduit" one??

Bob Phillips
08-19-2011, 05:11 AM
The code is quite simple, examine it and work out what to pick out to add the other sheet.

lovett10
08-19-2011, 05:14 AM
ok thanks for the help :)

Paul_Hossler
08-20-2011, 06:12 AM
Typo???



ws.Cells(NextRow, "M").Value = .OLEObjects("TextBox12").Object.Value
ws.Cells(NextRow, "N").Value = .OLEObjects("TextBox12").Object.Value


12 or 13?

Paul

Bob Phillips
08-20-2011, 02:05 PM
Yeah, you are right, it should be 13. The perils of cut and paste.