Give an error message when macro is used but cells are blank.

[Inuniform]

Hi excel guru's

I'm wondering wether there is a possibility to give an error message when a button is used and certain cells are left blank. I need this feature for the following two buttons:

Sub Implement_Task()
'
' Implement_Task Macro
'
'
    Sheets("Last task").Select
    Range("A2:N2").Select
    Selection.Copy
    Sheets("Action Register").Select Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    
    Range("A" & Range("B11").Value & "").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
          Sheets("Last task").Select
    Range("L2").Select
    Selection.Copy
    Sheets("Action Register").Select Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    
    Range("L" & Range("B10").Value & "").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Assign task").Select
        
           
End Sub

If any of the cells B4 B6 B8 B10 B13 B15 B17 B19 B21 don't have any content I need it to give an error message and don't run the macro yet untill the cells are filled with content.

Same with the following macro:

Sub CmdBtnComleted()
Dim fn As Range
With ActiveSheet
 Set fn = .Range("A2", .Cells(Rows.Count, 1).End(xlUp)).Find(.Range("K4").Value, , xlValues)
  If Not fn Is Nothing Then
   fn.Offset(, 3) = "Complete"
  End If
End With
End Sub

For this code it is a little bit more tricky, In K4 an ID is entered which coresponds with a row in which a task has been entered. The macro finds that row by means of the ID and changes the cell in D to "Complete". After this via automatic VBA coding in the sheet the row will be moved to another sheet. If a cell in column M or P is not filled I need it to give an error same as with the previous macro. If a cell column N is not filled I want it to give something like "You haven't filled in any downtime, do you wish to continue?" whereafter they can choose to go further or not. The code for the automatic VBA in the sheet is as follows in case you need it:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.CountLarge > 1 Then Exit Sub
 Dim Rstart As Range, Rend As Range, Rdest As Range
 Dim destinationLastRow As Long, sSize As Long
     Set Rstart = Range("A" & Target.Row)
     Set Rend = Range("P" & Target.Row)
    
     If Target.Column = 4 Then
        If Target.Value = "Complete" Then
            With Sheet6 'Historic Register
                destinationLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            End With
            With Range(Rstart, Rend.Offset(, -1))
                sSize = .Count
                .Copy
            End With
            Set Rdest = Sheets("Historic Register").Range("A" & destinationLastRow).Resize(1, sSize)
            Rdest.PasteSpecial xlPasteValues
            Target.EntireRow.Delete Shift:=xlShiftUp
            'Sheet6.Range("B" & Target.Row & ":J" & Target.Row & "").ClearContents
            'Sheet6.Range("B" & Target.Row & ":J" & Target.Row & "").ClearContents
            Application.CutCopyMode = False
            'Rstart.Offset(1).Select
        End If
     End If
End Sub

Hope I explained it well enough, thanks in advance!

 

[dmt32]

Hi,
one way would be to create a common Function you call from each procedure to validate data entry.

In a standard module

Function IsComplete(ByVal Target As Range) As Boolean
    Dim Cell As Range
    For Each Cell In Target
        IsComplete = Len(Cell.Value) > 0
        If Not IsComplete Then MsgBox "Entry Required.", 16, "Entry Required": Cell.Select: Exit Function
    Next Cell
End Function

to use in your code:

Sub Implement_Task()
'
' Implement_Task Macro
'
'
    Dim RequiredCells As Range
    Set RequiredCells = Sheets("Last task").Range("B4, B6, B8, B10, B13, B15, B17, B19, B21")
    
    If Not IsComplete(RequiredCells) Then Exit Sub
    
    'rest of your code
        
           
End Sub

Hope Helpful

Dave

 

[Inuniform]

Most helpfull! Thank you for your quick reply! There is a small problem though. The code works because when I run it with one of those cells blank it shows the message box, but when I then click oke it gives a "run-time error '1004' Select method of range class failed". When I click debug the part ": Cell.Select" is marked yellow.

Inuniform

 

[dmt32]

I assumed that the sheet with required cells is the ActiveSheet which clearly, it is not.

Try this update:

Function IsComplete(ByVal Target As Range) As Boolean
    Dim Cell As Range
    For Each Cell In Target
        IsComplete = Len(Cell.Value) > 0
        If Not IsComplete Then
        MsgBox "Entry Required", 16, "Entry Required"
        If ActiveSheet.Name = Target.Parent.Name Then Cell.Select
        Exit Function
        End If
    Next Cell
End Function

Dave

 

[Inuniform]

The sheet with required cells is the active sheet however by the automated sheet coding (if you call it that) another sheet briefly is the active sheet. That might be the issue. The new code gives an error message but it also gives an error message when all required cells have content. I have checked the cell values in the code and they all seem to be accurate. I might have to add that the cells don't all contain numeric values but also text, which would mean that something has to change here

 IsComplete = Len(Cell.Value) > 0

.

Inuniform

 

[Inuniform]

I found the problem! In this code

Sub Implement_Task()
'
' Implement_Task Macro
'
'
    Dim RequiredCells As Range
    Set RequiredCells = Sheets("Assign task").Range("B4, B6, B8, B10, B13, B15, B17, B19, B21")
    
    If Not IsComplete(RequiredCells) Then Exit Sub

It said the sheet name was Last task but the name is Assign task. I changed it and now it workd perfectly. Cheers mate and happy holidays!!

 

[dmt32]

I found the problem! In this code

Sub Implement_Task()
'
' Implement_Task Macro
'
'
    Dim RequiredCells As Range
    Set RequiredCells = Sheets("Assign task").Range("B4, B6, B8, B10, B13, B15, B17, B19, B21")
    
    If Not IsComplete(RequiredCells) Then Exit Sub

It said the sheet name was Last task but the name is Assign task. I changed it and now it workd perfectly. Cheers mate and happy holidays!!

Glad you resolved yourself & solution works ok for you.

Merry Xmas

Dave