@CC, I see your position, but where do you draw the line? Life can be so easy if a few simple rules are followed for spreadsheet layout (and for posting in forums, and for many other things). Let's try and spread the good practice rather than supporting the bad practice by writing complicated, convoluted formulae or VBA just because it can be done.
More often than not, this complicated workaround is not something the OP will understand, anyway, so after a month, when they make some changes to their spreadsheet, the complicated solution no longer works and they don't have the stuff to fix it. With good spreadsheet design, you only need a few basic formulas to keep going in most situations.
Somewhere, in another forum, not too long ago. Today, actually:
OP: i need to detect when the value of the active cell is changed. I need to detect when the active cell on the worksheet is "A2" and after that I need to catch the value of the active cell if it is different to empty.
the problem is that i need to catch the value (for example in another cell) at the same time that the activecell is editing
Me: Hello, you can do that with a Worksheet Change event macro.
Right-click the sheet tab and select "View Code". Copy the macro below into the code window and add the steps you want the macro to do.
cheers, teylyn
thks teylyn for your example but is not what Im looking for.
your code show the value after the active cell change to another one
I need to catch the value of the active cell when you are writing on it
Member A: Can you provide an example with the original cells and values and then - what they should look like after the change?
Me: You cannot duplicate a cell content in another cell while you are still editing the cell. You need to confirm the data entry in the cell with one of the known methods (Enter key, tab key, etc) and only then is the cell content available for manipulation via VBA or formulas.
Member B: While I'm not quite 100% on what you what
This code
- checks to see if A2 was changed
- if A2 is now blank then the value is restored to the prior non blank value (using a static variable)
- if A2 is not blank that the static variable is updated to the new value
Cheers
mmmm I cannot catch the value????
Ok one last question it is possible to detect when the active cell is editing???
Me: Well, kind of. As far as I know you can't run VBA code while Excel is in edit mode, so not being able to run code might be one indicator.
But maybe you need to step back a bit. Instead of assuming the solution can be achieved with specific technique, why don't you describe what you want to achieve or why you want to do this? That may take us to different approaches.
cheers, teylyn
Member B: > why don't you describe what you want to achieve or why you want to do this?
Seconded
OP: Let me try to explain myself
this is what I try to do
when the cel "a2" is the active cell and is in edit mode I want to detect that state to make visible a combobox (instead the cell )with two values:
1.- Value 1
2.- Value 2
When the user select a value from the combobox I would like to assign that value to the active cell .
Me:Ah! That is called data validation and can be achieved without macros.
Click the cell. Click Data - Data Validation
[... bla bla on data validation]
OP:Sweet!!!
That was a great solution bro
thanks!!!!
Errhmmm -- Duh!?
Should I have tried to find a way to work against Excel and VBA and somehow MAKE Excel do what the OP wants?
Hah!
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Domski
icon below to give reputation feedback, it is appreciated.
Bookmarks