Using Sheets.Activate method within a VBA function doesn't work. Why?

Pixley, Ray 1 Reputation point

The function Sheets.activate doesn't work within a VBA function call. What am I doing wrong?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,294 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Pixley, Ray 1 Reputation point

    I want to be able to create a help sheet using a function call, but Excel vba ignores the sheets.add and sheets.delete methods. Using as stand ins the .activate and .select methods also fails, yet they work ok for VBA subs. Here is my codes for both methods:

    Public Function TestBuildHelpSheet(eInput As String) As Double
    Dim ws As Variant
    For Each ws In Sheets
    If ws.Name = "Help Sheet" Then ws.Activate ' doesn't work
    End Function

    Sub RemoveHelpSheet()
    ' This works fine, changing ws.activate to ws.delete also works;
    ' It deletes the help sheet. Also tried ws.Select, same result
    Dim ws As Variant
    For Each ws In Sheets
    If ws.Name = "Help Sheet" Then ws.Activate ' works
    Next ws
    End Sub

    The eInput is just something for the function to receive to get going, and the stops are to allow the switchover to F8 stepping.

    Why does this work with Subs, and not with functions? I also tried having the function call the sub, but that didn't work either.

    0 comments No comments

  2. Michael Taylor 44,136 Reputation points

    The function has to be called by somebody. My guess is that whoever is calling the function isn't right. Please post the code where you are actually calling the function that tries to activate the help sheet.

    0 comments No comments

  3. John Korchok 4,681 Reputation points

    I'm not clear why you would want to implement this as a Function. Functions usually process some data and return a value. That's not the case with your example. Just use a Sub instead.

    0 comments No comments

  4. Pixley, Ray 1 Reputation point

    Thank you for answering. What I gave as a function is stripped down coding of a function that looks something like this inside the function:

    TestBuildHelpSheet = 0 (Default return)
    Select Case eInput
    Case "Apple"
    TestBuildHelpSheet = 1
    Case "Pear"
    TestBuildHelpSheet = 2
    Case Else
    If vbYes = MsgBox("May I replace the " & HelpSheetName & "?", vbYesNo) Then
    ' goes on to insert an updated help sheet since the eInput string was invalid and
    ' the user won't have any idea what choices there may be unless I give them a table
    ' they can reference.
    end select

    I left out the message box since that is not causing the problem. I can't use a sub, a user won't have access when using the function in their spreadsheet. Obviously I do have to make sure duplicate help sheets don't occur and not wipe out any notes an end user may have put on previous editions of a help sheet, which is why there has to be some message boxes in the coding. Additionally, the help sheet can stay with the excel file and be updated by the user by either forcing an error or entering the string "help" when using the function (I don't show that code above).

    My question is, why doesn't it select/activate the sheet so I can provide decent help to an end user?

  5. John Korchok 4,681 Reputation points

    There aren't enough years left in the universe to answer all the why questions about Microsoft Office. I would focus on solving the problem. Create a sub that activates the worksheet, call the sub from the function.

    0 comments No comments