+ Reply to Thread
Results 1 to 5 of 5

Calling another sub is giving error

Hybrid View

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Boston
    MS-Off Ver
    Excel 2003
    Posts
    4

    Calling another sub is giving error

    I am trying to call this procedure from another
    Module1
    Private Sub unProtectSheets()
    On Error Resume Next
    For Each Page In ActiveWorkbook.Worksheets
        Page.Unprotect Password:="accounting"
    Next
    End Sub
    And here is how I tried to call and the resulting error:
    Call module1.unProtectSheets
    or
    Call unProtectSheets
    gives
    Compile Error:
    Method or Data member not found
    and
    unProtectSheets
    gives
    Compile Error:
    Sub or Function not Defined
    I can't figure it out.

    Please and Thank You

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,006

    Re: Calling another sub is giving error

    Change:
    Private Sub unProtectSheets()
    to:
    Sub unProtectSheets()
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Boston
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Calling another sub is giving error

    Quote Originally Posted by romperstomper View Post
    Change:
    Private Sub unProtectSheets()
    to:
    Sub unProtectSheets()
    That does work and prevents the error. But now that procedure now appears in the list of macros that a user can run. And that is what I am tryng to prevent.

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Calling another sub is giving error

    You could add a password check to the macro. That way, when the user calls the macro via the toolbar, they need to input a password for the macro to run.

        resp = InputBox("Enter Password")
        Select Case resp
        Case "accounting":
             On Error Resume Next
             For Each Page In ActiveWorkbook.Worksheets
                  Page.Unprotect Password:="accounting"
             Next
        Case Else: MsgBox "Invalid Password"
        End Select

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,006

    Re: Calling another sub is giving error

    Add:
    Option Private Module
    to the top of the module.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1