frank_m
04-19-2011, 11:07 PM
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
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