Creating a Message Box in Excel VBA

[PDKings]

Hello everyone,

My goal is to create an excel VBA code for my sheet that tracks parts that have been ordered. I have a problem where employees will either forget or neglect to put information in cells for the parts in each row.

Not sure if it’s possible but I’d like for it to see them put the part name in A1 and then makes sure they fill out the rest of the cells in the corresponding row and if not gives them a reminder message to not leave it blank.

I’ve tried digging into this and the best I could do myself was have to error pop up from any cell being empty. I don’t have the code with me atm. I’ll reply if I can get it. But Do y’all know where I could start? Or if I’m wasting my time?

[Paul_Hossler]

Pop up boxes can get annoying

I'd think that highlight a missing required field might be better

29676

As a possible Proof of Concept --

This is an event handler that goes into the worksheet code module (look at attachment)

If there's a PN and missing data it highlights the missing cell.

Enter something into the highlighted cell and the highlight goes away

Easy enough to add some error checking, bad data, negative qty, etc.

There I think it'd be appropriate to have a Msgbox




Option Explicit


Const numStartCol As Long = 1 ' col number for Part Number
Const numCols As Long = 4


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rData As Range, rChanged As Range
Dim r As Long, c As Long

Set rChanged = Target.Cells(1, 1)

If rChanged.Row = 1 Then Exit Sub
If rChanged.Column > numStartCol + numCols - 1 Then Exit Sub ' adjust
If rChanged.Column < numStartCol Then Exit Sub ' adjust

Set rData = rChanged.EntireRow.Cells(1, numStartCol).Resize(1, numCols)


With rData
If Len(.Cells(numStartCol).Value) > 0 Then
For c = 2 To numCols
If Len(.Cells(numStartCol + c - 1).Value) = 0 Then
.Cells(numStartCol + c - 1).Interior.ColorIndex = 40
Else
.Cells(numStartCol + c - 1).Interior.ColorIndex = xlColorIndexNone
End If
Next
End If
End With

End Sub


If you need more help or other ideas, attach a small sampel workbook and more information

[PDKings]

Thanks Paul! Your formula works great but I'm not sure if it's going to be enough when it comes to people leaving missing info in the document. We use it to track orders and who picked them up. So we generally refer to this document when things go missing. So missing information is kind of important and the message box forcing people to move to the box that requires info through the retry button is enticing. I used condition formatting to get a similar result that your code provides and it generally gets ignored. I'll provide an example of the document as well.


29678

[Paul_Hossler]

When do you want the MsgBoxes to display?

Example

I enter ABC123 in A3 and hit enter

Do you want 12 Msgboxes, one each for missing data on that line

Job Number
PART NUMBER
Part Number 2
QUANTITY
Zone
INDEX
LOCATION
Order Number
ORDERED BY
DATE ORDERED
PICKED UP BY
DATE

That's a lot of clicking

Another more user friendly option would be to check for missing data with leaving the worksheet, highlighting missing fields, and then do a single Msgbox "Can't Leave. Still have 23 missing data fields"

Personally, I'd combine my first suggestion and highlight as I go (adding error checking) and then a Msgbox when leaving if there are still errors to be corrected

[PDKings]

Is there a way to do that but for when switching sheets? Instead of leaving the whole document because that would absolutely be better. Also does VBA work inside of teams?

[Paul_Hossler]

Try this

Teams? Probably

[PDKings]

Works great Paul thanks a lot this is exactly what I needed! Do you think a newbie like me could possibly edit this to fit similar sheets?

[Paul_Hossler]

Works great Paul thanks a lot this is exactly what I needed! Do you think a newbie like me could possibly edit this to fit similar sheets?

Sure

Different formats might be a little tricky

Some things would more appropriately be handled as a general purpose sub and passing parameters

So these are now general purpose and in a standard module. They're called by WS specific modules that pass the WS name and some column numbers, etc




Option Explicit


Sub ShadeCells(rCol1 As Range, iStartCol As Long, iNumCols As Long)
Dim r As Range
Dim c As Long

Set r = rCol1.EntireRow.Cells(1, iStartCol).Resize(1, iNumCols)

With r
If Len(.Cells(iStartCol).Value) > 0 Then
For c = 2 To iNumCols
If Len(.Cells(iStartCol + c - 1).Value) = 0 Then
.Cells(iStartCol + c - 1).Interior.ColorIndex = 40
Else
.Cells(iStartCol + c - 1).Interior.ColorIndex = xlColorIndexNone
End If
Next
End If
End With
End Sub


Function ErrorCount(ws As Worksheet) As Long
Dim r As Range

Set r = ws.Cells(1, 1).CurrentRegion
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)

ErrorCount = 0

On Error Resume Next
ErrorCount = r.SpecialCells(xlCellTypeBlanks).Count
On Error GoTo 0
End Function




Sub ErrorMessage(ws As Worksheet)
Call MsgBox("You still have " & Format(ErrorCount(ws), "#,##0") & " pieces of missing data on Worksheet " & ws.Name, vbCritical + vbOKOnly, "Missing Data")
End Sub





SOmething similar to this is in each WS module and passing information to the general purpose macros above



Option Explicit


'just for this sheet
Const numStartCol As Long = 1 ' col number for Part Number
Const numCols As Long = 13




Private Sub Worksheet_Activate()
Dim rowLast As Long, r As Long

rowLast = Cells(Rows.Count, 1).End(xlUp).Row

For r = 3 To rowLast
Call ShadeCells(Cells(r, 1), numStartCol, numCols)
Next r
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rChanged As Range

For Each rChanged In Target.Cells
With rChanged
If .Row < 3 Then GoTo NextCell
If .Column > numStartCol + numCols - 1 Then GoTo NextCell
If .Column < numStartCol Then GoTo NextCell

Call ShadeCells(rChanged.EntireRow.Cells(1, 1), numStartCol, numCols)
End With
NextCell:
Next
End Sub





The last piece is in the THisWorkbook module. Displaying the error msgbox only requires the WS name so Workbook_SheetDeactivate(() can be put in this modue instead of in every WS module



Option Explicit


Private Sub Workbook_Open()
Worksheets("Parts1").Select
End Sub




Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If ErrorCount(Sh) > 0 Then Call ErrorMessage(Sh)
End Sub







Sounds a little confusing (overwhelming ?) but you'll get it

[PDKings]

Appreciate you Paul you’ve been a big help in my education on VBA

[Paul_Hossler]

Updated per your PM

Ref: My post #8


Different formats might be a little tricky



It seems like there's a variety of worksheet formats in the workbook so ...

1. I made "Parts Log" in A1 the signature for parts worksheets so the macros can tell if it's leaving a Parts worksheet

2. Added a check for a Parts worksheet that has no parts lines (ErrorCount = 0) so no error message