Named Range Question

[Pacman52]

Hi all, I have a Named Range called 'FestivalData' which is referencing a worksheet (WS) named 'Festivals' (sh02Festivals)

 =OFFSET(Festivals!$BO$2,1,,COUNTA(Festivals!$BP$2:$BP$9985)-1,63)

I've set this as the rowsource for a listbox.

When the userform is opened I need to have the a different WS eg 'sheet1' active but the resource keeps erroring unless I have the WS 'Festivals' active.

I was wondering is there a way to accomplish this by adapting the named range perhaps or does anyone know if this is actually possible or if the error is being caused by something else?

Thanks Paul

 

[ExcelToDAX]

can you share the VBA code?

 

[Pacman52]

Hi thanks so much for your reply. I was just literally looking at some of the subs as your message came through and found I'd placed a line activating the worksheet in the wrong place so moved it further down the sub and got it to work without any changes to the Named Range so all sorted - for now !