Userform - Checklist result to database sheet

[Ben171]

I have created an excel database, it in involves the following userform:

The database user fills this form in and then data is transferred to the sheet "database" using the following:

[CODE=vba]Sub Submit()

'code for submitting data to database

    Dim sh As Worksheet
    Dim iRow As Long
 
    Set sh = ThisWorkbook.Sheets("Database")
    
    'finds first available blank row in database to add data to
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    
    With sh
    
    'adding each row to database, iRow is column in Database
    'Be sure to add any new columns to the below
    
        .Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        
        .Cells(iRow, 2) = frmForm.ModelNo.Value
        
        .Cells(iRow, 3) = frmForm.PartNo.Value
        
        .Cells(iRow, 4) = frmForm.WorksOrderNo.Value
        
        .Cells(iRow, 5) = frmForm.SerialNo.Value
        
        .Cells(iRow, 6) = frmForm.MaterialNo.Value
        
        .Cells(iRow, 7) = frmForm.SerialNumber.Value
        
        .Cells(iRow, 8) = frmForm.txtType.Value
        
        .Cells(iRow, 9) = frmForm.txtSize.Value
        
        .Cells(iRow, 10) = frmForm.txtWKPRESS.Value
        
        .Cells(iRow, 11) = frmForm.txtCertDate.Value
        
        .Cells(iRow, 12) = frmForm.BatchNo.Value
        
        .Cells(iRow, 13) = frmForm.JobNo.Value
        
        .Cells(iRow, 14) = frmForm.DateOfManufacture.Value
        
        .Cells(iRow, 15) = frmForm.Label47.Caption
        
        .Cells(iRow, 16) = Application.UserName
        
        .Cells(iRow, 17) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
        
        .Cells(iRow, 18) = frmForm.TextBox25.Value
        
        .Cells(iRow, 19) = frmForm.Certificate.Value
        
        .Cells(iRow, 20) = frmForm.BarRating.Value
        
    
    End With

I have now added a checklist at the bottom of the form, see above form image. How can i capture whether this box is ticked or not and then produce a YES or NO on iRow 21 (next available column) of the database sheet.

Is it possible to give the checkbox a value i.e if ticked then YES then just put for example ".Cells(iRow, 21) = frmForm.Checkbox1.Value"




End Sub[/CODE]

 

[dmt32]

Hi,
try following & see if does what you want

.Cells(iRow, 21) = IIf(Me.Checkbox1.Value, "Yes", "No")

Note The Me keyword, using this negates need to hard code the form object name with each control

Dave

 

[Ben171]

Hi,
try following & see if does what you want

.Cells(iRow, 21) = IIf(Me.Checkbox1.Value, "Yes", "No")

Note The Me keyword, using this negates need to hard code the form object with each control

Dave

Thanks for the response, this looks like what I am after, but not having any luck with this. It wants me to close the brackets before the 'Me'

 

[dmt32]

Thanks for the response, this looks like what I am after, but not having any luck with this. It wants me to close the brackets before the 'Me'

My error I selected wrong code tags

, I re-posted the code

.Cells(iRow, 21) = IIf(Me.Checkbox1.Value, "Yes", "No")

Dave

 

[Ben171]

My error I selected wrong code tags

, I re-posted of the code

.Cells(iRow, 21) = IIf(Me.Checkbox1.Value, "Yes", "No")

Dave

Exactly what I was after, thank you! A lot easier than expected.
It didn't like the Me keyword, but worked with the form name instead. Is this because you cannot use the Me keyword in a standard module?

 

[dmt32]

Exactly what I was after, thank you! A lot easier than expected.
It didn't like the Me keyword, but worked with the form name instead. Is this because you cannot use the Me keyword in a standard module?

yes that is correct - I thought code was in your forms code page.

Dave

 

[Ben171]

yes that is correct - I thought code was in your forms code page.

Dave

my fault, should have been clearer. Thanks again for your help

 

[dmt32]

my fault, should have been clearer. Thanks again for your help

not at all - glad resolved your problem & appreciate feedback

Dave