Replies

Password Protect looks good but is not free. In that respect True Crypt makes better sense.

I’ve been using True Crypt for several weeks now without problems, although it is quite difficult to understand.

David

I am once again filled with admiration. Exactly what I was looking for. It will take me a little while to get it into my existing spreadsheet but the sample proves it works.

I’d love to know what you do for a living. Man, you’re always there within minutes with awesome responses.

Regards
David

Thanks for the prompt response.

Let us say that cell B1 is blank and the user enters a figure of

Thanks guys. I think the answer is stick with Outlook for anything other than very simple emails..

I had hoped to get away with using Outlook for work and Thunderbird for the 2 associations I maintain databases for (using 2 Thunderbird profiles). It looks like the only way forward is to create 3 Windows/Exchange profiles with Outlook in each. Messy having to log out and login to change.

David

Is exactly what the ‘doctor’ ordered. Thanks Alan.
David

Thanks Hans. Exactly what I wanted.
David

Brilliant, thanks Rory and Hans

David

Rory

This now works fine if I add or delete figures within the range. However, changing an amount from black to red/strikethrough does not force a recalculation, and neither does F9 (I’m set to recalc automatically). The only way seems to be to select the cell with the formula and go into it as if to edit it; exiting forces a recalc. This is impractical.

Any thoughts?

David

Thanks for the response.

=Colorfunction(S39:Z39,-1) typed into a cell gives a #NAME? error. The error message in Excel2003 says “Invalid name error” and the function does not appear in the macros list. I’ve put this function in the worksheet’s VBA module so it should show. Any ideas what I’m doing wrong?

David

How, if possible, would I change this function to sum cells where the content have a text colour? Or more to the point, not a text colour? We have a spreadsheet where amount unpaid are black and when paid they are changed to red text with strikethrough. I want to sum the black, i.e. unpaid, amounts.

David

I might add, for the benefit of anyone else who reads this thread, that i’ve found that the reference 1:1 to mean the whole of the first row can also be made an absolute reference $1:$1 and or OtherSheet!$1:$1. Having the reference as absolute seems to be more useful than relative in the context I was using it.

David

Thanks again Hans

Thanks Hans. Very impressive formula, well explained – I’ve never seen one so complicated, but it worked a treat.

For my information, what is the significance of the 1:1 part of the Match function? I found that I could achieve the same thing by substituting an actual range so I assume it is some sort of shortcut for the top row. I guess I will need to use an actual range because the formula will be used in a different sheet, e.g. OtherSheet!A1:ZZ1.

David

Sorry, should have looked back before posting

David

Hello Pat

Sorry, On Load also doesn’t work. I have experimented with some code I found a long time ago on the MS website. It uses a timer to create a delay between opening the form and running the queries. I’ve found that the delay can be the minimum 1 millisecond (I think thats the unit) its sufficient just to have a delay.

This surely can’t be the answer, but heck it works.

Thanks again to you and Hans.

David