IF any textboxes on Userform

[rharri1972]

Hello!
I have 4 Textboxes on Userform1
Textbox1 = Part Number
Textbox2= Description
Textbox3= Vendor
Textbox4= Price

Once the data is manually placed in the textboxes I have an "OK" command button that will search out the next available row to place this data on worksheet "Inventory".

However, I do NOT want any data moved to the worksheet unless ALL textboxes have a value.

On my button click...
What I would like to do is say if ANY of the textboxes are blank or empty or ="", whichever is proper, Then do not add data to worksheet until all textboxes have a value.
I would also like to call out by msgbox the value needed for any blank textbox, for example, lets use Textbox 2 in this case. If Textbox2 is blank, and it's value is "Description", then I would like to have a msgbox prompt stating to "Please enter part description" or whichever "value" message would coincide with each textbox.

Once all textboxes have a value then I would want the data to transfer to the worksheet.

Also, is there a way to validate or confirm that the data was "pasted" or "sent" to that row on the sheet prompting a msgbox something like "New part added successfully"?

Any help will be greatly appreciated.

 

[dmt32]

Hi,
always helpful if you can share the code you have with forum but as general idea, maybe following will do what you want

Private Sub CommandButton1_Click()
    Dim i           As Long
    Dim nextrow     As Range
    Dim ws          As Worksheet
    Dim arr(1 To 4) As Variant
    
    Set ws = ThisWorkbook.Worksheets("Inventory")
    
    For i = 1 To 4
        With Me.Controls("Textbox" & i)
            If Len(.Value) = 0 Then
                MsgBox "Please Enter " & Choose(i, "Part Number", "Description", "Vendor", "Price"), _
                48, "Entry Required"
                .SetFocus
                Exit Sub
            Else
                arr(i) = .Value
            End If
        End With
    Next i
    
    Set nextrow = ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, 1)
    
    nextrow.Resize(1, 4).Value = arr
    
    MsgBox "New part added successfully", 64, "Success"
    
End Sub

Dave

 

[rharri1972]

Hi,
always helpful if you can share the code you have with forum but as general idea, maybe following will do what you want

Private Sub CommandButton1_Click()
    Dim i           As Long
    Dim nextrow     As Range
    Dim ws          As Worksheet
    Dim arr(1 To 4) As Variant
   
    Set ws = ThisWorkbook.Worksheets("Inventory")
   
    For i = 1 To 4
        With Me.Controls("Textbox" & i)
            If Len(.Value) = 0 Then
                MsgBox "Please Enter " & Choose(i, "Part Number", "Description", "Vendor", "Price"), _
                48, "Entry Required"
                .SetFocus
                Exit Sub
            Else
                arr(i) = .Value
            End If
        End With
    Next i
   
    Set nextrow = ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, 1)
   
    nextrow.Resize(1, 4).Value = arr
   
    MsgBox "New part added successfully", 64, "Success"
   
End Sub

Dave

Dave... works great! Thank you for the help. I knew variables had to be declared but still wouldn't have ever figured out how to put it together. Would you mind explaining the 48 & 64 in the MsgBox code. I try to read and study to learn and understand. Thanks!

 

[dmt32]

Hi,
glad suggestion does what you want

48 & 64 are the icon constant values for warning & information messages in the msgbox function.

vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.

I normally would use the available constants in the language as they make code more readable but find with the msgbox, these can get too large when you start adding all the required values together so in this case, I just use the total numeric value needed.

You can read more in the VBA helpfile: MsgBox function (Visual Basic for Applications)

Dave

 

[rharri1972]

Hi,
glad suggestion does what you want

48 & 64 are the icon constant values for warning & information messages in the msgbox function.

vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.

I normally would use the available constants in the language as they make code more readable but find with the msgbox, these can get too large when you start adding all the required values together so in this case, I just use the total numeric value needed.

You can read more in the VBA helpfile: MsgBox function (Visual Basic for Applications)

Dave

Thank you Dave!!!