Enable/Disable cell based on other cell

[montoya]

Dear all,

I'm new in VBA and cannot handle it. I would like to ask you to provide me some references where I could learn it, so to that starts to be familiar with it.

I have a small problem. Basic idea is, sheet is protected (empty password), and only cell "C2" is unlocked (allowable to edit). Based on content in cell "C2", i want to enable or disable cell "A2" as it follows: if cell "C2" is "LIQUID", just enable ONLY cell "A2" from entire sheet, if content is "GAS" then keep the cell "A2" locked, and give to it (cell "A2" a certain value with will be calculated within program), if it is empty, then do nothing (but it is supposed that it won't be empty).

I have a small code with does the work only partially. It works perfectly for "Liquid" case and empty case, but it doesn't work for "Gas" case. Actually it works for "Gas" as well because it does what I want, but at the end I'm getting a error, and excel crushes. The error type is: "Run-time error 28: out of stack space". Could someone come up with an idea, please?

Here you are the code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim f As String, ro As String
    Dim dens As Integer
    ro = Range("A2").Value
    f = Range("C2").Value
    ActiveSheet.Protect
   Select Case f
    Case "Liquid"
        ActiveSheet.Unprotect
        Range("A2").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    Case "Gas"
        ActiveSheet.Unprotect
        Range("A2").Select
        'ro = 50 + 4
        Range("A2").Value = 50+4
    Case ""
        ActiveSheet.Unprotect
        Range("A2").Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        ActiveSheet.Protect
        Exit Sub
    End Select
     ActiveSheet.Protect
End Sub  

Also I have atached the excel file to see better error.

Any idea are appreciated.

Thank you.

[AlphaFrog]

When the code changes the value for A2, that will trigger the Worksheet_Change event procedure to run again. Then the code changes A2 yet again creating an endless loop.

This will prevent the code from triggering itself.

Please Login or Register  to view this content.

[montoya]

Hi AlphaFrog

Thank you very much. It works perfectly. I have just added a small changes on the code so that for gas case, when A2 is 54, to bloc it again. Now it works perfectly.

Another question is, on C10 cell I want to add a data validation so that to make only 2 options available, gas and liquid. A drop box with these 2 option. When I make it, it works fine as well. But when I want to save the file (with data validation), it warnings me that data validation cannot be saved and it will be removed. List with data, (Liquid and Gas) are in sheet 2. If I reopening the file after I had saved it, the data validation is gone. The good thing is that the file still working fine if I change it manually afterwards

Why it is happening in this way? How can I keep data validation on C10?

[AlphaFrog]

Unlock cell C10

[montoya]

Hi AlphaFrog

I have unlocked cell C10. Anyway, the password is blank. There is no any word for the password. You can find file attached.

Thank you very much

[alansidman]

I'm new in VBA and cannot handle it. I would like to ask you to provide me some references where I could learn it, so to that starts to be familiar with it.

I am using the 2010 edition of this book, but I noticed you are using Ex 2003, so this might be a good book for you. I like how he puts things in perspective. Makes it easy to understand. Lots of examples.

http://www.amazon.com/Macros-Microso...238085&sr=1-18

[montoya]

I noticed you are using Ex 2003, so this might be a good book for you.

Thank you alansidman for this book. Is this book related to Ex 2003 or to Ex 2010?

Not exactly. I'm using Ex 2010. You maybe concluded it because the file I have attached are .xls and not .xlsx extention. I just fixed to save them as Ex 2003.

[AlphaFrog]

I can save it with the C10 Data Validation List and the sheet protected. I'm using Excel 2003. I don't know what to tell you.

As a test, can you save it without the sheet protected?

Exactly what steps are you doing to set up the data validation list?

[montoya]

AlphaFrog,

I define data validation on cell C2 using Data Ribbon -> Data Validation - > List - > use list from sheet 2.
In file book34, I have applied data validation on cell C2, not in cell C10. I want to change content on C2 based on options from drop down list made using data validation. When you choose (from list) Gas, on A2 should appear 54 (and remains protected), when I choose Liquid, it should allow me to edit in cell A2. It works perfect, but when I want to save it, data validation from cell C2 is removed. It also gives an warning about it.

See a picture attached and file. List for cell C2 is in the sheet2

[AlphaFrog]

In Excel 2010, you can make a DV List with a source reference to another worksheet like this
=Sheet2!E5:E6

You cannot use a source reference to another sheet with DV Lists in Excel 2003. That's why it's removing the DV List when you save with the .xls file format.

You can use a named range, or you can put the list as comma separated text in the DV Source e.g. Gas, Liquid
Excel 2003:

• Create Drop Down From List on Different Sheet
• Use a Delimited List

For future reference: it's best if you state the excel version you are using and if applicable, if you save it as a different version format.

[montoya]

AlphaFrog,

Thank you so much. I used a named range and the problem is solved. It works now as I wanted. I really thank you.

[alansidman]

I assumed you were using 2003 because that is what your profile is showing. Here is a link for 2010 book.

http://www.amazon.com/VBA-Macros-Mic...2255845&sr=1-3