Row dropdown triggers converting entire table row to values

duteberta

Board Regular
Joined
Jun 14, 2009
Messages
89
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Attachments

  • Screenshot 2024-03-07 at 5.38.39 PM.png
    Screenshot 2024-03-07 at 5.38.39 PM.png
    104.2 KB · Views: 7

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:

Rich (BB 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.
 
Upvote 0
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:

Rich (BB 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...
 
Upvote 0
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.
 
Upvote 0
I didn't realize it was an Excel table. Try this version:

Rich (BB code):
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").
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top