Code Only works when sheet is in focus

A

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
 
T

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.
 
B

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
 
A

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
 
A

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
 
B

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top