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

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to the cell that contains the drop down list. Change the range (in blue) to the cell that contains the formula. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Range("B1").Value = Range("B1").Value
End Sub

 

[damav78]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to the cell that contains the drop down list. Change the range (in blue) to the cell that contains the formula. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Range("B1").Value = Range("B1").Value
End Sub
[/CODEDea

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to the cell that contains the drop down list. Change the range (in blue) to the cell that contains the formula. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Range("B1").Value = Range("B1").Value
End Sub

Thank you for your quick reply. It worked but i have notice couple of things. 1)When i choose from dropdown list the other cell has an error green arow. So if i want to delete the text from the first cell in order to get back to normal i have to manually choose the second cell to be recalculated. 2)And most importand.... it does not let me save the file. what do i have to do?

 

[mumps]

other cell has an error green arow.

I'm not sure what you mean by this. The macro simply removes the formula and replaces it with the existing cell value.
This version will restore the formula if you delete the text from the first cell. Change the ranges and formula to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Range("B1").Value = Range("B1").Value
    Else
        Range("B1").Formula = "=sum(C1:D1)"
    End If
End Sub

The macro should not interfere with the "Save" function in Excel. Are you using any other code in your workbook?

 

[damav78]

I'm not sure what you mean by this. The macro simply removes the formula and replaces it with the existing cell value.
This version will restore the formula if you delete the text from the first cell. Change the ranges and formula to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Range("B1").Value = Range("B1").Value
    Else
        Range("B1").Formula = "=sum(C1:D1)"
    End If
End Sub

The macro should not interfere with the "Save" function in Excel. Are you using any other code in your workbook?

From your example:
in cell A1 i use B7 and in B1 i use Q7
what about the =sum(C1:D1)? what cells do i have to put over there?

Also about the saving issue.... i dont use any othere code. When i try to save my file i got a message that says that if i want to save the file i have to choose from start menu a macro capable file type.
It is a message i have never seen before so i dont know what i have to do.

 

[mumps]

In which cell do you have your drop down list? In which cell is your formula and what is the formula in that cell?
Regarding the "Save" problem: since the file now contains a macro, when you save it, it has to be saved as a macro-enabled file. You do this by selecting "Excel macro-enabled workbook" in the "Save as type:" box in the Save window. This changes the file extension from "xlsx" to "xlsm".

 

[damav78]

In which cell do you have your drop down list? In which cell is your formula and what is the formula in that cell?
Regarding the "Save" problem: since the file now contains a macro, when you save it, it has to be saved as a macro-enabled file. You do this by selecting "Excel macro-enabled workbook" in the "Save as type:" box in the Save window. This changes the file extension from "xlsx" to "xlsm".

The drop down menu is at B7 cell. The cell with the formula is at Q7 cell. The formula is =ROUND((A7*$D$1);0)

 

[mumps]

Try this version in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Range("Q7").Value = Range("Q7").Value
    Else
        Range("Q7").Formula = "=ROUND((A7*$D$1);0)"
    End If
End Sub

 

[damav78]

Try this version in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Range("Q7").Value = Range("Q7").Value
    Else
        Range("Q7").Formula = "=ROUND((A7*$D$1);0)"
    End If
End Sub

I done the save as xlsm thing. Now with the new formula you gave me. When i try to delete the text from the drop down list (in order to have a blank cell) i got a run-time error '1004' message. Aplication-defined or object-defined error. And gives me 2 option, end or debug. If i select debug it opens the code window and the last row after else is highlighted yellow (the Range("Q7").Formula = "=ROUND((A7*$D$1);0)")

 

[mumps]

Does the formula work properly when it is manually entered into Q7?