-1

I have written this VBA code in excel, and while saving it in this workbook, I am getting sub or function not defined error and the offset line is highlighted. I am entirely new to VBA and I don't know to debug this, please help me out with this.

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

'######################################################################################
'Employee Name Validation Started
'######################################################################################
Activate
Range("B2").Select
exceptionCount = 0
Do Until ActiveCell.Address = "$B$10"
' Check if the Employee Name is Not Null
If IsEmpty(ActiveCell.Value) Then
totalExceptions = 1
exceptionCount = 1
Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
Interior.ColorIndex = 15 'Retain Grey Color
End If
Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
exceptionString = exceptionString + vbCrLf & "- Employee Name Cannot be Empty"
End If
'#######'Employee Name Validation Completed '##############################################


End Sub
FunThomas
  • 23,043
  • 3
  • 18
  • 34
Ragu
  • 21
  • 2
  • Your first issue is `Offset(1, 0).Select`. 'Offset' is a `Range.Property`. You have ***not*** assigned it to any range. – JvdV Jul 23 '20 at 08:09
  • probably `Activate` is not defined and causing your issue. Anyhow, I strongly encourage you to read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and rework your code. And use `Option Explicit` so that the compiler can inform you about such error. – FunThomas Jul 23 '20 at 08:09
  • Sure will check it out and try to improve my code. Thanks. – Ragu Jul 23 '20 at 08:20

1 Answers1

1

You forgot to mention where you have the error, apparently it was in this line:

Offset(1, 0).Select

This makes sense, because Offset() is not some function, it's a property of a range.

Most probably you want to use the offset of the cell you have selected, which you can write as:

Selection.Offset(1, 0).Select

Good luck

Dominique
  • 16,450
  • 15
  • 56
  • 112