Hi All,
I needed a way to turn-off calculation on a single worksheet within a large workbook which is connected to an API, and cobbled together the below VBA code, which works, but is ineloquent and also references directly a cell, which I know is bad practice for a UDF. I know just enough about VBA to be dangerous.
What I'm trying to accomplish, but haven't had success at yet, 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 $Z$38, for example =CalcOff("Sheet1"), and also have "CalcOff & (the affected worksheet's name)" for example "CalcOff Sheet1", as the non-error output in the cell that has the function formula input?
Any guidance would be greatly appreciated.
Thank You
<
Function CalcOff(Condition)
Worksheet = Range("$Z$38").Value
On Error GoTo ErrHandler
If Evaluate(Condition) Then
Worksheets(Worksheet).EnableCalculation = False
CalcOff = "CalcOff"
Exit Function
End If
ErrHandler:
Worksheets(Worksheet).EnableCalculation = True
CalcOff = "CalcOn"
End Function
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar