View Full Version : [SOLVED:] Creating a Message Box in Excel VBA
PDKings
04-25-2022, 12:28 PM
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
04-25-2022, 01:19 PM
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
04-25-2022, 01:49 PM
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
04-25-2022, 02:53 PM
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
04-25-2022, 02:58 PM
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
04-25-2022, 03:34 PM
Try this
Teams? Probably
PDKings
04-25-2022, 04:07 PM
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
04-25-2022, 04:47 PM
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
04-25-2022, 04:59 PM
Appreciate you Paul you’ve been a big help in my education on VBA
Paul_Hossler
04-26-2022, 01:57 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.