• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error-handling to stop code from entering a formula which refers to a specific named cell or range

L

lingyai

New Member
I need some suggestions on error-handling help please. This is a rather obscure situation, but trust me, I need to do this.

I have two named cells, "CellA" and "CellB".

The macro enters a formula into CellA. The formula will vary. Basically, the macro must be free to write any formula in CellA that it's told, referencing any other cell(s) it's told, EXCEPT any formula which contains a reference to CellB. So examples of illegal formulas would include "=CellB", "=3*CellB", etc.

When such illegality is attempted, but before it's allowed to occur, there should be a message box, "I can't let you do that, Dave" and only offers the option to exit the sub.

Perhaps this could be accomplished via Excel validation?

I'd be grateful for any help.
 
Hi ,

It is difficult to visualize a situation which would require such a validation rule.

How is the macro entering various formulae into the named cell CellA ?

Even assuming that a reference to CellB can be prevented , what would happen if instead of the reference to CellB , a reference to the worksheet cell address of CellB is attempted ?

Probably if you upload your workbook , with data and code in it , it may make it easier for members to help you.

Or you can wait and see if others come forward with their suggestions.

It would help if you could mention the Excel version you are using.

Narayan
 
Thanks. I am using Excel 2010. I could send a file but it would only have two cells named CellA and CellB. I realise it's hard to imagine why someone would want to do this. Basically it is prevent a circular referemce. It's a long, irrelevant story. I'd like please to just keep the focus of this on the narrower question of whether this specific procedure is possible in VBA. But if I can make it work in my larger routine, I will explain and share the result.

How about something like this workaround?

At a designated point in the code, go to a new, third named cell, CellC, and,as I'd do it in Excel:

--write in CellC the formula, "=CellB"
--in CellC, type " ' " before the equals sign, which turns it to text; copy CellC, then and paste it onto itself as a hard-coded value.

Then,
--go to CellA, and ask, does the text in CellC contain "CellB" anywhere?

If yes, error. If no, carry on...
 
Hi ,

It is still not clear how the formula in the named range CellA will appear ?

If it is to be entered by the user , then the code that you want can be a part of the Worksheet_Change event procedure , otherwise , I am unable to understand how it can be made to run.

Narayan
 
lingyai said:
Thanks. I am using Excel 2010. I could send a file but it would only have two cells named CellA and CellB. I realise it's hard to imagine why someone would want to do this. Basically it is prevent a circular referemce. It's a long, irrelevant story. I'd like please to just keep the focus of this on the narrower question of whether this specific procedure is possible in VBA. But if I can make it work in my larger routine, I will explain and share the result.

How about something like this workaround?

At a designated point in the code, go to a new, third named cell, CellC, and,as I'd do it in Excel:

--write in CellC the formula, "=CellB"
--in CellC, type " ' " before the equals sign, which turns it to text; copy CellC, then and paste it onto itself as a hard-coded value.

Then,
--go to CellA, and ask, does the text in CellC contain "CellB" anywhere?

If yes, error. If no, carry on...
Click to expand...

Ok, yes, the above would work, using this method for saying whether there is specific text within a string

https://exceljet.net/formula/cell-contains-specific-text

So I guess this is solved in that I can do what I'd like, although it seems klunky to have to take the extra steps to convert the formula to text, and then determine whether that text contains a forbidden string. It would be nicer -- faster, I suppose -- if I were able to know directly whether a formula in a cell has a name containing the text string of interest -- I can live with this workaround.
 
NARAYANK991 said:
Hi ,

It is still not clear how the formula in the named range CellA will appear ?

If it is to be entered by the user , then the code that you want can be a part of the Worksheet_Change event procedure , otherwise , I am unable to understand how it can be made to run.

Narayan
Click to expand...


The formula appears in different ways at different times. What I'm asking about would be part of a larger sub which the user starts with a command button. I think I've found a (maybe not elegant) solution, mentioned in my latest post.
 
Mods, how do I edit the title of this thread? I did it with another the other day but can't seem to recall how I did it. I'd like to add "Workaround found"
 
You must log in or register to reply here.
Back
Top