Forum Discussion

BlueCollarVending's avatar
BlueCollarVending
Copper Contributor
Nov 16, 2023
Solved

[SOLVED] vba debug help

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 = ""

 

 

 

 

Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Nov 16, 2023

    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 = ""
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Nov 16, 2023

    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 = ""

Resources