Error 400 Appears and Then Goes Away On Its Own

[valenteen]

Hi all!

I'm really hoping that someone will be able to help me out with my dilemma. I've built a spreadsheet that draws a cross-section and then calculates a bunch of properties based on it's dimensions. In a nutshell, you enter in the dimensions, select the cross-section type (three to choose from in a drop-down menu: U-Beam, L-Beam, and T-Section), and press the "Draw Shape" button on the spreadsheet. This results in the macro drawing the appropriate cross-section.

Here's where it gets weird though...If I open my spreadsheet and try to run the program I'll get an Error 400 message (After inserting an ErrorTrap into the code, I get a message saying "Application Defined or Object-Defined Error"). BUT if I open my spreadsheet, wait for about 20 minutes, and then try to run the program it works perfectly! Furthermore, the same thing happens all over again if I try to change the shape type in the drop-down menu and then run the program (Get Error 400 at first, wait 20 minutes, then program works fine).

I have absolutely no idea what's going on. I've pasted my code below:

Sub DrawBeam()

Dim MyShape As Shape
Dim b(20) As Double
Dim h(20) As Double
Dim x_i(20) As Double
Dim y_i(20) As Double


For Tower = 1 To 20
b(Tower) = Range("B" & Tower + 41)
Range("C" & Tower + 41).Select
h(Tower) = Range("C" & Tower + 41)
x_i(Tower) = Range("E" & Tower + 41)
y_i(Tower) = Range("F" & Tower + 41)
Next Tower

'Select range with objects drawn
ActiveSheet.Range("N28:AC100").Select
'Delete shapes within selection range,
For Each MyShape In ActiveSheet.Shapes

If Intersect(MyShape.TopLeftCell, _
Selection) Is Nothing Then
'do nothing--will not delete button outside of selection area
Else
MyShape.Delete
End If
Next MyShape
' Loop for drawing the regions
For Tower = 1 To 20
If b(Tower) = 0 Then
' Do nothing
Else
' z = 100
railanchorx = 900 + x_i(Tower) - b(Tower) / 2 'from the right side of the screen
railanchory = 700 - y_i(Tower) - h(Tower) / 2 'from the top of the screen
' Draw the shape
ActiveSheet.Shapes.AddShape(msoShapeRectangle, railanchorx, railanchory, b(Tower), h(Tower)). _
Select

End If
Next Tower
Range("M27").Select
End Sub


I REALLY hope someone will be able to tell me what's going on!

Thanks so much! : )

 

[Damon Ostrander]

Hi Valenteen,

This is just a hunch, really. If the buttons your code deletes are ActiveX buttons (i.e., from the Controls toolbox) I recommend you try switching to Forms buttons (from the Forms control toolbox). I suspect that deleting ActiveX buttons without dealing with the Click event code associated with them may be creating your problem.

Damon

 

[valenteen]

Damon,

Thanks for responding! I'm already using a form control. Any other ideas?

Valenteen

 

[valenteen]

I fixed it! I can't believe how hard I've been making it on myself. Here's what I did and it works great:

'Select range with objects drawn
ActiveSheet.Range("N28:AY156").Select
'Delete everything within selection range
Selection.Delete

As long as nothing that I don't want deleted falls within the selection range, than it'll work fine.