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:
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:
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:
Hope I explained it well enough, thanks in advance!
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:
Code:
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:
Code:
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:
Code:
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!
Last edited: