I have a worksheet (Excel 2007) that displays the contents of the selected cell in a rectangle shape
that is positioned (30 above and 50 to the left of the target cell) based on the left and top values of the target cell. The code runs in the SelectionChange event of the sheet and works perfectly.
The code for this part is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("d5:am375"), Target) Is Nothing Then
Range("D402").Value = Target.Value
tp = Target.Top
rt = Target.Left
coursetop = tp - 30
courseleft = rt + 50
.Top = coursetop
.Left = courseleft
As I said, it works absolutely fine (provided I disable the code below).
However, the user has the option to toggle this cell display on or off using a button. When they enable cell display, I want it to identify the cell they are currently in and then display the rectangle with the cell contents (again 30 above and 50 to the left of the currently selected cell).
The current code is:
'toggle cell display off if flagged as 1
If Range("BV3").Value = 1 Then
.Shapes("Course1").Visible = False
.Range("BV3").Value = 0
.Range("D401").Value = "Enable Cell Display"
'toggle cell display on and display current cell value in rectangle
ElseIf Range("BV3").Value = 0 Then
celltext = ActiveCell.Value
cltop = ActiveCell.Top
clleft = ActiveCell.Left
top2 = cltop - 30
left2 = clleft + 50
.Shapes("Course1").Visible = True
.Range("D402").Value = celltext
.Range("BV3").Value = 1
.Visible = True
.Top = top2
.Left = left2
Sheets("TC-Scheduler").Range("D401").Value = "Disable Cell Display"
I am getting an error saying "The specified value is out of range" on the .Top = cltop - 30 line.
It works fine if I disable the top and left settings (it just displays the rectangle on a different area of the sheet). I've tried every variation of referencing the shape that I can think of (including dispensing with 'with' and using complete references) but I'm pulling my hair out trying to figure out why this doesn't work. I'm also pretty sure that the values of top2 and left2 are valid (the debugger values are exactly what they should be and both values are positive).
Any help greatly appreciated.