Code Only works when sheet is in focus

[Andibevan]

Hi All,

The following function works fine, but only on for the worksheet which the
user is currently looking at.

Can anyone provide some guidance so that this code might work on 3-4
different worksheets within 1 workbook?

Function HolAvail()

Application.Volatile



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



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



Set Var_Date = Range(Var_Date_Column & "2")



Var_Name_Row = Application.Caller.Row 'Row where name is



Set Var_Name = Range("A" & Var_Name_Row)



'Criteria to lookup against holiday / resource list

HolAvail = Evaluate("SUMPRODUCT((" & Var_Date.Address & _

">=Hol_Start)*(" & Var_Date.Address & _

"<=Hol_End)*(" & Var_Name.Address & _

"=Hol_Name)*(Hol_Type_Code))")



If (Evaluate("SUMPRODUCT((" & Var_Date.Address & _

">=Hol_Start)*(" & Var_Date.Address & _

"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))"))

= 2 Then



HolAvail = 2

Else

HolAvail = HolAvail

End If

'SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Name="Public

Holiday")*(Hol_Type_Code))

'If (Evaluate(SumProduct((I2 = Hol_Start) * (I2 <= Hol_End) * (Hol_Name =

"Public Holiday") * (Hol_Type_Code)))) Then



'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)



End Function



Ta



Andi

 

[Tom Ogilvy]

all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

 

[Andibevan]

Thanks Tom - I will re-write my code using your suggestion.

Ta

 

[Bernie Deitrick]

Andi,

Get rid of all this:

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
Set Var_Date = Range(Var_Date_Column & "2")
Var_Name_Row = Application.Caller.Row 'Row where name is
Set Var_Name = Range("A" & Var_Name_Row)


and use

Set Var_Date = Application.Caller.Parent.Cells(2, Application.Caller.Column)
Set Var_Name = Application.Caller.Parent.Cells(Application.Caller.Row, 1)

HTH,
Bernie
MS Excel MVP

 

[Andibevan]

Bernie,

Thanks for the help - I tried you suggestion and it still only works on the
sheet I that is active.

Any ideas?

Ta

Andi

 

[Andibevan]

Tom,

I changed Var_Name and Var_Date as suggested but it still only works on the
sheet in focus.

Do I need to change the part of HolAvail = Evaluate("SUMPRODUCT......?

Ta

Andi

 

[Bernie Deitrick]

Andi,

Try changing the SUMPRODUCT formula lines to:

HolAvail = Evaluate("SUMPRODUCT(('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
">=Hol_Start)*('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"<=Hol_End)*('" & Var_Name.Parent.Name & _
"'!" & Var_Name.Address & _
"=Hol_Name)*(Hol_Type_Code))")

If (Evaluate("SUMPRODUCT(('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
">=Hol_Start)*('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))")) = 2 Then

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Sure - a string is a string - there isn't any more information in the string
than you place there. If you string does not have the sheet locations
included, it will refer to the activesheet - same as it would in a cell.