Help With Vba Code! Please

[lovett10]

Hi i need help writing code for my excel document.

I have attached the document: 5.xlsm

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]

What are those boxes around Switches meant to be, checkboxes or textboxes?

[lovett10]

Theyre text boxes to enter quantity

[Bob Phillips]

[vba]

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

[lovett10]

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]

The code is quite simple, examine it and work out what to pick out to add the other sheet.

[lovett10]

ok thanks for the help

[Paul_Hossler]

Typo???


[VBA]
ws.Cells(NextRow, "M").Value = .OLEObjects("TextBox12").Object.Value
ws.Cells(NextRow, "N").Value = .OLEObjects("TextBox12").Object.Value
[/VBA]

12 or 13?

Paul

[Bob Phillips]

Yeah, you are right, it should be 13. The perils of cut and paste.