Shape .top value out of range

Hi All

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
       With ActiveSheet.Shapes("Course1")
        .Top = coursetop
        .Left = courseleft
    End With
        End If
End Sub

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:

Sub CellDisplay()
'toggle cell display off if flagged as 1
If Range("BV3").Value = 1 Then  
With ActiveSheet
    .Shapes("Course1").Visible = False
    .Range("BV3").Value = 0
    .Range("D401").Value = "Enable Cell Display"
    End With

'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
With ActiveSheet
    .Shapes("Course1").Visible = True
    .Range("D402").Value = celltext
    .Range("BV3").Value = 1
    End With
With ActiveSheet.Shapes("Course1")
    .Visible = True
    .Top = top2
    .Left = left2
End With

Sheets("TC-Scheduler").Range("D401").Value = "Disable Cell Display"
End If
End Sub

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.

Regards

Terry
TerrygordonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
Try:

    .Top = cdbl(top2)
    .Left = cdbl(left2)

Open in new window

0
TerrygordonAuthor Commented:
Sorry James - still the same error message.
0
Martin LissOlder than dirtCommented:
Can you attach your workbook?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

TerrygordonAuthor Commented:
Unfortunately - it is massive and full of confidential data.
0
Martin LissOlder than dirtCommented:
Is the sheet protected?
0
Glenn RayExcel VBA DeveloperCommented:
Regardless of the code, I'd insert a test for a negative value for either coursetop or top2 to set to zero if the formula would result in a negative value.  If the row heights were changed somewhere down the line, that might cause this.

Example (coursetop)
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
         If coursetop < 0 then coursetop = 1
         courseleft = rt + 50
        With ActiveSheet.Shapes("Course1")
         .Top = coursetop
         .Left = courseleft
     End With
         End If
 End Sub

Open in new window


Regards,
-Glenn
0
TerrygordonAuthor Commented:
The sheet is password protected, but protection is disabled at the start of the sub and re-enabled when it has run. Again this works fine in the selectionchange event. I removed these lines from the copied code before I posted it. Also, none of the cells in the interactive part of the sheet are locked.

I would have thought a negative value would have shown when you hover over the variable in debug (I've also had it display in a msgbox and that was positive).

I am at home now but will try Glenn's code tomorrow and let you know what happens.

Regards

Terry
0
TerrygordonAuthor Commented:
P.s. Glenn - the problem is not occurring with the first block of code (the SelectionChange event) - only in the second sub, which runs from a button. The button is a shape with the sub assigned as a macro, rather than a commandbutton (purely for cosmetic reasons) so, I don't know if that makes a difference?
0
Glenn RayExcel VBA DeveloperCommented:
If top2 is showing as a positive value in your debug process (i.e., floating over variable after it is assigned), then my suggestion doesn't have an effect.  In any case, here's what I'd change for the subroutine (line 16 added):

Sub CellDisplay()
 'toggle cell display off if flagged as 1
 If Range("BV3").Value = 1 Then   
With ActiveSheet
     .Shapes("Course1").Visible = False
     .Range("BV3").Value = 0
     .Range("D401").Value = "Enable Cell Display"
     End With

 '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
     if top2 < 0 then top2 = 1
     left2 = clleft + 50
 With ActiveSheet
     .Shapes("Course1").Visible = True
     .Range("D402").Value = celltext
     .Range("BV3").Value = 1
     End With
 With ActiveSheet.Shapes("Course1")
     .Visible = True
     .Top = top2
     .Left = left2
 End With

 Sheets("TC-Scheduler").Range("D401").Value = "Disable Cell Display"
 End If
 End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
The sheet is password protected, but protection is disabled at the start of the sub...
Are you sure? Could you be using the wrong password? (I don't know if that would throw an error)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Haris DulicCommented:
Hello,

i  tried to recreate the error you got , so i setup same settings as described and the all of the code posted runs without any error. Even negative value for the top is not the issue since the top cant be less then 0.

IMHO, i thing that the code is ok and that you have issue on the sheet (protected or something).

If you can create new excel (without any sensitive data) and try to setup same scenario to confirm that you still got the error and then maybe you can post the new excel so we can take a look.

BR
0
TerrygordonAuthor Commented:
Hi All

Humble pie time - I just realised that I must have accidentally deleted the line that unprotects the sheet when I was playing around with some of the suggestions. Now that it is back, everything works fine.

I am spreading the points between Martin (who identified the issue) and Glenn (for all the work he put in).

Still don't know what caused the original issue (when the unprotect line was in place) but it's all working fine now.

Thanks for all your help.

Regards

Terry
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.