Help about an automation i want to create

[damav78]

Hi, I would like your help in an issue that i cannot find a way to solve.
I have 2 cells.
The first one has a dropdown list with one option only available.
The second one is a formula cell that collects multiple data from other cells and its dynamic. It means that when i change values to other cells this cell is affected.
So what i would like to do is:
When the first cell is blank the values on second cell to change as normally do (dynamically).
If from the first cell i choose the predefined value from my dropdown list then the second cell to actually "lock" the current value that has at that moment and dosen't be affected anymore from any changes.
Thank you in advance

 

[damav78]

It should have no effect on any other sheet.

Also one last thing. Now that i have save it as xlsm, everytime i open the file i got a Security warning in the yellow banner says that the macros have been disabled and i have to select "enable content" in order all this to work. Is there anything i can do to be enabled by default?

 

[damav78]

Also one last thing. Now that i have save it as xlsm, everytime i open the file i got a Security warning in the yellow banner says that the macros have been disabled and i have to select "enable content" in order all this to work. Is there anything i can do to be enabled by default?

Now i noticed that only row 7 is affected. So if i would like to use this in my table with a lot of rows do i need to replace the B7 and Q7 inside the code with B7:B50 for exmple? as also Q7:Q50? And if yes what will happen if i add more columns or rows in my table? will renew the code automatecally including the new rows or do i have to do it manually?

 

[damav78]

Dear @mumps please ignore the message about the security warning. It was only showed up the first couple of times i opened the file.
Just check my other message:
Now i noticed that only row 7 is affected. So if i would like to use this in my table with a lot of rows do i need to replace the B7 and Q7 inside the code with B7:B50 for exmple? as also Q7:Q50? And if yes what will happen if i add more columns or rows in my table? will renew the code automatecally including the new rows or do i have to do it manually?
I have try it but propably it's not the right way because nothing worked as it should. So what do i have to do to affect my whole table (rows)?

 

[mumps]

This version will work with all rows starting at row 7. It also assumes that you will have a formula in all applicable rows in column Q.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
    End If
End Sub

 

[damav78]

This version will work with all rows starting at row 7. It also assumes that you will have a formula in all applicable rows in column Q.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
    End If
End Sub
[/CODEOhhhh
[/QUOTE]

Ohhhh.... You are great!!! I just notice one thing... i have also another column with a formula that has to be locked like Q column. Is column T. The formula in column T is =(K7-($D$3*$D$2))/$D$1
Actualy the column Q refers to kg and column T refers to money. So when i choose the drop down from B it has to lock both cells Q and T.
Sorry if i am obtrusive but you are the only one who help me and i have no idea to do all that by self.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
        Range("T" & Target.Row).Value = Range("T" & Target.Row).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(K" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

 

[damav78]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
        Range("T" & Target.Row).Value = Range("T" & Target.Row).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(K" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

You are fantastic!!!!!
So... i suppose that if in the future i need to add some more columns in my table i just need to change the Q and T columns letter of the code with the new ones right? Of course as also the letters in the formulas if they are affected. Am i right?

 

[mumps]

That is correct. Just follow the same code pattern.

 

[damav78]

That is correct. Just follow the same code pattern.

You have helped me to solve an issue i was strunggling to find a way for weeks now. And acctualy you did it better than i could imagine.
Thank you so much for your precious help and the time and effort you spent for me.

 

[damav78]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
        Range("T" & Target.Row).Value = Range("T" & Target.Row).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(K" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

Dear @mumps i just notice an issue at column T. Although i have set it as currency cell with 4 demicals, when i choose the drop down list in order to lock it, it makes a rounding in 2 demicals although it showing 4 demicals. For example before "locking" i have a value 1,0488 and after locking it makes it 1,0500. I check the cell format after "locking" and it is still currency cell with 4 demicals.