Error 400 Appears and Then Goes Away On Its Own

valenteen

New Member
Joined
May 12, 2011
Messages
3
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! : )
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
May 12, 2011
Messages
3
Damon,

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

Valenteen
 

valenteen

New Member
Joined
May 12, 2011
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,118
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top