Row dropdown triggers converting entire table row to values

[duteberta]

I have a table with the fist column being "Status" which is a dropdown of 3 values ("ACTIVE", "PENDING" and "SOLD").

If I select "SOLD" from the dropdown I want THAT ENTIRE ROW to convert to values. Meaning all formulas or functions within that row turn to values (so they never change).

Maybe the drop-down trigger of "SOLD" would trigger a macro that would copy and PASTE as values with number formatting. Not sure exactly how to do this.

I need help knowing the VBA code to enable for this please.

 

[Eric W]

Open a copy of your workbook. On the page with the table, click on the page tab and select "View Code". On the window that opens, paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NumCols As Long

    NumCols = 8
    If Intersect(Target, Range("B3:B10")) Is Nothing Then Exit Sub
    If LCase(Target.Value) <> "sold" Then Exit Sub
    Application.EnableEvents = False
    Target.Resize(, NumCols).Value = Target.Resize(, NumCols).Value
    Application.EnableEvents = True
    
End Sub

Change the range in green to the range of cells that might have "Sold" in it. Change the 8 in red to the number of columns in the table, including the Sold column. This assumes the Sold column is on the left. Go back to your Excel sheet and give it a try.

 

[duteberta]

Open a copy of your workbook. On the page with the table, click on the page tab and select "View Code". On the window that opens, paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NumCols As Long

    NumCols = 8
    If Intersect(Target, Range("B3:B10")) Is Nothing Then Exit Sub
    If LCase(Target.Value) <> "sold" Then Exit Sub
    Application.EnableEvents = False
    Target.Resize(, NumCols).Value = Target.Resize(, NumCols).Value
    Application.EnableEvents = True
   
End Sub

Change the range in green to the range of cells that might have "Sold" in it. Change the 8 in red to the number of columns in the table, including the Sold column. This assumes the Sold column is on the left. Go back to your Excel sheet and give it a try.

For the green nomenclature since this is a table should I use "table name[tablecolumn]" so that if I add rows it's always inclusive? And for red- what if I add columns- will this update? I'll try it...

 

[duteberta]

For the green nomenclature since this is a table should I use "table name[tablecolumn]" so that if I add rows it's always inclusive? And for red- what if I add columns- will this update? I'll try it...

I just tried and it did not work. The functions were still in the cells of the "Sold" row.

 

[Eric W]

I didn't realize it was an Excel table. Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StatusCol As Long, r As Long, MyTbl As Range, MyRow As Range

    StatusCol = 1
    Set MyTbl = Range("Sales")
    If Intersect(Target, WorksheetFunction.Index(MyTbl, 0, StatusCol)) Is Nothing Then Exit Sub
    
    If LCase(Target.Value) <> "sold" Then Exit Sub
    Application.EnableEvents = False
    r = Target.Row - MyTbl.Row + 1
    Set MyRow = WorksheetFunction.Index(MyTbl, r, 0)
    MyRow.Value = MyRow.Value
    Application.EnableEvents = True
    
End Sub

StatusCol should be which column in the table that has the status, and MyTbl should have the name of your table (I used "Sales").