How to activate cell position when empty

Question

I'm currently working on a workbook with "SHEET1" and "SHEET2". Now, when there's an empty cell in SHEET1 or SHEET2, it will prompt the user to add values on the mandatory cells.

Example:
CELL B2 = NULL in SHEET1
- It will prompt the user that there's a missing value on that cell and redirect to that cell but once the you clicked the message box, it will redirect to the SHEET2.

PROBLEM: If the missing cell is for SHEET1, I dont want to activate the SHEET2.

Here's my code below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim maxrow As Long
Dim maxrow2 As Long
Dim errcnt As Long
Dim errcnt2 As Long

  ThisWorkbook.Sheets("SHEET1").Select
  ThisWorkbook.Sheets("SHEET1").Cells(1, 1).Select
  Range("A1048576").Select
  Selection.End(xlUp).Select
  maxrow = Selection.Row

  For I = 2 To maxrow
    If ThisWorkbook.Sheets("SHEET1").Cells(I, 1).Value = "YES" Then
      For j = 2 To 10
        If ThisWorkbook.Sheets("SHEET1").Cells(I, j).Value = "" Then
          ThisWorkbook.Sheets("SHEET1").Select
          ThisWorkbook.Sheets("SHEET1").Cells(I, j).Activate
          errcnt = errcnt + 1
        End If
      Next j 
    End If

    If ThisWorkbook.Sheets("SHEET1").Cells(I, 1).Value = "NO" Then
      For j = 2 To 10
        If ThisWorkbook.Sheets("SHEET1").Cells(I, j).Value = "" Then
          ThisWorkbook.Sheets("SHEET1").Select
          ThisWorkbook.Sheets("SHEET1").Cells(I, j).Activate
          errcnt = errcnt + 1
        End If
      Next j
    End If    
Next I

If errcnt > 0 Then
    MsgBox "There are " & errcnt & " fields missing", vbExclamation, "Missing Value"
    Cancel = True
Else
End If

ThisWorkbook.Sheets("SHEET2").Select
ThisWorkbook.Sheets("SHEET2").Cells(1, 1).Select
Range("A1048576").Select
Selection.End(xlUp).Select
maxrow2 = Selection.Row

For X = 2 To maxrow2
  If ThisWorkbook.Sheets("SHEET2").Cells(X, 1).Value = "YES" Or ThisWorkbook.Sheets("SHEET2").Cells(X, 1).Value = "NO" Then 
    For j = 2 To 10
      If ThisWorkbook.Sheets("SHEET2").Cells(X, j).Value = "" Then
        ThisWorkbook.Sheets("SHEET2").Select
        ThisWorkbook.Sheets("SHEET2").Cells(X, j).Activate
        errcnt2 = errcnt2 + 1
      End If
    Next j
  End If
Next X

If errcnt2 > 0 Then
  MsgBox "There are " & errcnt & " fields missing", vbExclamation, "Missing Value"
  Cancel = True
Else
End If

End Sub

I believe this is because of this code:

ThisWorkbook.Sheets("SHEET2").Select

You did not state what error you are getting, nor did you ask a question. — Scott Craner, Oct 25 '16 at 13:26
@ScottCraner here: - It will prompt the user that there's a missing value on that cell and redirect to that cell but once the you clicked the message box, it will redirect to the SHEET2. It must not redirect to SHEET2 — PeterS, Oct 25 '16 at 13:36
You have a lot recorder code in there...utilize this information http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros?rq=1 — Rdster, Oct 25 '16 at 13:41

score 0 · Answer 1 · answered Oct 25 '16 at 13:37

0

Instead of this:

If errcnt > 0 Then
    MsgBox "There are " & errcnt & " fields missing", vbExclamation, "Missing Value"
    Cancel = True
Else
End If

try this:

If errcnt > 0 Then
    MsgBox "There are " & errcnt & " fields missing", vbExclamation, "Missing Value"
    Cancel = True
    Exit Sub
End If

Notice I changed the ELSE (that was useless there) by exit sub. This will trigger to end the macro in case there is something missing in SHEET1 and it will no carry on selecting SHEET2.

answered Oct 25 '16 at 13:37

Pedro F Pardo

56
4

I tried this one, but the document will be saved. I dont want the document to be saved when there's missing mandatory cell. – PeterS Oct 25 '16 at 13:40
@bobajob Im very new to this. Im currently using Workbook_BeforeSave function. But before saving I want to validate if all the madantory cells have been filled up. – PeterS Oct 25 '16 at 13:55

How to activate cell position when empty

1 Answers1