Solved: Permission Denied renaming Textbox if name in the thousand's in 2003 - ok in 2007

[frank_m]

Hello,

I'm having an issue with Excel 2003, where I'm getting a Permission Denied Error# 70 while renaming Textboxes via code, if Excels next available name is in the thousand's.

It works fine in Excel 2007

Attached is a sample workbook that throws the error both in the sheet selection change event and the manually operated test macro.

Please see next post for attached workbook there where the names used are less than 1,000, thereby causing no error with that sample.

Is there some way I can flush out the next name memory? as demonstrated by the sample attachments those names are saved somewhere hidden as workbook specific.. Or correct me if I'm wrong about that. It seems so because when I save the workbook by a new name it still assigns the next name based on the next increment of names previously used with other copies even though the textboxes with those names no longer exist. - Where as if build a new workbook from scratch the next available names are much smaller.
Sub NoError_ExcelNamesTheTextBox()

'This Macro is Called manually for testing purposes
'In this example Excel assigns the Textbox Name
'No errors with this code even if the name is in the thousands

'On Error Resume Next

Dim iLeft As Integer, iTop As Integer, iWidth As Integer, iHeight As Integer
Dim shp As Shape

ActiveSheet.TextBoxes.Delete

iLeft = ActiveCell.Left
iTop = ActiveCell.Top + ActiveCell.Height + 1
iWidth = ActiveCell.Width
iHeight = 0.75

Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)

With shp
.Locked = False
''''''.Name = "TxtBxColumn" & ActiveCell.Column
.Line.ForeColor.SchemeColor = 10
End With

End Sub

Sub ErrorThrown_CodeReNamesTheTextBox()

'This Macro is Called manualy for testing purposes
'In this example the Textbox is renamed while it's created
'there is a permission denied Error#70 IF next available Textbox name in Excel is in the thousands.
'for demonstration of no error see sample named ThisIsOk_TextBoxName.xls (http://www.vbaexpress.com/forum/attachment.php?attachmentid=5912&d=1303279683) attached in next post.

'On Error Resume Next

Dim iLeft As Integer, iTop As Integer, iWidth As Integer, iHeight As Integer
Dim shp As Shape

ActiveSheet.TextBoxes.Delete

iLeft = ActiveCell.Left
iTop = ActiveCell.Top + ActiveCell.Height + 1
iWidth = ActiveCell.Width
iHeight = 0.75

Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)

With shp
.Locked = False
.Name = "TxtBxColumn" & ActiveCell.Column
.Line.ForeColor.SchemeColor = 10
End With

End Sub

Sub CreateRowCellsTextBoxes()

'This Macro is Called from the Selection Change Event
'Procedure renames the Textboxes as they are being created
'In sample workbook attached in this post there is an error
'a permission denied Error#70 because next available Textbox name in Excel is in the thousands.
'for demonstration of no error see sample named ThisIsOk_TextBoxName.xls (http://www.vbaexpress.com/forum/attachment.php?attachmentid=5912&d=1303279683) attached in next post.

'On Error Resume Next

Dim TheRow As Range, cll As Range
Dim iLeft As Integer, iTop As Integer, iWidth As Integer, iHeight As Integer
Dim shp As Shape

Application.ScreenUpdating = False

ActiveSheet.TextBoxes.Delete

Set TheRow = ActiveCell.EntireRow.Resize(, 30)

If Not TheRow Is Nothing Then
For Each cll In TheRow.Cells

iTop = cll.Top + cll.Height + 1
iHeight = 0.75
iLeft = cll.Left
iWidth = cll.Width


Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)

With shp
.Locked = False
.Name = "TxtBxColumn" & cll.Column
.Line.ForeColor.SchemeColor = 10
'.OnAction = "MacroColumn" & cll.Column
End With

Next cll
End If

Application.ScreenUpdating = True

End Sub Option Explicit

Private prevRow As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row <> prevRow Then
Call CreateRowCellsTextBoxes
End If

prevRow = Target.Row

End Sub

[frank_m]

Sample workbook attached to demonstrate where there is no error because Excels next available textbox name is less than 1,000.

Thanks

[GTO]

Greetings Frank,

In the wb you posted at #1, there were about 30 or 31 textboxes above row 1. These were not being deleted by the code (I didn't really figure out why).

Thus, when the newly added textbox was (attempted to) renamed, an error occured as the name was already assigned to another pre-existing box.

Hope that helps,

Mark

[frank_m]

Awesome - that certainly explains the error and gets me sniffing down the right trail.

Apparently ActiveSheet.TextBoxes.Delete doesn't work in every scenario involving text box location and/or size.

After further investigation:
Apparently the key here was the height of those textboxes you discovered above row 1
They all had zero height' (issue of ActiveSheet.TextBoxes.Delete missing zero height shapes seems to have been corrected in Excel 2007 as when I test the same file, those are deleted)

Fortunately looping through the shapes is able to locate and delete those rebellious strays.
Public Sub LoopShapes()
Dim sh As Shape

For Each sh In ActiveSheet.Shapes
MsgBox "Shape Type is: " & sh.Type & " Name is: " & sh.Name & " Height is: " & sh.Height
If sh.Type = 17 Then'- being that text boxes are the only shape type that I'll ever be using
sh.Delete '-------- I'm going to remove type check to be sure same name attempts don't happen
End If

Next sh

End Sub Thanks a bunch Mark, for once again sharing your skill.

[Bob Phillips]

Are you using moveable textboxes to draw a line on the selected row?

[frank_m]

HI Bob,

Yes I am. How might I make them not moveable? (By moveable do you mean re size with cells?
- If that is what you mean, I do know how to fix that -- .Placement = xlMoveAndSize 'instead of' xlFreeFloating
Edit:(corrected typo's and added more comments)
The reason I don't like using one long shape to underline the row is that with one shape for each cell i have a macro assigned
so if the user accidentally clicks the shape the macro selects the relevant cell using the shapes top left property.

I am using textboxes rather than a line shape, because I may later want to store cell values for use with an undo function.
I had been identifying the selected row with row borders but that was causing repaint issues in certain situations, including when I use cell validation.

Your thoughts?

Thanks,

Frank

[Bob Phillips]

Well, I certainly wouldn't use tetxboxes, it seems overly-complex and problem prone.

If you just want to highlight the selected row, why not just colour it?

[frank_m]

Because that would overwrite colored cells and my users are addicted to coloring cells. (a habit I do plan to break them of, but can't do so quickly and/or easiliy)

[Bob Phillips]

Then use CF, that would revert back when the select another row.

[frank_m]

Well, I'm open to experimenting with that. Do you have code you can share with me to use conditional formatting to color the selected row?
If you don't have it handy, I'll go ahead and do a google search for that as I imagine that approach is likely widely used.

Thanks

[Bob Phillips]

Select all of the cells in the target area and add CF with a formula of

=ROW()=CELL("row")

and the colour of your choice.

Then add event code of

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

[frank_m]

HI Bob,

That works great if I don't include my selection change event code, (which is extensive and runs fast and well until I add any conditional formatting.

In fact the row highlight using CF works well with my event code in intact, until i filter rows, then the row coloring becomes very slow to repaint.

'I am using Application.ScreenUpdating = True at all times during all of the selection change event's