If you want to change
=+SUM(Sheet1!A1,Sheet1!A3) you just need to replace all
,Sheet1!. So your code needs to be
ObjData.SetText "+sum('" & Selection.Parent.Name & "'!" & Replace$(Selection.Address(0, 0), ",", ",'" & Selection.Parent.Name & "'!") & ")"
Actually I thought
Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True)
should also return
Sheet1!A1,Sheet1!A3 due to
External:=True but obviously ther is a bug and it returns
Sheet1!A1,A3 which is obviously not what one would expect (as it doesn’t make any sense that the
address method returns only the first one as external).
CLICK HERE to find out more related problems solutions.