I would like to do the following: In one cell I have a drop down menu where the user can select “ON” or “OFF” In another cell, I would like to have the ability to enter data if “ON” and to blank out the cell if “OFF” Any ideas?
Use a data validation rule in the first cell, where you provide a list of allowed values in a named range elsewhere.
data validation w/ conditional formatting on the greyed out cell… perhaps grey highlighting w/ grey text so as to create the illusion of nothing showing up. Good luck…
i need the cell to be blank - data validation is okay but not perfect since it doesn’t blank out an existing value when the input is turned off, it just precludes data entry. Thanks for the suggestions.
try Google Groups
VBA should do it.
Create a ComboBox named Tester Private Sub Tester_Change() TextBox = Tester.Value If TextBox = “Yes” Then Selection.ClearContents Range(“D1”).Select Selection.Interior.ColorIndex = xlNone Else Range(“D1”).Select Selection.ClearContents With Selection.Interior .ColorIndex = 1 .Pattern = xlSolid End With End If End Sub
Yep ^ Gotta learn VBA. You can make Excel sit up and beg. Also, if VBA isn’t enough for you you can put DLL’s into VBA that can do anything. You can intercept Excel’s formation of it’s own ActiveX objects and process the inputs to them before they even make it to Excel, etc…
Now all we need is Visual Basic for Women and we’ll be set.
VBW reports: Heap overflow error…
go to www.mrexcel.com and visit the message board. there is alot of good information there regarding anything you want to know about vba or excel.
MFE: Do you have to run the macro for it to clear the cell or will it happen automatically?
buddyglass Wrote: ------------------------------------------------------- > MFE: Do you have to run the macro for it to clear > the cell or will it happen automatically? it’s embedded in the change of combobox (hence Sub ComboBox_CHANGE()). so if change from yes to no or no to yes it automatically runs the macro.
thanks, i’ll try it out.
I agree with MFE and Joey that VBA is very useful for modeling in Excel and everyone who is serious about financial modeling needs to learn it. However, this task can be accomplished without VBA. Here is what you do: 1) go to Forms toolbar and select Combo Box 2) somewhere in your Excel file enter ‘ON’ and ‘OFF’ in separate rows, typically you enter it somewhere and place control box above it 3) right click on your control box, go to ‘format control’ and for ‘input range’ select a range for the rows where you placed ‘on’ and ‘off’; for ‘cell link’ give it a cell reference to any blank cell in your file, typically you put it close to your control box, this cell will be used to differentiate when on/off option is chosen (select on or off in your control box and you will see that cell dedicated for cell link will change to 1 or 2 respectively 4) lastly, all you have to do is to write a simple if statement. Lets say you set your cell link to A5, then you write =if(A5=1,‘enter your data or put a formula for data entry’,if(A5=2,""))
The posts above answer your question, but you might find it useful to consider the following: - why do you need to do this? Can’t you change the structure so that you don’t need this. Any situation where cells are appearing and disappearing isn’t so good. - if you’re designing it for a foolish client who isn’t capable of ignoring a redundant cell, then perhaps they shouldn’t be using Excel! Seriously, they will find someway to mess things up if they are that bad. - I’d recommend using TRUE and FALSE (or 0 and 1) over OFF, ON, Yes, No or Maybe. Makes things much easier and there is less room for mistakes e.g. (A1="Yes)*(A2=“Yes”) reduces to A1*A2. You also throw in SUMPRODUCTS and arrays too. - Some people distrust macros especially when they are involved in calculations hence conditional formatting might be the best solution from above. For example, if I clicked on the ON switch and a macro ran resulting in a cell removed, I would be more than a little sceptical of what else had changed that wasn’t an explicit dependent of that cell. Then I’d be off looking into VBA and unimpressed.
HoldSideAnalyst Wrote: ------------------------------------------------------- > Now all we need is Visual Basic for Women and > we’ll be set. Cute.
try{ Woman.bringBeer(cold); Woman.changeOutfit(Woman.ROMANCE_MOOD, +5 ); Woman.inviteFriend(Woman.PRETTY_FRIEND); } catch NoFlowersException { Woman.recieveGift(Man.bringWine(Man.LOTS_OF_WINE)); Woman.recieveCompliment(Man.say(“You are so beautiful tonight”)); }