Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

[SOLVED] vba debug help

Copper Contributor

Hello all. So i have a form that i use to input information for inventory. Im having a issues on some of the inputs. Im needing help writing a code that will pop up a message box (instead of debug box) that will say to please enter "xx" information. At the moment, if i forget to enter a date in the date box, and hit enter, i receive a debug box and it wants me to debug the issue. But i would like to change that to a msgbox telling the user to enter all the needed info. Also is there a way to make it generic so i can insert it into other parts of the code? Any advice would be appreciated.

 

I guess what im trying to say in so many words, is there a way to put a validation in place for each of my form boxes to make sure they have a entry? and if they dont, to show a msgbox saying " please input a -whatever box is blank-" instead of having the debug window show?

 

Heres the section of code:

 

Im having the issue with row 13 but would like a generic formula validation for rows 11-14

 

 

 

Private Sub add_Click()
Set invoutws = ThisWorkbook.Sheets("Inventory In-Out")
Set varws = ThisWorkbook.Sheets("VARIETY PACK")
Set invmasterws = ThisWorkbook.Sheets("Inventory MASTER")
nrowmaster = WorksheetFunction.CountA(invmasterws.Columns(2)) + 1
nrowvar = varws.Cells(varws.Rows.Count, 2).End(xlUp).Row

nrows = WorksheetFunction.CountA(invoutws.Columns(1))  'Cells(invoutws.Rows.Count, 1).End(xlUp).Row

If Me.codeval <> "" Then
            invoutws.Range("A" & nrows + 1) = Me.codeval
            invoutws.Range("B" & nrows + 1) = Me.typeval
            invoutws.Range("C" & nrows + 1) = DateSerial(Year(Now), Split(Me.dateval, "-")(0), Split(Me.dateval, "-")(1))
            invoutws.Range("D" & nrows + 1) = Me.qty * 1 = ""

 

 

 

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@BlueCollarVending 

Something like this:

 

Private Sub add_Click()
If Len(Me.dateval)=0 Then
    Msgbox "Please enter a date!"
    Exit Sub
End If
If Not IsDate(Me.dateval) Then
    Msgbox "Please enter a valid date!"
    Exit Sub
End If
Set invoutws = ThisWorkbook.Sheets("Inventory In-Out")
Set varws = ThisWorkbook.Sheets("VARIETY PACK")
Set invmasterws = ThisWorkbook.Sheets("Inventory MASTER")
nrowmaster = WorksheetFunction.CountA(invmasterws.Columns(2)) + 1
nrowvar = varws.Cells(varws.Rows.Count, 2).End(xlUp).Row

nrows = WorksheetFunction.CountA(invoutws.Columns(1))  'Cells(invoutws.Rows.Count, 1).End(xlUp).Row

If Me.codeval <> "" Then
            invoutws.Range("A" & nrows + 1) = Me.codeval
            invoutws.Range("B" & nrows + 1) = Me.typeval
            invoutws.Range("C" & nrows + 1) = DateSerial(Year(Now), Split(Me.dateval, "-")(0), Split(Me.dateval, "-")(1))
            invoutws.Range("D" & nrows + 1) = Me.qty * 1 = ""
Thank you
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@BlueCollarVending 

Something like this:

 

Private Sub add_Click()
If Len(Me.dateval)=0 Then
    Msgbox "Please enter a date!"
    Exit Sub
End If
If Not IsDate(Me.dateval) Then
    Msgbox "Please enter a valid date!"
    Exit Sub
End If
Set invoutws = ThisWorkbook.Sheets("Inventory In-Out")
Set varws = ThisWorkbook.Sheets("VARIETY PACK")
Set invmasterws = ThisWorkbook.Sheets("Inventory MASTER")
nrowmaster = WorksheetFunction.CountA(invmasterws.Columns(2)) + 1
nrowvar = varws.Cells(varws.Rows.Count, 2).End(xlUp).Row

nrows = WorksheetFunction.CountA(invoutws.Columns(1))  'Cells(invoutws.Rows.Count, 1).End(xlUp).Row

If Me.codeval <> "" Then
            invoutws.Range("A" & nrows + 1) = Me.codeval
            invoutws.Range("B" & nrows + 1) = Me.typeval
            invoutws.Range("C" & nrows + 1) = DateSerial(Year(Now), Split(Me.dateval, "-")(0), Split(Me.dateval, "-")(1))
            invoutws.Range("D" & nrows + 1) = Me.qty * 1 = ""

View solution in original post