Selected Answer
You need what's called an "event procedure". That's a VBA procedure that runs automatically when a particular "event" occurs, in this case a change of the slot. Of course, such a procedure must be in a place where the "event" will be noticed which, logically, is on the tab where the change occurs. Since it's VBA it can't be on the same level of that tab where the cells are. Therefore it must be in the code module of that tab. Don't try any other location. It won't work.
So that we both know which tab I'm talking about. I created a sheet called "MySheet" and, for good measure, another one called "OtherSheet". You change a cell on "MySheet:" and the code changes a cell on [the] "OtherSheet".
Which cell? And which other cell? I called one of the "TriggerCell" and its partner the "TargetCell". And, of course, the TargetCell is on the TargetTabName which, you guessed correctly, is "OtherSheet". Why this complication? Why, while I know perfectly well that TargetTabName = "OtherSheet" don't I just change the cell TargetCell on Worksheets("OtherSheet"). The reason is that you may want to give another name to the OtherSheet and when you do you don't want to go digging in the code where to change the name. Just change the value of Const TargetTabName at the top of the code.
What change to make? The slot and port numbers are (I hope and trust) firmly related to each other. Slot #1 needs port 10 and slot #2 requires port 11. Therefore, Slot = Array(1, 2) and Port = Array(10, 11). If the slot is the first option then port must also be the first. And if you add a 3rd slot to the Slot array you must add a corresponding port number to the Port array.
And with that I will leave you to do the rest of the work. Below please find the event procedure.
Private Sub Worksheet_Change(ByVal Target As Range)
Const TriggerCell As String = "B2" ' on this sheet (change to suit)
Const TargetTabName As String = "OtherSheet" ' change to suit
Const TargetCell As String = "C3" ' on other sheet (change to suit)
Dim Slot As Variant ' list of slots
Dim Port As Variant ' matching list of ports
Dim i As Long ' index of Slot
If Target.Address(0, 0) = TriggerCell Then
Slot = Array(1, 2) ' modify / extend as needed
Port = Array(10, 11) ' match number of elements & their sequence
On Error Resume Next
i = WorksheetFunction.Match(Val(Target.Value), Slot, 0)
If Err Then i = 1 ' change to first choice by default
On Error GoTo 0
Worksheets(TargetTabName).Range(TargetCell).Value = Port(i - 1)
End If
End Sub
There are two further notes to make.
- I placed this formula near the TriggerCell on MySheet so that I can see what's going on on the OtherSheet:
=OtherSheet!C3
- When the TriggerCell is given a value that doesn't exist in the Slot array an error occurs which would crash the procedure. However, there is an error handler in the code which replaces the faulty value with "1". The code then following will react as if 1 was entered. The user will not be notified.
