Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF only works when focus is on a specific sheet - Help!!
Tom - Thanks but It is already in the general module.
"Tom Ogilvy" wrote in message ... Is the code in a general module as opposed to the worksheet module. If not, try putting it in a general module. -- Regards, Tom Ogilvy "Andibevan" wrote in message ... Hi All, I have been struggling with the following UDF that I keep nearly get working but then it gives up. It is a stand-alone UDF that needs no variables as it determines the date it should work on from the 2nd row of the column it is called from, and the name to look up from column A of the row it is called from. The problem is that it works fine when calculate is hit (F9) from one worksheet but it only generates #REF! when called (or forced to calculate using F9) from another. I am completely stumped:- Function HolAvail() Application.Volatile On Error GoTo Err: Dim Var_Name As Range 'Location of Name Dim Var_Date As Range 'Location of Date Dim Var_Name_Row As String 'Name row Dim Var_Date_Column As String 'Date Column Dim Var_SheetZZ As String ' as worksheets? Dim STR_RNG_Date As String Dim STR_RNG_Name As String Var_SheetZZ = Application.Caller.Parent.Name Var_Date_Column = Application.Caller.Column 'Column where date is 'Converts Column number into Column Letter If Var_Date_Column 26 Then Var_Date_Column = Chr(Int((Var_Date_Column - 1) / 26) + 64) & _ Chr(((Var_Date_Column - 1) Mod 26) + 65) Else ' Columns A-Z Var_Date_Column = Chr(Var_Date_Column + 64) End If Var_Name_Row = Application.Caller.Row 'Row where name is Set Var_Date = Range(Var_Date_Column & "2") Set Var_Name = Range("A" & Var_Name_Row) STR_RNG_Name = "'" & Var_SheetZZ & "'!" & Var_Name.Address STR_RNG_Date = "'" & Var_SheetZZ & "'!" & Var_Date.Address HolAvail = Evaluate("SUMPRODUCT((" & STR_RNG_Date & _ "=Hol_Start)*(" & STR_RNG_Date & _ "<=Hol_End)*(" & STR_RNG_Name & _ "=Hol_Name)*(Hol_Type_Code))") If (Evaluate("SUMPRODUCT((" & STR_RNG_Date & _ "=Hol_Start)*(" & STR_RNG_Date & _ "<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))")) = 2 Then HolAvail = 2 Else HolAvail = HolAvail End If 'Workdays are Blank HolAvail = IIf(HolAvail = 0, "", HolAvail) 'Weekends are W (Weekdays 7 & 1) HolAvail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _ "W", HolAvail) Err: 'HolAvail = "" HolAvail = "E" End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP!! Unhide Sheet with Macro and focus on other sheet | Excel Discussion (Misc queries) | |||
UDF only works when focus is on a specific sheet - Help!! | Excel Programming | |||
Set Focus to Worksheet and then a specific ROW | Excel Programming | |||
Code Only works when sheet is in focus | Excel Programming | |||
Statement to give focus to a specific control on a UserForm? | Excel Programming |