Method failure as a symptom of a wrong reference?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A protected worksheet contains 4 command buttons. Each of these buttons activates a routine (in this case: 'NewExtern') in an add-in as follows:

Private Sub Commandbutton1_Click()
Application.Run "MyAddIn.xla!modUstEvents.NewExtern", ActiveSheet
End Sub

Each of these four routines are supposed to make changes to the worksheet, which must therefore be unprotected. The code of the four routines is very similar, and looks like this:

Public Sub NewExtern(ByVal ThisSheet As Worksheet)

'Declarations here

With ThisSheet
' Code ....
' Uprotect the sheet before applying changes
.Unprotect password:=strPasswd
' Code that makes changes to the sheet....
' Re-enable protection of the sheet
.Protect password:=strPasswd, UserInterfaceOnly:=True
End With ' ThisSheet

End Sub 'NewExtern

Now, the worksheet and the add-in were made and written in Excel 2000 Pro, and everything works fine. In Excel 97 all routines work as well, except the last one, where the error message that the unprotect method of the worksheet object failed appears. There are, however, no fundamental dfferences between the routines. Moreover, if I make changes to the first three routines by deleting all code except for the unprotect and protect methods, method failure suddenly occurs in these routines as well. So, it seems to me that the failure is merely a symptom of some more fundamental problem. Any suggestions?

TIA,

Arne
 
Change the takefocusonclick property of the commandbuttons to False. This
was a bug in xl97, fixed in later versions.

--
Regards,
Tom Ogilvy

Arne said:
A protected worksheet contains 4 command buttons. Each of these buttons
Click to expand...
activates a routine (in this case: 'NewExtern') in an add-in as follows:
Private Sub Commandbutton1_Click()
Application.Run "MyAddIn.xla!modUstEvents.NewExtern", ActiveSheet
End Sub

Each of these four routines are supposed to make changes to the worksheet,
Click to expand...
which must therefore be unprotected. The code of the four routines is very
similar, and looks like this:
Public Sub NewExtern(ByVal ThisSheet As Worksheet)

'Declarations here

With ThisSheet
' Code ....
' Uprotect the sheet before applying changes
.Unprotect password:=strPasswd
' Code that makes changes to the sheet....
' Re-enable protection of the sheet
.Protect password:=strPasswd, UserInterfaceOnly:=True
End With ' ThisSheet

End Sub 'NewExtern

Now, the worksheet and the add-in were made and written in Excel 2000 Pro,
Click to expand...
and everything works fine. In Excel 97 all routines work as well, except the
last one, where the error message that the unprotect method of the worksheet
object failed appears. There are, however, no fundamental dfferences between
the routines. Moreover, if I make changes to the first three routines by
deleting all code except for the unprotect and protect methods, method
failure suddenly occurs in these routines as well. So, it seems to me that
the failure is merely a symptom of some more fundamental problem. Any
suggestions?
 
Back
Top