PDA

View Full Version : VBA - Verification of the sheet > MsgBox



joshua1990
04-19-2017, 04:47 AM
Hi Guys!

I am currently working through my VBA book and the Internet, but have not found an elegant approach to my problem.

I have a worksheet, which is edited by several macros when opening through the workbook_open event.
This means that a user opens the file and receives a finished structured document.
For this to work, however, the first column (A2: A) must contain numbers. Last line is oriented to column B ("Sheets (" tblOne "). Cells (.Rows.Count," B ") End (xlUp) .Row").

I now want to put a macro at the first position in the workbook_open event, which checks whether there are any numbers in A2: Lastrow anywhere. Otherwise the error (msgBox) message "***" should appear. The user should then have the option that the workbook_open event runs the remaining procedures, or the document is closed.



Best regards
Joshua

mdmackillop
04-19-2017, 05:28 AM
Hi Joshua
Call this macro at the start of your event code

Sub Test()
Dim LR As Long, Nums As Long
With Sheets("tblOne")
LR = .Cells(Rows.Count, 2).End(xlUp).Row
Nums = Application.Count(Range(.Cells(2, 1), .Cells(LR, 1)))
If Nums = 0 Then
If MsgBox("Continue?", vbOKCancel) = vbCancel Then ActiveWorkbook.Close False
End If
End With
End Sub

joshua1990
04-21-2017, 12:00 AM
Hey mdmackillop!

Thanks for your contribution and solution.

Unfortunately the code does not work: /

Sub CheckErrors()
Dim LR As Long, Nums As Long
With Sheets("tblExport")
LR = .Cells(Rows.Count, 2).End(xlUp).Row
Nums = Application.Count(Range(.Cells(2, 1), .Cells(LR, 1)))
If Nums = 0 Then
If MsgBox("At least one does not have any numbering.", vbOKCancel) = vbCancel Then ActiveWorkbook.Close False
End If
End With

End Sub


Here is a sample sheet


Edit:
I think a loop is missing

mdmackillop
04-21-2017, 12:33 AM
Unfortunately the code does not work
Can you be a bit more specific?

joshua1990
04-21-2017, 12:45 AM
Excuse me, of course!

The code runs through, but nothing happens. Although there are no numbers in the first lines of column 1, no window appears. If at least one cell is missing a number, the MsgBox should appear

mdmackillop
04-21-2017, 12:56 PM
Here's a revised code. However your original question asked for something different

which checks whether there are any numbers in A2: Lastrow anywhere. Otherwise the error (msgBox) message "***" should appear.


Sub CheckErrors()
Dim LR As Long, Nums As Long, r As Range
With Sheets("tblExport")
LR = .Cells(Rows.Count, 2).End(xlUp).Row
Set r = Range(.Cells(2, 1), .Cells(LR, 1))
If Application.Count(r) <> r.Cells.Count Then
If MsgBox("At least one does not have any numbering.", vbOKCancel) = vbCancel Then ActiveWorkbook.Close False
End If
End With
End Sub