Posts by steveski

I have a spreadsheet where I keep track of a list of projects as well as related financial info. The spreadsheet is password protected. I’d like to make portions of the spreadsheet visible/accessible to employees, without divulging the financial info. Can you give me any general guidance on how to do this?

Thank you.

Re: #VALUE! returned for logical function

Thank you. This thread can be closed now.

I'm using the following function:

=SUM(IF((C$2:C$148="E")*(ISTEXT(D$2:D$148)*(A2:A148=1)),L$2:L$148))

and it is returning #VALUE!

I know there is a toggle I need to execute (function key? ctrl-something?) in order to make such functions work, but I forget what to do.

Help?

Thanks.

I figured it out.

The denominator inside the parentheses of the basis of the MIRR formula (see MIRR in Excel help) is:

NPV(frate,values(negative))*(1+frate)

If there is only one negative number (initial cost) in the first year (as in the Excel help example), then the above quantity is always equal to the initial cost, regardless of how frate is varied.

The only time varying frate affects MIRR is when there is also a negative number in one of the subsequent years.

This became embarrassingly obvious when I inspected the basic NPV formula:

NPV = Sum[i=1 to n] (values i)/((1+rate)^i)
If there is only one value, then when the above is multiplied by (1+frate), (1+frate) drops out and the result is 'value'.

The formula for MIRR is MIRR(values,finance_rate,reinvest_rate)

If I vary reinvest_rate, then MIRR changes.

However, if I vary finance_rate, MIRR does not change.

Does this sound right?

(I get the same result in Excel 2000 and Excel 2002.)

Thanks.

Brandtrock, thanks for your suggestion. I haven't followed up on it yet but I will.

In the meantime, I checked my friend’s PC (using Excel 2000).

1) Tools>References>Microsoft Excel 9.0 Object Library was checked.

2) The calendar control was not actually grayed-out, but the DayFont Property of the Calendar1 was set at 2pt font, so the numbers on the days were indiscernible. When I tried to change the font size I got the following error “Object doesn’t support this property or method”.

Do you know what could cause this?

Thanks.

Thanks for your advice, I will check that when I get on his computer tomorrow, and I'll let you know what I discover.

I’m using the Calendar Control in a UserForm with Excel 2000. Here is the code:
---------------------------------------------
Private Sub Calendar1_Click()
Me.Hide
End Sub
---------------------------------------------
On my computer everything works fine.

However, I e-mailed my application to someone else (also using Excel 2000) and he told me that when he ran the application the Calendar Control was grayed-out. (He could see the month, but he could not see the numbers of the individual days).

Does anyone have an idea as to what would cause this problem? The person I sent the application to is 40 miles away from me, however, I am going there tomorrow morning, so any help you can give me before then will be much appreciated.

Thank you.

You saved me. Thanks!

Correction: I can invoke the UserForm with UserForm1.Show (not Show.UserForm1)

I am trying to get a Module to launch a UserForm (for the user to give input) then return the value from the UserForm to the Module so I can use the value in my code.

Here is the UserForm (UserForm1):

Private Sub Calendar1_Click()


End Sub

---------------------------------------------------------------------------

From the Module, I know I can invoke the UserForm with Show.UserForm1

And I know the value returned from this UserForm is Calendar1.Value, but I can’t figure out how to get this value to pass back to the Module.

Help?

I solved this by changing the location of the PivotChart and placing it in a Sheet. Then it was simple to write VBA code to format and display the Start Date and End Date.

I figured out how to do this by having VBA populate a seperate sheet which then feeds the PivotChart.

My VBA code asks the user for a date range (Start Date and End Date). Is there a way to have the date range automatically displayed on the PivotChart?

Thanks.

I have a PivotTable which I would like filtered for a date range. I want the date range to be user-defined on a case-by-case basis (via InputBox or MsgBox).

Is this possible?

I created a clunky one (actually two):

BSdate = InputBox("Enter Start Date as mm/dd/yy", "mm/dd/yy")
BEdate = InputBox("Enter End Date as mm/dd/yy", "mm/dd/yy")

It works, but if anyone knows of any existing code that creates a nicer user interface (with a drop-down calendar maybe?) I would appreciate the information.

Thank you.

Thank you very much. It worked fine.

Does anyone know of some already-written code to create an InputBox for the user to enter a start date and an end date?

I'll keep searching the site.

Thanks.

Perfect, thank you!

Can you please tell me if anything is wrong with the following? I get "Sub or Function not defined" at Text(BEdate, "mm/dd/yy")

Dim BEdate As Date
BEdate = "2/1/04"
Sheets("Any Date Range").Range("B1") = Text(BEdate, "mm/dd/yy")

Thanks.

I have a stacked bar chart and I would like to display the total above each stacked bar. How can I do this?

Thanks.