Excel is doing some rather bizarre things to my control buttons, and I was hoping someone might be able to help me figure out what's going on.
First, a little background: I've put together a spreadsheet to manage records that I receive annually -- in other words, each year there's a new set of figures in a number of categories, and I want to add those figures to the sheet and apply some standard calculations. I don't think the details matter, so I'll omit them in the interest of (relative) simplicity. For current purposes, what's relevant is that I've put several control buttons on the sheet, and one of them is linked to a VBA procedure called AddYear. (I'm using Excel 2011 on a Mac, so ActiveX isn't an option -- the buttons are simple form controls that just call a procedure when clicked.)
AddYear defines an Integer variable called RecentAnniv, where I store the number of the rightmost non-blank data-entry column. I use RecentAnniv to define a Range object called rTakeCopyFrom, which includes the relevant rows over the columns from RecentAnniv - 2 to RecentAnniv + 2, covering the input fields and their associated labels and formulas. I Set rTarget = rTakeCopyFrom.Offset(0, 6) (leaving space for my five columns of content plus a blank column to make it easier to read), and then xlPasteSpecial the formulas and formats from rTakeCopyFrom to rTarget. It's not the most elegant solution in the world, but I'm the only one who uses it and it works for what I need it to do.
Of course, the more years I add, the wider the spreadsheet spreads, expanding by 6 columns each year. The buttons are in the first couple of columns, and it soon becomes inconvenient to scroll all the way to the left to access them -- normally, I'll be looking at the most recent data when I want to add new information. So, in the AddYear procedure, I added code to move the buttons to the same section of the sheet as the rTarget area -- specifically, to column RecentAnniv + 8, the rightmost column in rTarget.
As I write this, it occurs to me that I could avoid the entire problem I'm about to describe by the simple expedient of keeping the first columns in a frozen pane, but I didn't think of that before, and now that I've encountered the problem it would be deeply unsatisfying to give up without figuring out what's happening. The code I'm using instead is the following:
(ConvertToLetter , as you might expect, is a function that converts the integer column number into the corresponding alphanumeric column identifier.)
- Sub MoveButtons(MoveToHere As String)
- Dim strNewButtonReference As String
- Dim sButtonWidth As Single
- Dim sButtonHeight As Single
- Dim i As Integer
- sButtonWidth = 96
- sButtonHeight = 16
- With ActiveSheet
- For i = 1 To .Shapes.Count
- strNewButtonReference = MoveToHere & (i * 3 + 1)
- .Shapes(i).Left = Range(strNewButtonReference).Left
- .Shapes(i).Top = Range(strNewButtonReference).Top
- .Shapes(i).Width = sButtonWidth
- .Shapes(i).Height = sButtonHeight
- Next i
- End With
- End Sub
This works fine, the first time. But when I click on the relocated button, bizarre things start to happen. The buttons get taller or shorter, double in width, move to rows they're not supposed to be in. (The code specifying height and width, which I added in an attempt to fix the problem, has no apparent effect.) Eventually the buttons become something like 32 columns wide, obscuring lots of the information in the spreadsheet.
Anyone have a clue what's going on, and how to fix it?