VBA Troubles

[Lowly VBA User]

I inherited a project from a ex-coworker and part of it involves Excel with a VBA code. I am not well versed in VBA...actually not versed at all and our IT department is useless so I figured I'd try a forum to see if anyone could help. I continue to get error messages from the code and no matter how much I read up on VBA, I can't seem to fix it. Please help!!

Option Explicit 
Dim bEdited As Boolean, wksMain As Worksheet, wksSh As Worksheet
Const sSaveMsg$ = "Saving this workbook will lock and prevent editing of cells where data was entered." & vbLf & "(Choose YES to save, NO to continue editing)" 
Const sWksMainName$ = "Sheet 1" 
Const sInputArea$ = "C6:G506"
Const sPWD$ = "Optrell2" 
Private Sub Workbook_Open() 
  Set wksMain = ThisWorkbook.Sheets(sWksMainName) 
End Sub 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
  Set wksMain = Nothing: Set wksSh = Nothing 
End Sub 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

  If Not bRangeEdited Then GoTo Xit 
  If Not Me.ReadOnly Then

With sInputArea

    If MsgBox(sSaveMsg, vbExclamation + vbYesNo) = vbNo Then 
      Cancel = True: Exit Sub 
    End If 
    'Lock edits on all sheets
    Dim v 
    For Each v In Me.Sheets 
      If Not v = wksMain Then 
        v.Unprotect "Optrell2" 
      v.Range(gsInputArea).SpecialCells(xlCellTypeConstants).Locked = True 
        v.Protect "Optrell2" 
      End If 'Not v = wksMain 
    Next 'v 
  End If 'Not Me.ReadOnly 
  bEdited = False 
End Sub 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
  bEdited = True: Set wksSh = Sh 
End Sub

 

[NARAYANK991]

Hi ,

Can you mention :

1. The error message itself

2. The line of code which is highlighted when the error message is displayed

Narayan

 

[Lowly VBA User]

The error message is as follows:

Compile Error:
Variable Not Defined

What highlights in yellow is as follows:

 PrivateSub Workbook_BeforeSave(ByVal SaveAsUI AsBoolean, Cancel AsBoolean)

What is also highlighted (or selected if you will) is the bRangeEdited in the following statement:

 IfNot bRangeEdited ThenGoTo Xit

 

[NARAYANK991]

Hi ,

The VBA variable which has been declared is bEdited , because of :

Dim bEdited As Boolean

Replace bRangeEdited by bEdited , so that the line of code should read :

If Not bEdited Then GoTo Xit

Narayan

 

[Lowly VBA User]

Narayan thank you so much for your prompt responses.

Now there's another error:

Compile error:
With object must be user-defined type, Object, or Variant

This is highlighted in yellow (again):

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

And the With in the following is selected:

 With sInputArea

 

[GraH - Guido]

I don't see an

end with

Seems you are doing nothing with the with-statement. Put it in comment and retry your macro.