Re: [ExcelVBA] Re: VBA EnableCalculation Clean Up

 

I think I'll ask the question you asked first:

"What I'm trying to accomplish, ... is to modify the code so that the worksheet name can be directly entered into the function formula instead of entering the worksheet's name into cell ..."

 

Your function:

Function CalcOff(Condition)

 

is passing a single argument: (Condition) to the function.

 

You can require multiple arguments.

 

Public Function CalcOff(shtName, Condition as Boolean)

 

That way, you can use:

=CalcOff("Sheet1",false)

 

This UDF needs to be in a "standard" module (not a sheet module).

 

--------------------------------

Now, to answer an unasked question:

 

The only time you NEED to turn off sheet calculations is when the sheet is "active".

You could create an event macro that would turn it off whenever it is selected:

 

Private Sub Worksheet_Activate()
    ActiveSheet.EnableCalculation = False
End Sub

 

This, of course WOULD need to be in the sheet module.

Or, if the sheet gets deleted and re-created from another source,

you could put it in the thisworkbook module as:

 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If (Sh.Name = "Sheet1") Then
        ActiveSheet.EnableCalculation = False
    End If
End Sub

 

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

From: "rajarjrtj@yahoo.com" <rajarjrtj@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Friday, April 25, 2014 2:58 PM
Subject: [ExcelVBA] Re: VBA EnableCalculation Clean Up

 

I'm at an impasse having tried every machination of code that I could think of to make the UDF work, even after incorporating Tim's mods. 

 

I would greatly appreciate any guidance in debugging the below code.

 

<<<<

Function CalcOff(Condition, Optional WorksheetNameParameter As String)
If WorksheetNameParameter = "" Then
WorksheetNameParameter = ActiveSheet.Name
Else
'WorksheetNameParameter was valued, so check that it is a valid name
Dim ws As Worksheets
Dim WorksheetFound As Boolean: WorksheetFound = False
 For Each ws In Worksheets
If Worksheets(WorksheetNameParameter) = ws.Name Then
WorksheetFound = True
Exit For
End If
Next ws
If WorksheetFound = False Then
MsgBox "The worksheet name is not valid." & vbCrLf & _
"Enter a valid worksheet name or remove the parameter to use the current worksheet."
End If
End If

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (9)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___