VB Compile error

[grahamiwa]

Not having many skills with VB I am now stuck again

I have a VB macro that now works perfectly thanks to the help from this forum.

I have tried to make it a BeforeClose procedure so users dont have to call the macro or having to put a Control button on the sheet

In Visual Basic Editor I opened the VBA Projects and clicked on ThisWorkbook tab. In the code window I typed the first line as :-

Private Sub Workbook_BeforeClose()

Then copied and pasted everything from the macro under that

When I go to close the workbook and have the procedure run I get a Compile Error message box.

Compile Error:
Procedure Declaration does not match description of event or procedure having the same name

Now I an lost again HELP PLEASE

 

[ScottR]

You haven't declared the variable "Choice" and (I assume) have "Option Explicit" in your code module

Add

dim Choice as Integer

as the first line of the sub

 

[grahamiwa]

The whole code is :-

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If MsgBox("Do you want to close this form?", vbYesNo) = vbNo Then
Cancel = True
Else
Cancel = False
End If

ActiveSheet.Unprotect Password:="SHES"

Dim c As Range
For Each c In Range("C10:J110")
c.Locked = c.Value <> ""
Next c

ActiveSheet.Protect Password:="SHES", DrawingObjects:=True, Contents:=True, Scenarios:=True

ActiveWindow.ScrollWorkbookTabs Position:=x1First
Sheets("READ ME").Select

ActiveWorkbook.Save

strFileName = "\\mlbdat02\shared\organization\T&D-All\ePTW Course Scheduling\ePTW Schedule Master\Archive\Schedule Master Test" & Chr(32) & Format(Date, "d mmm yyyy") & ".xls"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:= _
xlNormal, Password:="SHES", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = True

ActiveWorkbook.Close

It is still not working and when I click the OK on the error message box the first line - Private Sub Workbook_BeforeClose(Cancel As Boolean), is highlighted in yellow additionally on the ActiveWindow.ScrollWorkbookTabs line the statment after the = sign looks like it has been selected as it is Dark blue background like when you select text to copy it

 

[VoG]

Try

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

 

[grahamiwa]

The ActiveWindow.ScrollWorkbookTabs Line was copied out of a macro I ran to see the code and works ok

I had been using another Excel workbook which had a Workbook_BeforeClose procedure as a model of what I should be doing.
In VB Editor window before the Private Sub Workbook_BeforeClose line it had a general declaration "Option Explicit"

I have just deleted this declaration and surprise the code works ok now. I do not have to add a message box with choices or anything

What does that Option Explicit declaration do?

 

[VoG]

Option Explicit requires you to declare all variables, which is a good thing.

In the line that was erroring you had 1 instead of l where I indicated.

 

[james_lankford]

yes, please put Option Explicit back in

as VoGg said, its telling you that you're using a variable that you haven't dimmed yet

in your case you were using
x1First - x one First

but you should have been using
xlFirst - x L First

without Option Explicit you will still be using x one First (a variable that dos not exist) and you will never know there's a problem until Excel tries to set the position for ActiveWindow.ScrollWorkbookTabs and then it will fail
it would be the same as
ActiveWindow.ScrollWorkbookTabs Position:=abcdefg
it just wouldn't make any sense to excel

so having option explicit is a really a must have
its screaming at you and telling you, "HEY! There's a variable here that I've never seen before! You might want to check the spelling on that!"