Posts by DuckBill

Richie,

Thanks for the (very) comprehensive reply; extremely interesting.

I guess that the way round my original problem is to use the InputBox Function and then check for a response of "". (I hadn't realised that clicking "Cancel" would generate ""). Then I wouldn't get the "cell reference" problem at all.

Thanks again,

DuckBill

Denis,

Glad my question was useful in some way!

But, surely your code only stops the VBA (or subroutine) if nothing is typed in. What if the user types something then changes his mind and hits "Cancel"?

(Meanwhile, is there any way to disable the "RefEdit"-like behaviour in Application.InputBox?)

DuckBill

Nearly the same language, Denis.

If you use Application.InputBox instead of just InputBox, you get a different format box which behaves as I described.

(I used Application.InputBox because if the user clicks "Cancel" it ends the whole procedure; the InputBox version doesn't seem to do this)

DuckBill

I have a spreadsheet that uses an input box to get some text from the user. But if they mis-spell something and try to use the arrow keys to go back and change the offending text, Excel starts entering cell references. (So you end up with some input like "Some tect $A$5")

I know it's a minor point, but some of our less experienced users get confused by this!

Is there any way to disable this cell referencing?

DuckBill

Dennis,

Sorry, but what is a TOC?

DuckBill

I am helping to create a workbook that incorporates hyperlinks to get from one sheet to another. We want to use the EnableSelection Property to restrict the cells that the user can access, but when we switch the EnableSelection Property to xlUnlockedCells, whenever you try to click somewhere else on the sheet, the nearest hyperlink is activated!

Is there a way round this?

DuckBill

Roy,

Many thanks for the response; the code looks very useful. I guess it also means that there are no "easier" ways of doing this using standard features, as I suspected.

DuckBill

I have a multi-worksheet Excel 2000 file that is being used as a type of database (I say "type" because it is not formatted very well for historical reasons!) and the users would like to be able to search across all the sheets for any records that include a particular text string (e.g. "bearing") and then create a copy of each of those records on a separate worksheet for comparison purposes.

I am fairly confident(!?) that I can create some code that will do this, but I just wondered if there was a standard feature that I could utilise instead?

Any ideas?

DuckBill

Just to let you know that I have now deleted all traces of the old redundant document management system from my PC and....guess what? No more Auto_Open macro problems.

Thanks again for the helpful comments. Hopefully this experience may be of assistance to somebody.(??!!)

DuckBill

Dennis,

I am intrigued by the formula you posted above (which seems to work very well, BTW). It seems to incorporate criteria in a way that I have not seen before (e.g. (A1:A5="01")). I did not know that it was possible to do that where you would normally just specify a range or array.

Where else can this be used and what limitations are there?

DuckBill

Bruce and Dreamboat,

Thanks for the comments. OS is Windows 2000 Ver 5.0 (SP1) running on company network and Excel is 2000 (9.0 SR-1). (I also have Excel 97 installed, but it does not exhibit the same problem)

I have checked various workbooks and if the code is in a Workbook_Open module instead of an Auto_Open procedure it DOES run in every case - so that's an improvement! (Excel still doesn't ask if I want macros enabled, but that's not too much of a big deal.) So I have a bit or re-coding to do now!

We have a redundant document management system in the company that had an excel add-in, the certificate for which has expired. I can't see why that would cause this problem, but I'm checking that.

Thanks again.

DuckBill

Bruce,

Many thanks, Application.Calculate did the trick.

I did think of trying that myself, but I didn't think it would work, because I thought that as the function was not calculating, that mean that its code was not being run, which would mean that any instruction to calculate within its code would not run either...if you see what I mean. (Typical example of thinking too much and not experimenting!)

Thanks again,

DuckBill

I understand about macro security settings and I have mine set to "Medium", so when I open a workbook I should be asked whether I want macros enabled or not.

However, this only happens when I open a file from Explorer or from the recently-used list under File (in Excel itself). If I use File, Open and browse for the file, Excel opens it and enables macros without asking, but ignores any Auto_Open macro.

I used to think that this was a quirk of my PC, but I have since noticed that the same thing happens for a lot of my colleagues. The worry is that some of my most widely-used workbooks rely on an Auto_Open macro to set various values and protect worksheets, etc.

Has anybody come across this and is there a way of ensuring the Auto_Open macros run?

DuckBill

I have not created many user-defined functions, so I may be doing something wrong here. But, I have created one which works as required, but it does not re-calculate automatically. If I edit the cell in which it is used, then it recalculates.

Any ideas? (My options are set for Automatic Calculation BTW)

DuckBill

Thanks Dave, very useful.

DuckBill

Dave,

I don't know what you have done, but I have just logged back on and the dates all look OK now.

However....I did ask for email notification of new replies and that doesn't seem to be working. Sorry!

(I have just previewed the above post, and I notice that there is another "Receive email notification of new replies?" check box on the bottom of the preview screen - if this is not ticked does it overwrite the tick in the previous screen (where you write the post)? If so, maybe it should be changed so that the preview screen picks up the selections from the write screen?)

DuckBill

First of all, congratulations on setting up the OzGrid forum. It looks like it should be a winner!

My question is why do the dates for postings appear incorrectly. (I don't just mean the "wrong way round" as per mm/dd/yy) My dates seem to have the month digit duplicated. For example, my posting here will show as 5/22/03, instead of 2/5/03. Is this just me?

(BTW, I notice that the dates shown in profiles for when a member joined are displayed correctly, as is the date for this posting at the top of the preview screen. It is just the dates in the forum listings)

DuckBill

Dave,

Looks like you beat me to it! (I thought you guys down under would be asleep at this time of day!)

BTW, am I right in thinking that the Target bit in your code means that the code only looks at changes in the target and nothing else? What other advantages are there in using targets.

DuckBill

IrishBob,

I'm fairly new to VBA too, so I'll let the older hands recommend a book (I would also be interested). But as for the question, you can enter VBA code on a worksheet which will automatically activate when something on the worksheet changes; a simple if statement can then check to see if the appropriate cell is >50.

Open the Visual Basic Editor and in the Project section double-click on the appropriate sheet. In the code window, select "Worksheet" from the left drop down and "Change" from the right drop down. This will enter the start and finish bits for the code. then try something like this in between:

If Range("A1").Value > 50 Then
MsgBox "Greater than 50"
End If

You then either insert the code you want instead of the MsgBox above, or call it as another procedure.

Hope this helps.

DuckBill