How do I make a macro run on a particular cell once a range of cells all contain data?
In my worksheet, i need a macro to run in the cell in column F once the cells in columns A, B, C & E of the same row contain data and the last of these has been selected away from: eg, the macro needs to run in F3 once all data entries into A3, B3, C3 & E3 are complete but not before - if A3, B3 & C3 all contain data and the user starts entering a 6-digit number into E3, I don't want the macro to run until they are finished entering the number.
Hopefully the above makes sense but if not please ask.
I know that this should be some sort of event handler but am too green on vba at the moment (learning fast though thanks to this website)
UPDATE
The code I have at the moment is this, but I think I may have replaced too much with the Application.Run line.
Private Sub Worksheet_Change(ByVal Target As Range)
cur_row = Target.Row
a_len = Len(Cells(cur_row, 1).Value)
b_len = Len(Cells(cur_row, 2).Value)
c_len = Len(Cells(cur_row, 3).Value)
e_len = Len(Cells(cur_row, 5).Value)
If a_len > 0 And b_len > 0 And c_len > 0 And e_len > 0 And Target.Column <> 6 Then
Application.Run "'Pricing Calcs V4.xlsm'!price_calc"
End If
End Sub
The main price-calc code that is caused to run is below and the other problem we are getting is when a whole lot of data is copied and pasted in, the macro goes into overdrive and crashes Excel.
Sub price_calc()
On Error GoTo EndMacro
Dim Wb1 As Workbook
Application.ScreenUpdating = False
blind_type = Cells(Selection.Row, 1).Value
fab_width = Cells(Selection.Row, 3).Value
fab_height = Cells(Selection.Row, 5).Value
If blind_type = "Chain-Op Roller in Thames or Dart" Then
ws_name = "R20 Thames-Dart"
ElseIf blind_type = "Chain-Op Roller in Medway or Roe" Then
ws_name = "R20 Medway-Roe"
ElseIf blind_type = "Crank-Op Roller in Thames or Dart" Then
ws_name = "R20C Thames-Dart"
ElseIf blind_type = "Crank-Op Roller in Medway or Roe" Then
ws_name = "R20C Medway-Roe"
ElseIf blind_type = "127mm Vertical in in Thames or Dart" Then
ws_name = "VL30 127mm Thames-Dart"
ElseIf blind_type = "89mm Vertical in in Thames or Dart" Then
ws_name = "VL30 89mm Thames-Dart"
End If
Set Wb1 = Workbooks.Open("N:\11. General\Price Charts.xlsx")
row_location = Application.WorksheetFunction.Match(fab_height, Wb1.Sheets(ws_name).Range("A1:A28")) + 1
col_location = Application.WorksheetFunction.Match(fab_width, Wb1.Sheets(ws_name).Range("A1:S1")) + 1
unit_price = Sheets(ws_name).Cells(row_location, col_location).Value
EndMacro:
Wb1.Close SaveChanges:=False
Selection.Value = unit_price
Application.ScreenUpdating = True
ActiveCell.Offset(1).Select
End Sub
Please help if there's a simpler way of doing this
UPDATE 2
Perhaps I should say that, as long as the cells are filled one at a time from the left by tabbing on to the next each time, as soon as you tab past the cell in column E and into column F, the macro runs fine.
The problem is only when clicking return instead of tab from the 'E' cell so that it's not the 'F' cell that's selected.
And then the crash happens if you paste a whole lot of data in at once - it's all like the macro is trying to decide whether or not the cells are all filled.
Instead of having the macro run automatically, could it be made to run all the way down column F and stop as soon as the row is not filled. This could be activated by a button.
Any suggestions gratefully received.