When a drop down list is changed, VBA code to insert a formula into other cells (which are also a drop lists)

[Steve1208]

I have been trying to get the VBA to enter a specific formula into another cell (which are also drop down lists) when another drop down list is changed. Please see below for clarity:


Cell C2 - When this drop list cell is changed

Cell B14 - I would like this drop list to have the following formula automatically inserted:
="Contract Controls:"&" "&INDEX(Contract!D95:XFD95,MATCH('CHECKLIST 2'!C2,Contract!D3:XFD3,0))

Cell A14 - I would also like this drop list to have the following formula automatically inserted:
="Contract:"&" "&INDEX(Contract!D4:XFD4,MATCH('CHECKLIST 2'!C2,Contract!D3:XFD3,0))

Thank you in advance for any advice

Best Regards

 

[Jerry Sullivan]

Hi Steve,

Here's some code for you to try. Copy it into the Sheet Code Module of the sheet that has the 3 dropdown cells. To get to that module, right-click the tab of that sheet then pick: View Code

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim sErrMsg As String
  
 On Error GoTo ErrProc
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False
 
 If Target.Address <> "$C$2" Then GoTo ExitProc
 
 Me.Range("B14").Formula = _
   "=""Contract Controls:""&"" ""&INDEX(Contract!D95:XFD95,MATCH('CHECKLIST 2'!C2,Contract!D3:XFD3,0))"

 Me.Range("A14").Formula = _
   "=""Contract:""&"" ""&INDEX(Contract!D4:XFD4,MATCH('CHECKLIST 2'!C2,Contract!D3:XFD3,0))"


ExitProc:
 On Error Resume Next
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub

 

[Norie]

Do you only have one dropdown?