Posts by dpenney

Re: Determine If Allow Edit In Cell Is On/Off Or True/False

[COLOR="Red"]DAVID HAWLEY![/COLOR]

You're taking advantage of my stupidity!

Seriously, though, I will re-read ALL the instructions to try to fit in with what you require from a customer.

So sorry that I got off on the wrong foot and am being such a difficult customer!

You wrote:
how you word your post is not really of concern to me, but should be [COLOR="Blue"]to yourself [/COLOR]if you want a decent reply. This tells me I should ask the question to myself, i.e. "How do I use Excel's option settings in a formula?" So far - so good. That's what I did. (More or less!)

Your next advice was, "Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will [be] wrong and prevent someone from finding a solution to a simliar issue." Thankfully, Andy latched onto my question quickly and supplied an answer VERY quickly. Thank you very much, Andy! ( a friend of mine, from southern England, is in Kandahar, Afghanistan now, but I digress!)

Finally, Dave, your response does not address the fact that Andy added to, and opened up, a whole new dimension to the original question. Please reflect Andy's input in the title of the post. Otherwise, future searchs will be directed to nowhere! (How many visitors need to know the present subject of your new title?)

Dave, so sorry for being such a sn__ on this item, but ........................

Dave (in eastern Canada)
CQM/OE (Certified Quality Manager/Organizational Excellence)[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Andy:

Further to the use of the User Defined Function, udfEditInCell(), that you suggested, the answer, TRUE or FALSE, will not update itself unless I press F2 to edit the cell and then press Enter. I checked the Calculation mode and it IS set to Automatic.

Why is the function not calculating by itself?

Regards, ..... Dave

Re: Determine If Allow Edit In Cell Is On/Off Or True/False

Hi Dave:

Sorry for the incorrect wording of the title. I consider myself warned and chastised!

I HAD taken a few minutes to read what you suggested, and thought, and then thought some more, about how to phrase my question. I then re-read the info and thought a little more. One of the suggestions given was not to be too specific or too narrow, or something like that. I felt that my final choice was so in-between and inadequate that I was about to abandon my question and not even post, but I gave it my best shot anyway.

As for getting my question answered fast, Andy responded within a minute or two. Now THAT's fastastic service!

As it turns out, I realize now that the user defined function that Andy provided can be used in a wide variety of situations to test for Excel's settings and how to test for them in a formula. Therefore, may I humbly suggest that my original title is a better title for others to search on, since the answer that Andy provided can be used so universally. Dang! He's good!

Andy even provides an alternate method (which I have yet to try) and his additional suggestion makes this post even more universal and useful.

If you change the title to limit it to what you suggest, the universality of Andy's solution will be lost in the bowels of the "Allow edit in cell" title. A more appropriate title could be "Determine an Excel setting and use the result in a formula", or something more general and useful. Please don't use the "Allow edit in cell" restriction. If you do, fewer people will see the benefit of Andy's solution.

However, having said all that, I do respect, and am paying attention to, your suggestions. I will comply and will try better with my titles in future posts. Please don't kick me off your forum. I need you guys!

With much respect and understanding for your concerns and request,
Dave (in Canada)

PS. Andy, I will try your alternate suggestion tomorrow, and will respond to that post accordingly. I am already modifying your formula to use it to test for other settings where needed. You gave me a jackpot!

Re: Use Option Settings In A Formula

Hi Andy:

I usually have the "Edit directly in cell" option turned off because I always want to edit in the same place (the formula bar). And the "Edit directly in cell" option will often hide information in adjacent cells that I need to refer to while I'm editing. It's a nuisance!

Anyway, I'm preparing a workbook for others to use where it would be easier for the user to copy a chunk of data from a large merged text cell and paste it into a cell on a web application form. So I provided two buttons beside that cell to turn on "Edit directly in cell" to do the copy and paste, and then the other button to turn it back off when finished. However, the buttons were not indicating that they had been clicked. By using your user defined function in a formula, I can indicate below the buttons whether this option is turned on or not. (I've already used it just now and it works like a charm!)

