This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 95%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPR%3C/text%3E%3C/svg%3E)
The function Sheets.activate doesn't work within a VBA function call. What am I doing wrong?
Ok. Tried the call-a-sub approach approach already with no success before posting the question.
However, here's some code showing what I tried.
But first create an excel file with at least two sheets, name one "Help Sheet" and the others something else.
Put in any cell in one of the other spreadsheets the formula: =ActivateThis("Something")
and let it calculate, preferably stepping it using F8.
See if it delete the "Help Sheet" when the sub finds it. It will find the sheet, but won't delete it.
The eventual plan is to include it as part of an add-in, a button won't do.
Function ActivateThis(eInput As Variant) As Variant
ActivateThis = "Whatever" ' only because the function apparently needs to return something
Call ActivateHelpSheet
End Function
Sub ActivateHelpSheet()
Dim ws As Variant
For Each ws In Sheets
If ws.Name = "Help Sheet" Then ws.Delete
Next ws
End Sub
Why won't it delete? Or if .activate is use, why won't it activate? Ditto with .select? Another downstream issue is it not allowing cells to be filled with data, not allowing sheets to be added, even when called via subs, but those questions are beyond the scope of this question.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
Please respond to answers using the Comment link instead of adding your own answer so we can better track who you're responding to.
That isn't going to work. If you put a formula into a cell value then the formula is going to get called when the cell renders and potentially anytime after that as data changes or the worksheet is invalidated. You cannot be mucking with data while trying to render the data.
This really has nothing to do with function vs sub but rather how you're calling the routine. The purpose of a formula is to return a value that can be rendered by a cell. This article lists the limitations of functions in formulas. Basically it cannot change anything outside of returning a value.
I recommend you create a macro that does what you want and then put it on the toolbar for testing purposes.
I see your point about the function returning a solution. The problem come up when a user supplies data that the function can't accept, the code should be able to provide immediate guidance on what's needed other than saying something invalid was provided. Dropdown lists and other tools don't always fulfill that need, sometimes a more complex help method is needed. The Msgbox function simply doesn't allow a long enough string to be displayed when I made some trial runs.
As for your recommendation for creating a macro, that's not feasible, I already made some attempts with end users before posting this issue, and the question always come up where do I get help using this function when it doesn't like me. Please stop wasting time suggesting it.
To add to the above, I also tried
If ws.Name = "Help Sheet" Then ws.Activate
If ws.Name = "Help Sheet" Then ws.Delete
to see if the sheet had to be activated before deleted. No joy there wither.