Form Creation and Programming

[marcusja2002]

All

I was able to find a good functional VBA code to place form data into a database(almost completed just need to add defects). See below.

What I want to be able to do is instead of having a column for each of my defect families and types, I want a way to have the program review what is checked and input a new row for every defect type that is checked. If there is a single defect it will be entered once when I run the submit code, if there are multiple checks, it will add a new row with all the txtbox data and the defect family and type.


Private Sub cmbsubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")


iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Please enter a Job Number"
Exit Sub
End If


With ws
.Cells(iRow, 1).Value = Me.txtjo.Value
.Cells(iRow, 2).Value = Me.txtdte.Value
.Cells(iRow, 3).Value = Me.txtmdl.Value
.Cells(iRow, 4).Value = Me.txtsrl.Value
.Cells(iRow, 5).Value = Me.txttchn.Value
.Cells(iRow, 6).Value = Me.txtep.Value
.Cells(iRow, 7).Value = Me.txthtvlt.Value
.Cells(iRow, 8).Value = Me.txtwt.Value
End With


'clear the data
Me.txtjo.Value = ""
Me.txtdte.Value = ""
Me.txtmdl.Value = ""
Me.txtsrl.Value = ""
Me.txttchn.Value = ""
Me.txtep.Value = ""
Me.txthtvlt.Value = ""
Me.txtwt.Value = ""
Me.txtjo.SetFocus
End Sub

Thank you in advance.

 

[marcusja2002]

I found another piece of code I think could help

[FONT=&quot]Sub[/FONT][FONT=&quot] TextBox_ActiveX_Loop()[/FONT]

[FONT=&quot]Dim[/FONT][FONT=&quot] txtbx [/FONT][FONT=&quot]As[/FONT][FONT=&quot] OLEObject[/FONT]