Which leads to another option that might be better - a radio button that shows the current status, and which can be clicked to switch the status. How would that be done, please? (No need to reply if you're busy, but it could be an option that could be used in many, many situations!)

Thanks very much, ..... Dave (in eastern Canada)

Re: Use Option Settings In A Formula

Andy:

Thanks very much for your quick reply. I can, and will, use your suggestion immediately and move on, with much gratitude.

However, it would be so cool is this could be done with an "ordinary" function without resorting to VBA. Is there a way?

Much thanks, ..... Dave

I want to return a true or false result that I can use in a formula to indicate whether "Edit directly in cell" has been "set". The VBA code is

Application.EditDirectlyInCell = True

. How can I access this result with a function or formula?

Regards, ..... Dave

Re: VBA macro won't run to completion in a large workbook!

Hi Folks:

Thanks for all the suggestions. Sorry I didn't get back to any of you before now.

Guess what! The problem cleared itself up and went away. I can't remember doing anything specific to fix it. It just fixed itself.

However, the problem returned a few days later after I had been in the VBA editor making a few adjustments to one of my very humble macros. When the problem returned this time, I shut everything down and rebooted. Then things worked fine again. (However, I remember an earlier time when the problem acted up that it didn't go away after a reboot. But it did go away by itself about a week later. Go figure!)

So thanks for your help and suggestions. I hope my problem stays fixed.

Until next time.
Dave, in Eastern Canada

Hi:

I have several very simple macros for inserting five new rows, copying a range of formulas from various named ranges into the new rows, and then adjusting the height of one of the rows. These macros have been running just find until just now.

What happens is that the macro inserts the new rows, and then halts without any error warning. It just stops and won't execute the remaining instructions. If I place a MsgBox command before the code that inserts the new rows, the Msgbox is displayed, and the rows are inserted after I click OK. But then no more code is executed. If I place the Msgbox immediately AFTER the Insert Row instruction, the rows get inserted, but the Msgbox does not display. The code simply stops executing before it reads the Msgbox instruction.

My workbook is quite large - 10.7 MB. As a test, I deleted most of the 2200 rows, leaving only the top 50 rows. I then saved this reduced file under a test name. Guess what! The macros worked fine! But they won't work in the original large wookbook!

So here are my questions: Is there a file size limit that causes VBA to act this way? Is there a switch or parameter that I can change to get my macros working properly again?

I'm using Excel 2000 on a Sony laptop with WindowsXP Home and 512 MB RAM.

Here is one of the macros for you to review the code (and yes, I know I can make the code more efficient, but I like to understand it when I go back in and review it months later <grin>)

Sub Insert_Sales_Group()


'Insert 5 rows and select cell in column A
    ActiveCell.Offset(0, -6).Range("A1").Select
    ActiveCell.Range("A1:A5").Select
    Selection.EntireRow.Insert       [I]<- Macro stops after inserting rows[/I]
    MsgBox "Press Enter"              [I]<- This MsgBox doesn't get displayed.[/I]
    ActiveCell.Select
    
'Copy the appropriate range of formulas into the inserted rows
    Range("Sales_Group").Copy ActiveCell
    Application.CutCopyMode = False
    
'Set the totals separation row to a height of only 3
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.RowHeight = 3


'Make the new Title of the group the active cell
    ActiveCell.Offset(-2, 6).Range("A1").Select


End Sub

Thanks for any help you can offer,
Dave

[Search mode off]
[Seek Wisdom mode On]

When I use the Zoom feature in Excel 2000 (80%?) to show more columns on the screen, the numbers in the columns do not line up any more. This is particularly noticeable if the number is something like 1,111.11 in a column that contains digits other than ones.

Is this a freak of Excel 2000, or is there a particular font name I can use to avoid this problem? I HAVE tried different options and fonts, but have not been able to find a solution.

Thanks,
Dave

And if I've missed an appropriate FAQ page, could you direct me to it, please?

Re: Rules for &quot;Extend list formats and formulas&quot;

Here is a small portion of one of the databases in question. Notice that the Day, Systolic, and Diastolic fields have the formats and formulas copied without problem when you add a new record. But the Date and Time fields won't copy their conditional formats at all (unless you force it first with a Ctrl+D action and then overwrite the old data. (The Pulse field doesn't have any formatting.)

Are there any restrictions on the types of data or formats that can be used with this feature?

Cheers,
Dave

P.S. In the attached spreadsheet, the elevated blood pressure levels in the last few days are a result of my frustration with trying to get this feature to work properly! :?

Re: Rules for &quot;Extend list formats and formulas&quot;

Hi Shades:

Thanks very much for the quick reply. You must be on my side of the world (everyone else in Australia is asleep! - physically, not mentally!). But obviously, you're much further south than I am.

The snow accumulation here now is over 40 cm (16 inches) and with the wind, the windchill factor makes it feel like minus 17 celcius (almost down to zero fahrenheit).

(I do hope I don't get into trouble with Dave Hawley with our idle chit-chat about weather! Sorry, Dave!

Re your comment about the conditional formatting screwing up the works, I don't think so. Here's why. The next two columns (fields) of one of my databases is a tri-condition for blood pressure monitoring. It colour-codes the cell to green if the result is normal, yellow if high-normal, and red signifies I may die in the next 24 hours! These are "programmed" (conditional formatted) for two columns - one for Systolic pressure, the other for Diastolic. These fields work find and have never had to be Ctrl+D'd to make them work as described in my initial post.

So that's why I'm asking the question. What are the rules for this feature?

(Damn, I wish I lived somewhere where it only got down to only 68 fahrenheit in winter. That would be so grand (Oops! Off topic again!))

Cheers,
Dave in Eastern Canada

Hello everyone:

I have enabled the "Extend list formats and formulas" option in Tools->Options->Edit tab in Excel 2000. I have been using this feature to build several different database lists.

But it is very inconsistent. I know about the last three out of five rule. I have also found that you can't use a formula that refers to a cell on another sheet. Nor can you use a validation list if it's on another sheet - even if referred to by a range name.

But the feature works sometimes for a few records, and then quits working.

My question is: Is there a list of rules somewhere that I can refer to so that I can know in advance what works and what doesn't?

For instance, if I use Ctrl+; (semicolon) to enter the current date in the next record, that stops the automatic conditional formatting from the items above (my conditional format makes a date bolded and underlined if different from the one before it). Also, Ctrl+Shift+; to enter the current time stops the conditional formatting (I have three different formats depending on whether the time is in the morning, afternoon, or evening).

I CAN get around all of the frustrations just listed by using Ctrl+D to copy the cell contents and formats from the cell above, and then entering my real data, such as the current date or time, but I shouldn't have to do that.

I could also create a macro that copies a named range into the line where I'm about to enter my new record (the copied range would contain any conditional formatting and/or formulas), but I want to keep this one really, really simple.

Any suggestions? Does anyone have the list of DOs and DON'Ts?

Thanks,

Dave in Canada
(presently storm-bound at home by one of the worst winter storms in a long time! 35 cm of snow and winds gusting to 100 km per hour! Sigh. And I was hoping they'd cancel winter because of lack of interest.)

Re: Showing text of a formula or two as text

Hi Everyone:

I tried your suggestions. Not much luck yet with making the function available in all bookbooks automatically. Meanwhile, since I have only four or five workbooks where I need this function, I've been copying and pasting to those books. And that's just find for now.

I want to thank all of you who contributed to this post. I've been using the function to show the text of the formula within adjacent cells, and it works exactly like I want it to, and I can insert rows and columns without having to go back to those cells to retype or recopy/repaste that work - a great time saver.

In fact, I'm getting a kick out of using the function. Everytime I type it in and see the results, I grin from ear to ear and chuckle (sometimes out load), "That's so cool!"

Thanks everyone. I have just got to learn me some more VBA!

Dave (in Canada)

Re: Showing text of a formula or two as text

Hi again Dave:

I just tried something else (within three minutes after posting the last reply).

I clicked in the current VBAProject, the Book1 sheet I was experimenting in, and entered your code in a module there.

Works like a charm! Thanks very much! I even created an AVERAGE formula below the other data and merely copied the cell containing "your" new function. The copied function immediately showed the correct result, i.e. =AVERAGE(A1:A3) as the text of the function.

But why doesn't it work while it's in PERSONAL.XLS? Doesn't it have to be there in order to available in other workbooks?

Is this where I need to use those options for Private, General, Global, or something like that?

Thanks,
Dave (in Canada)

Re: Showing text of a formula or two as text

Dave:
Thanks for the reply and info. I know enough VBA to get into trouble, but not enough to get back out. (Actually, I know a fair amount about Excel, but practically nothing about VBA! I think I may need your training course.)

Here's what I did after I followed your instructions:

I entered a few numbers in cells A1:A3. I then entered the function =sum(A1:A3) in cell A4. I then selected cell B4 and entered =showfunction(A4) and pressed Enter. I expeced to see the text =SUM(A1:A3) to be displayed in cell B4, but #NAME? is displayed instead. (The text 'showfunction' in the formula in the formula bar doesn't convert to capital letters either (is this expected with VBA functions as with regular functions?))

Other info: The code is in a Module3 in the VBAProject named PERSONAL.XLS. The name 'class' in the title bar above the code is 'General'.

Is there something else I should have added that you may have assumed I should have known. I'm a very beginner starter with VBA!

Thanks for your understanding,
Dave

Re: Book1 not disappearing after opening a workbook

Mr. Hawley, Sir!!

Thank you for the prompt reply. But I should have figured that one out myself!

(The reason for the "Mr. Hawley, Sir" thing is that I bought your book a few months ago, and it is really, really great.

I especially loved the hack #64 "Make Your Formulas Increment by Rows When You Copy Across Columns". It was so helpful for scheduling Raw Materials associated with Finished Goods over several weekly time periods that I developed it further to increment both down and across, and I also added features that keep it working correctly even if row and columns are inserted above and to the left. And to speed it up, I removed references to ROW() and COLUMN() to outside the formulas. Otherwise, it became very sluggish.

You're one smart hacker!
Thanks very much.
Dave

I have a need to show the text of a few - and only a few - formulas in an instructional worksheet. I know I can use the tools->options view to show all formulas in the sheet as text. I know I can insert an apostrophe in front of the equals sign, copy the formula to the adjacent left cell, say, and then delete the appostrophe. This would then be an exact copy of the formula.

BUT, if I insert a row above or a column to the left of this cell, the references are no longer "with it". What I need is a formula that references the original formula and "extracts" the text of the formula as text, not as the resulting returned value of the formula. I need the references in the returned text version to adjust to the inseting of rows and columns as I add more examples.

I've experimented quite a bit with all of the Text Functions and combinations thereof, and even such functions as CELL.

Is there a way to do this?

Dave (in Canada)

I've been experimenting with some VBA examples and even resaving the default start-up empty book (as Book in the xlstart folder) with new styles, etc. After starting Excel, this book would disappear after I retrieved some other non-empty workbook. But now it hangs around as just a continuously-open, empty workbook, until I close it myself.

What is causing this, please, and how do I get Book1 to close automatically after I retrieve any other workbook? Did I poke my cursor into something I shouldn't have? :?

Thanks,
Dave (in Canada)

In Lotus 1-2-3, one could copy from a range without first having to go to that range first to highlight it, press Ctrl+C, then return to the target cell and press Ctrl+P.

The sequence in Lotus 1-2-3 was:

Slash, Copy, Backspace, type in the desired range name to copy from, press Enter, then a final Enter to paste it to the target cell, all without having to leave the target cell.

How is this done in Excel 2000, please?

David Penney