VBA Troubles

[LowlyVBAUser]

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!! The code is below

 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$ = "Almo"
Const sInputArea$ = "C6:G506"
Const sPWD$ = "mdcd"
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 "mdcd"
      v.Range(gsInputArea).SpecialCells(xlCellTypeConstants).Locked = True
        v.Protect "mdcd"
      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

 

[cerfani]

What does the error message say?

What line of your code does the error pop up on?

Describe the process you take that creates the error. Start at step 1 and be thorough. Then possibly you can get some help.

 

[cerfani]

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$ = "Almo"
Const sInputArea$ = "C6:G506"
Const sPWD$ = "mdcd"

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 [COLOR=#ff0000]GoTo Xit [/COLOR][COLOR=#008000]' "Then Exit Sub" is that what you mean??[/COLOR][COLOR=#ff0000][/COLOR]
    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 "mdcd"
                    v.Range(gsInputArea).SpecialCells(xlCellTypeConstants).Locked = True
                    v.Protect "mdcd"
                End If 'Not v = wksMain
            Next 'v
        [COLOR=#ff0000]End With[/COLOR]
    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

i made your code readable and found an error, you are missing an end with

I recommend...

-use spacing between functions and blocks of codes like declaring variables, loops, if... make it organized
-whenever you nest code inside a block of code like a Sub, a Loop, an If statement, etc... indent 4 spaces

 

[LowlyVBAUser]

I did as you suggested and now I'm getting a Compile Error of "End With without With" where the End With was added. I'm not familiar with End With so any help would be huge.

 

[cerfani]

Well you can see for yourself... do Ctrl+F on this page and search for "End With"

you will not find it in your code. But you will find...

With sInputArea

this is the documentation for the With keyword...

https://docs.microsoft.com/en-us/do...-reference/statements/with-end-with-statement

It is used to make writing code easier and more readable. It is like a tool to help the coder as opposed to something that makes the program function.

Anyways look at my formatted code... if you align the margin of the With you can see i am closing it now with End with... show me your new code that has the error

EDIT:

And now that im looking at your code im noticing sInputArea is just a string constant. You use With statements for class objects to access properties easier and maybe to not have to use a function to get the object everytime you need to edit a property or call a function from the object. Anyways, String yes is a class object with members but it is a special class that is meant to be used like a value type... like an Integer.

Long story short just get rid of the... With sInputArea ... it serves no purpose in your code

and when you do try to use that string constant you reference it incorrectly... you wrote gsInputArea... v.Range(gsInputArea).SpecialCells(xlCellTypeConstants).Locked = True

gsInputArea is undefinded and = to Nothing

 

[Fluff]

There were multiple problems with your code, which I have tried to fix, but as you have given us no information it is a lot of guess work.
try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If Not bEdited Then Exit Sub
   If Not Me.ReadOnly Then
      With Range(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 "mdcd"
               v.Range(sInputArea).SpecialCells(xlCellTypeConstants).Locked = True
               v.Protect "mdcd"
            End If 'Not v = wksMain
         Next 'v
      End With
   End If 'Not Me.ReadOnly
   bEdited = False
End Sub

 

[LowlyVBAUser]

I managed to fix the InputArea error prior to seeing any of the replies. After reading a bunch of forums online, I managed to get most of the fixes that you recommended on my own but even with mine and all of your recommended fixes, I'm still having problems. I don't want to be a pain and continue talking in circles and into black holes so I will do a deep dive into a VBA book I found and hopefully I'll get a better understanding of what I'm doing so that I can ask the right questions should my issues persist.

Thanks for your help. I truly appreciate it.

 

[DDRA Steampunk]

deep dive into a VBA book I found and hopefully I'll get a better understanding of what I'm doing so that I can ask the right questions should my issues persist.

I suggest the Mr. Excel Library book *VBA and Macros* by Bill Jelen and Tracy Syrstad. I'm finding it very helpful (a lot more helpful than my class I'm taking!). I mostly haunt the forums for practical application, but the book is great for learning terminology and getting a better understanding of how the codes actually function.

 

[Fluff]

And it's available here if anyone's interested https://www.mrexcel.com/store/