Solved

Shape .top value out of range

Posted on 2014-09-18
13
254 Views
Last Modified: 2014-09-19
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
0
Comment
Question by:Terrygordon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40330290
Try:

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

Open in new window

0
 

Author Comment

by:Terrygordon
ID: 40330415
Sorry James - still the same error message.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40330492
Can you attach your workbook?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Terrygordon
ID: 40330549
Unfortunately - it is massive and full of confidential data.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40330725
Is the sheet protected?
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40330965
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
 

Author Comment

by:Terrygordon
ID: 40331131
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
 

Author Comment

by:Terrygordon
ID: 40331150
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40331194
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
 
LVL 47

Accepted Solution

by:
Martin Liss earned 250 total points
ID: 40331463
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40331748
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
 

Author Comment

by:Terrygordon
ID: 40332120
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40333021
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question