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

 

[GTO]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

When using application supplied event procedures, you must include the stock args (if any).

You do not actually need to type in an event's name. Look at the top of the code editing window. See the two drop-downs? Change the left one to 'Workbook' and the right one will be populated with the available events procedures.

 

[VoG]

It should be

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 

[grahamiwa]

Do I have to put a Boolean choice in the next line as I dont really want them to have a choice except for maybe "Cancel" to take them back

 

[VoG]

No, you don't need to use the Cancel argument if you don't want to. But it must be in the procedure declaration.

 

[GTO]

No. You can run your code w/o referring to Cancel. See how there is no ByVal in front of it? This means that it is passed by reference, so it can be used by the app, if for instance, you chose to include a Cancel = True somewhere in the procedure.

Edit: Hi Peter

 

[ScottR]

No. You can run your code w/o referring to Cancel. See how there is no ByVal in front of it? This means that it is passed by reference, so it can be used by the app, if for instance, you chose to include a Cancel = True somewhere in the procedure.

Sort of along the right lines but you could include Cancel=True in the procedure for ByRef as well and it would work. The difference is how the variable is altered from the perspective of the referring sub or function.

Consider the following

Sub ShowRefVal()

Dim myCancel As Boolean

myCancel = False
Call vByVal(myCancel)
MsgBox myCancel

Call vByRef(myCancel)
MsgBox myCancel

End Sub


Function vByRef(ByRef Cancel As Boolean)
Cancel = True
MsgBox Cancel
End Function


Function vByVal(ByVal Cancel As Boolean)
Cancel = True
MsgBox Cancel
End Function

Running the ShowRefVal sub would return msgboxes with true,false, true and true as cancel would be set to true in both procedures but only outside the functions when passed byref.

 

[grahamiwa]

Lost again guys I have only been back to trying VB for a couple of weeks

When I did the (Cancel As Boolean) with no extra code after it I still get an Compile Error message :- Variable Not Defined.

I had tried to put some choice code basically copied from another spreadsheet one of the other guys made that works well but it is still not good

This is the code I tried:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Choice = MsgBox("Do you want to close this form?", vbYesNo)
If Choice = vbNo Then
Exit Sub

Else

ActiveSheet.Unprotect Password:="SHES" ... and the rest of the code

I still get Variable Not Defined error before the message box appears

 

[GTO]

Please show us the entire code.

Edit: ACK! I read too quickly. Where is 'Choice' declared?

 

[james_lankford]

try

If MsgBox("Do you want to close this form?", vbYesNo) = vbNo then 
 Cancel = true
else
 Cancel = false
end if