VBA query

[CVN55]

Hi - someone on this forum wrote below for me and it works perfectly, but I need the "M3" in line 3 to be a cell in another workbook ie. Sheet 2 cell C29, how can I change this please?

Thank you,
Catherine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nStr As String
If Target.Address(0, 0) = "M3" Then
Select Case Target.Value
Case "Portfolio", "Portfolio status not yet known": nStr = "Research, Service Support, Excess Treatment, Standard Treatment"
Case "NonPortfolio": nStr = "Research"
End Select
With Range("K8:K14, K21:K26, K34:K40, K48:K51, K60:K64, K74:K77").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=nStr
End With
End If
End Sub

 

[frank_AL]

Catherine,

You say "but I need the "M3" in line 3 to be a cell in another workbook ie. Sheet 2 cell C29". Are you really meaning another workbook/file or just another worksheet in the same workbook. Either way, can you provide the name of the other workbook?

 

[CVN55]

Sorry, I meant another worksheet, in the same workbook!

 

[MARK858]

@CVN55, if you are using it as a Worksheet_Change event then you need to put the code in the Sheet 2 module and change

If Target.Address(0, 0) = "M3" Then

to

If Target.Address(0, 0) = "C29" Then

and

With Range("K8:K14, K21:K26, K34:K40, K48:K51, K60:K64, K74:K77").Validation

to

With Sheets("Sheet 1").Range("K8:K14, K21:K26, K34:K40, K48:K51, K60:K64, K74:K77").Validation

Please note that I have put the space in Sheet 1 as you have written Sheet 2 with the space but the default is Sheet2 without the space and so change as necessary.

 

[CVN55]

So that does mean I will have 1 code in each workbook, each referring to the other workbook? Thank you...

 

[MARK858]

Why are you talking WorkBooks again? If mean WorkSheets then yes you are using WorkSheet_Change code so it needs to be in each sheet.

You are probably better off by the sound of it using ThisWorkbook event code and in particular looking at Workbook_SheetChange.