Function Sub Procedure If Not Active Worksheet

[Guest]

I am trying to create an add-in with the following VBA code stored in it. It
references 2 ranges that have a list of dates for the networkdays function.
The macro doesn't work if it is not the active worksheet.

Public Function Busdays()
Dim Break As Date
Dim Recon As Date
Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("Is this a xxx?", vbQuestion + vbYesNo, "Select Yes
for xxx, No for yyy")
If strAnswer = vbYes Then
Break = InputBox("Enter the date of the break")
Recon = InputBox("Enter the date of the recon")
Busdays = networkdays(Break, Recon,
Workbooks("busdays.xls").Worksheets("Sheet1").Range("XHolidays"))
Busdays = Busdays - 1
Else
Break = InputBox("Enter the date of the exception")
Recon = InputBox("Enter the date of the recon")
Busdays = networkdays(Break, Recon,
Workbooks("busdays.xls").Worksheets("Sheet1").Range("YHolidays"))
Busdays = Busdays - 1
End If
End Function

 

[Chip Pearson]

What do you mean by "doesn't work"?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

It returns a #NAME error when I type =busdays() in a cell of a different
worksheet, although I have the one with the macro open, but not active.

George