URGENT HELP IN A VBA CODE

[AYSHANA]

GREAT DAY TO ALL!!

I NEED HELP IN THIS USER FORM
I WANT TO CREAT A FORMULA THAT WILL NOTIFY TO REFILL ALL THE BLANKS WHEN I WILL PRESS (SAVE) IN USER FORM.

THE FOLLOWING IS CODE, THANK YOU.


Private Sub CommandButton1_Click()

Dim TargetRow As Integer
Dim Component As String 'Component


TargetRow = Sheets("ENGINE").Range("B3").Value + 1
Component = TEXT_COMPO

Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 0).Value = TEXT_DEL
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 1).Value = TEXT_COMPO
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 2).Value = TEXT_REC
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 3).Value = TEXT_DATE
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 4).Value = TEXT_DISP

Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 5).Value = HEMO
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 6).Value = CLOT
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 7).Value = BAG
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 8).Value = LABEL
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 9).Value = COOLANT
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 10).Value = SEGMENT
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 11).Value = TEMP
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 12).Value = RECBY
Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 13).Value = DATIME

Unload FORM1

MsgBox Component & " was added to the Database", 0, "Complete"



End Sub

Private Sub CommandButton2_Click()

Unload FORM1

End Sub

 

[welshgasman]

Have tried a simple search?
clear excel user form - Google Search

 

[Logit]

How to check if all TextBoxes on an Excel UserForm are filled?

Option Explicit

Public Sub ValidateCheckBoxes()
    Dim EmptyBoxesFound As Boolean

    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Text = vbNullString Then
                EmptyBoxesFound = True
                Exit For
            End If
        End If
    Next Ctrl

    If EmptyBoxesFound Then
        MsgBox "At least one box is not filled.", vbExclamation
    Else
        MsgBox "All boxes are filled.", vbInformation
    End If
End Sub

 

[welshgasman]

My reading of the initial post was to be able to clear the controls ready for a new record?
Apologies if that was not the case.

 

[rlv01]

Perhaps something like this.

Private Sub CommandButton1_Click()
    
    Dim TargetRow As Integer
    Dim Component As String                           'Component
    Dim MissingData As Boolean
    Dim Ctrl As Control
    
    For Each Ctrl In Me.Controls
        Select Case TypeName(Ctrl)
            Case "TextBox", "ComboBox"
                If Trim(Ctrl.Text) = "" Then
                    MissingData = True
                    Ctrl.BackColor = RGB(255, 255, 153)   'color highlight to indicate missing data
                Else
                    Ctrl.BackColor = vbWhite              'if there is data
                End If
        End Select
    Next Ctrl
    
    If MissingData Then
        MsgBox "Please fill highlighted empty boxes before saving", vbExclamation
        Exit Sub
    End If
    
        TargetRow = Sheets("ENGINE").Range("B3").Value + 1
        Component = TEXT_COMPO
    
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 0).Value = TEXT_DEL
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 1).Value = TEXT_COMPO
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 2).Value = TEXT_REC
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 3).Value = TEXT_DATE
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 4).Value = TEXT_DISP
    
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 5).Value = HEMO
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 6).Value = CLOT
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 7).Value = BAG
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 8).Value = Label
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 9).Value = COOLANT
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 10).Value = SEGMENT
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 11).Value = TEMP
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 12).Value = RECBY
        Sheets("Database").Range("Delivery_Note").Offset(TargetRow, 13).Value = DATIME
    
        Unload FORM1
    
        MsgBox Component & " was added to the Database", 0, "Complete"

End Sub