Default cell value, looking for code

[jetjockey]

Hi,

I need to have a cell that will either accept a new value or display the value of another cell under certain conditions. My problem is that if I enter a formula in that cell It will delete the formula entered in that cell, How do we go around that...
I am looking for an explicit code I could copy paste...


ie Cell A1= 103 default at anytime the user can manually change that value, but if cell a12 = CYTR Cell a1=cell a2
if cell a12 = BGTL then Cell a1=cell a3 etc.


Thanks Pierre

[Sisyphus]

Hi,
This is the code you need in the code sheet of the worksheet on which you have your cell that needs to be monitored:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then CheckEntry Target
End Sub


and this is the code that should be on a code module, Module1 by default:

Code: Select all

Public Sub CheckEntry(ByRef Target As Range)
   
    With Target.Worksheet.Range("A12")
        If .Value = "CYTR" Then Target.Value = Range("A2").Value
        If .Value = "BGTL" Then Target.Value = Range("A3").Value
    End With
End Sub


I didn't understand what you mean by "etc." So, this code stops where your description stopped. Perhaps you can continue it yourself. Here is a little help to find your way in VBA in case you are very new to it:

Open the VB Editor window, either by pressing Alt+F11 or by selecting Tools / Macro / Visual Basic Editor from your CommandBar.
You should see the Project Explorer window on the left of the VBE window. It lists the open workbooks’ work and code sheets. If you don’t see it, open it by selecting View / Project Explorer or by pressing Ctrl+R.
To insert a code module right-click on the name of your VB Project (that is, the name of your workbook). In the dropdown that opens, select Insert / Module. It will be called Module1 by default if there aren’t any other modules with default names already.

Best of luck!

[jetjockey]

Thanks again

[jetjockey]

Well I did not explain myself clearly,

I need an input cell A1 which displays a value depending of cell A12 "CYTR" or" BGTL"

Cell A1 needs to update if Cell A12 updates, and if cell A12 = "manual entry" then A1 as to accept any value directly.

I should have been more explicit.

Thanks

[Don]

Try changing this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then CheckEntry Target
End Sub

to this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$12" Then CheckEntry Target
End Sub

and maybe also this:

Code: Select all

Public Sub CheckEntry(ByRef Target As Range)
   
    With Target.Worksheet.Range("A12")
        If .Value = "CYTR" Then Target.Value = Range("A2").Value
        If .Value = "BGTL" Then Target.Value = Range("A3").Value
    End With
End Sub

to this:

Code: Select all

Public Sub CheckEntry(ByRef Target As Range)
   
    With Target.Worksheet.Range("A12")
        If .Value = "CYTR" Then Range("A1").Value = Range("A2").Value
        If .Value = "BGTL" Then Range("A1").Value = Range("A3").Value
    End With
End Sub

(NOT TESTED)

But, about the manual entry part, why do you need to lock the cell for that? Because, the only way to do that would be to lock the spreadsheet and, depending on the rest of the spreadsheet, have all cells but A1 unlocked and then turn on/off the protection as needed; this can be done with code, but, if it's not really necessary, there's no point to do it.

[jetjockey]

This is exactly what I was looking for, and it works like a charm..

I do have multiple cells that are unlock on my sheets to allow users to enter data. More like a from which also brings in results from chart calculations.


Thank you for your help