Form Creation and Programming

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Parse the txtbx.Name to get what you want. Maybe you want prefix check for the Debug line:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 27, 2010
Messages
107
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:

Job Order #DateModel #Serial #TechnicianEngine Part #Heater VoltageWattageFailed SystemFailure DescriptionTime to Fix
1231/1/20171231234JAM12355LeaksFuel60
1231/1/20171231234JAM12355Oil GaugeOil Gauge30

<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>
</tbody>

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

Board Regular
Joined
Apr 27, 2010
Messages
107
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

Board Regular
Joined
Apr 27, 2010
Messages
107
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

Well-known Member
Joined
Jun 17, 2014
Messages
1,840
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

Board Regular
Joined
Apr 27, 2010
Messages
107
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.
 

Forum statistics

Threads
1,089,462
Messages
5,408,404
Members
403,202
Latest member
Novice43

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top