[FONT=&quot]'Loop through each ActiveX Control TextBox in ActiveSheet[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]For[/FONT][FONT=&quot] [/FONT][FONT=&quot]Each[/FONT][FONT=&quot] txtbx [/FONT][FONT=&quot]In[/FONT][FONT=&quot] ActiveSheet.OLEObjects[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]If[/FONT][FONT=&quot] TypeName(txtbx.Object) = "TextBox" [/FONT][FONT=&quot]Then[/FONT]
[FONT=&quot] Debug.Print txtbx.Name[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]End[/FONT][FONT=&quot] [/FONT][FONT=&quot]If[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Next[/FONT][FONT=&quot] txtbx[/FONT]

[FONT=&quot]End[/FONT][FONT=&quot] [/FONT][FONT=&quot]Sub

The problem is, I'm not 100% sure how this works. Does it automatically know the txtboxes names or its just looking for any text boxes? There are specific boxes I would want to skip checking since they are intended to be there for every defect text box. [/FONT]

 

[My Aswer Is This]

Trying to use scripts you have found but do not understand is hard to do.
You said:
it will add a new row with all the txtbox data and the defect family and type.

Where are we going to find the:
defect family and type.

And you want all this data to be entered into column (1)
one row after the other.

What will go into columns 2 to ???

 

[Kenneth Hobson]

Parse the txtbx.Name to get what you want. Maybe you want prefix check for the Debug line:

if Left(txtbx.Name, 3) ="txt" then
  Debug.Print "Will do my thing here", txtbx.Name
  Else
    Debug.Print "Skipped", txtbx.Name
End If

 

[Norie]

The 2 sets of code you posted seem to be for different types of textboxes.

The first appears to be for textboxes on a userform and the other for ActiveX textboxes on a worksheet.

What is it you actually have?

 

[marcusja2002]

I agree this is hard, I'm very much a beginner, so I'm trying to throw darts at a dart board in an attempt to learn what I'm supposed to be asking.

I have a form I created with a mixture of text boxes check boxes labels and Option buttons.

My goal is to have the program take a list of standard information; job order, date, operator etc and put it in the next available row and in the same preset columns every time I run the program.

The initial program does that if I have only a single defect. The problem comes in is when I have multiple. My goal is to have it look through my list of defect check boxes and everywhere it finds a check I get a new row of data. All the standard information along with the defect family connected to a check box, the actual defect connected to its own check box (both in their own column) and a txtbox that is next to the actual defect with times.

The end data would look something like below:

[TABLE="width: 878"]
<colgroup><col width="77" style="width:58pt"> <col width="75" style="width:56pt"> <col width="58" style="width:44pt"> <col width="52" style="width:39pt"> <col width="73" style="width:55pt"> <col width="87" style="width:65pt"> <col width="101" style="width:76pt"> <col width="60" style="width:45pt"> <col width="94" style="width:71pt"> <col width="126" style="width:95pt"> <col width="75" style="width:56pt"> </colgroup><tbody>[TR]
[TD="class: xl128, width: 77"]Job Order #[/TD]
[TD="class: xl128, width: 75"]Date[/TD]
[TD="class: xl128, width: 58"]Model #[/TD]
[TD="class: xl128, width: 52"]Serial #[/TD]
[TD="class: xl128, width: 73"]Technician[/TD]
[TD="class: xl128, width: 87"]Engine Part #[/TD]
[TD="class: xl128, width: 101"]Heater Voltage[/TD]
[TD="class: xl128, width: 60"]Wattage[/TD]
[TD="class: xl128, width: 94"]Failed System[/TD]
[TD="class: xl128, width: 126"]Failure Description[/TD]
[TD="class: xl128, width: 75"]Time to Fix[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]1/1/2017[/TD]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]1234[/TD]
[TD="class: xl129"]JAM[/TD]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]5[/TD]
[TD="class: xl129, align: right"]5[/TD]
[TD="class: xl128"]Leaks[/TD]
[TD="class: xl128"]Fuel[/TD]
[TD="class: xl128, align: right"]60[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]1/1/2017[/TD]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]1234[/TD]
[TD="class: xl129"]JAM[/TD]
[TD="class: xl129, align: right"]123[/TD]
[TD="class: xl129, align: right"]5[/TD]
[TD="class: xl129, align: right"]5[/TD]
[TD="class: xl128"]Oil Gauge[/TD]
[TD="class: xl128"]Oil Gauge[/TD]
[TD="class: xl128, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Another alternative to inputing the failed system, I'm thinking is an if/then statement or something that points toward a specific system with out haveing the need for a check box.

Thank you all for your help.

 

[marcusja2002]

A quick Lay out of the report: Legend (L) = Label, (T) = Text box, (C) = Check Box, (O) = Options Button, * = item would have its own dedicated column


Job Order (L) ==> *[___] (T) Date (L) ==> *[___] (T) Model (L) ==> *[___] (T)


Serial# (L) ==> *[___] (T) Technician (L) ==> *[___] (T)


Date (L) ==> *[___] (T) Heater (L) ==> *[___] (T)


Wattage (L) ==> *[___] (T)


Leak (C) CAC (C) ==> [___] (T)


Fuel (C) ==> [___] (T) Engine Start (C) ==> [___] (T)
Oil (C) ==> [___] (T) Engine Stop (C) ==> [___] (T)
Coolant (C) ==> [___] (T) Engine Performance (C) ==> [___] (T) & *[___] (T) one box is for extra information
Cool loop (C) ==> [___] (T)


Panel (C) ==> [___] (T)
Power (C) ==> [___] (T) Alt(C) ==> [___] (T)
Pick (C) ==> [___] (T) Cool Sel (C) ==> [___] (T)


Work with switch (L) ==> *[Yes (O) No (O)]




Repairs & comments (L) ==> *[___] (T)

 

[marcusja2002]

Check box how to change true / false to other possibilities

I have multiple check boxes. Lets say one is called bob and the other bill

If I check one that says bob I want to input the word bob into cell A1, but if a different box is checked I want bill input into the same cell.

There could be up to 20 items that could show up in a cell, so I want the specific name to be connected to the check box, not an if than formula pre populated in the cell.

Thank you in advance,

 

[ranman256]

Re: Check box how to change true / false to other possibilities

you dont use a screwdriver to hammer in nails. The right tool for the right job.
If you want the values BOB , BILL, etc, then use a combo box and fill it with BOB , BILL, and 20 other values.
Not a 2 value checkbox.

 

[marcusja2002]

Re: Check box how to change true / false to other possibilities

Let me state it another way

For my form to function the way I want it, I need the check box to return a word instead of true and a blank ("") if false.