Hi,all. Can anyone help with this? Let's say I have a Spin Button (Form control) in cell A1. I want it to determine the value of cell B1.
I right click the Button, choose Format Control, and under the Control tab, I can set the incremental change, min and max values, and the Cell Link. For the Cell Link I select cell B1. The reference in the "Cell Link" field defaults to an absoulte address format, ie. $B$1. It works fine.
But suppose I want to copy cells A1 and B1 and paste them down 100 rows, so that the Button in, for example, cell A2 determines the value in cell B2; the button in cell A3 determines the value in cell B3, etc.
When copying cells A1 and B1 when the default absolute reference for the Cell Link is used, of course every pasted button will control cell $B$1.
So when I created the Cell Link, I changed the default formatting of the cell reference from absolute to fully relative, ie. to no dollar signs, i.e. just B1.
However, even after doing this and trying to copy and paste cells A1 and B1 down many rows, all the Buttons *still* refer to cell $B$1. In other words, when I created the original link, typing in it as full relative, Excel outwardly seems to "accept" my command by not complaning when I click OK, but then it ignores that command, turning the reference back to fully absolute format. I can see this if I again right click and check.
I'd love to be able to paste the Buttons and their linked cells using fully relative references, as I have very many to make, and inidvidually editing the Cell Links of each will be quite time consuming.
Can anyone advise?
Bookmarks