Solved

Shape .top value out of range

Posted on 2014-09-18
13
241 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
  • 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 45

Expert Comment

by:Martin Liss
ID: 40330492
Can you attach your workbook?
0
 

Author Comment

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

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 45

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 45

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now