IF statement that activates a macro

[Polly27]

Hi all,

I've got a question about an IF statement that should run a macro. In cell B2 there is a list of 1 tot 12 which resembles the months January till December. By default cell B2 = 1 and all files in the sheet are connected to the folder 'January' (e.g. if cell B2 = 1 then cell C1 = C:\January\[testfile.xls].!sheet1.C1).

If cell B2 = 2 then cell C1 should be connected to the folder February (e.g. C1 = C:\February\[testfile.xls].!sheet1.C1). And so on for March, April, etc.

I've made a macro which replaces all cells with "\January\" in the workpath by "\February\". This should be activated then if B2 = 2. But I guess and hope there's a smarter way of doing this.

Any help on this please?

Regards,

Polly

 

[crimson_b1ade]

right-click your sheet tab and select 'view code'. paste the below:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address = "$B$2" Then
    Range("C1").Formula = "='C:\" & MonthName(Target.Value) & "\[testfile.xls]Sheet1'!$C$1"
  Else
    'do nothing
End If
 
End Sub

 

[Polly27]

Thanks, I'll give it a try!