How to check after a new row has been added to a table, if there is a empty field. If so insert a text string

skeeeter56

New Member
Joined
Nov 26, 2016
Messages
42
Office Version
  1. 2019
Platform
  1. Windows
Hi I have a file that is use to record daily data, it is entered by a form.
The code below runs when you hit OK on the form, its all works well.
Occasionally the value
VBA Code:
cboEmployee.Value
is left empty as the name is not in the data validation list that is use on form to select.
When this occurs I would like to just simply have a value "Outside Relief" added.
I am thinking it may be done in the code before the row is added if the
VBA Code:
cboEmployee.Value
is empty


VBA Code:
Private Sub bOkay2_Click()


Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long


Set tbl = Sheets("Missed Scan Summary").ListObjects("tblncidents")

    If tbl.ListRows.Count > 0 Then

        Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
        For col = 1 To lrow.Columns.Count
            If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
                tbl.ListRows.Add
                Exit For
            End If
        Next
    End If
    
    lrow2 = tbl.ListRows.Count

     tbl.DataBodyRange(lrow2, 1).Value = CDate(txtDate.Value)

     tbl.DataBodyRange(lrow2, 2).Value = cboRound.Value

     tbl.DataBodyRange(lrow2, 3).Value = cboEmployee.Value

     tbl.DataBodyRange(lrow2, 4).Value = cboType.Value
     
     tbl.DataBodyRange(lrow2, 5).Value = txtArticleNo.Value
     
     tbl.DataBodyRange(lrow2, 6).Value = cboReason.Value
     
     tbl.DataBodyRange(lrow2, 7).Value = cboTeamLeader.Value
     
    
     

     Unload Me


End Sub
,

If someone is able to assist me I would be most grateful
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Untested, but would this do it?
VBA Code:
tbl.DataBodyRange(lrow2, 3).Value = IIf(Len(cboEmployee.Value) = 0, "Outside Relief", cboEmployee.Value)
 
Upvote 0
Solution
Untested, but would this do it?
VBA Code:
tbl.DataBodyRange(lrow2, 3).Value = IIf(Len(cboEmployee.Value) = 0, "Outside Relief", cboEmployee.Value)
That has done exactly what I wanted , thanks so much for you help
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top