Function Sub Procedure If Not Active Worksheet
 

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").Rang e("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").Rang e("YHolidays"))
Busdays = Busdays - 1
End If
End Function

Function Sub Procedure If Not Active Worksheet
 

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


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

"George" wrote in message
...
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").Rang e("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").Rang e("YHolidays"))
Busdays = Busdays - 1
End If
End Function

Function Sub Procedure If Not Active Worksheet
 

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

"Chip Pearson" wrote:

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


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

"George" wrote in message
...
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").Rang e("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").Rang e("YHolidays"))
Busdays = Busdays - 1
End If
End Function