Prevent 1004 Error For Select Method

[BobTheBuilde]

Once I run the code that is at the very bottom of this post, I get the an 1004 Run-time erro ("Select Method of <class> class failed") when I run the code directly below:

ActiveSheet.Spinners.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width * 0.33, ActiveCell.Height).Select

This is for any kind (class) of object (whether it be an OLEObject, Textbox, Spinner, etc..).

As promised above, the following code (at the 'very bottom' of this post) is the code that, once run, causes all code at the top of this post to results in the aforementioned 1004 error). If anyone can help explain why running the following code results in this problem it would be very much appreciated! (please ignore the poor coding practice, I'm not worried about those, just the aforementioned problem! - I know I'm a newb)


Sub A_A_Utility_ReportNumberChange()

Application.ScreenUpdating = False
Call A_A_Utility_UnlockAllSheets
ActiveWorkbook.Unprotect

Dim N As Long
Dim StringHolder As String
Dim M As Long

Dim DateHolder As String
Dim TimeHolder As String

Dim Entry As Boolean
Dim Key As Boolean
Dim AdminPassword As Boolean

Dim NumberItems As Long

Entry = False
NumberItems = Sheets("DataHolder").Range("E10").Value
'this is the current number of lines (of editing) in the "Revisions" sheet
N = Sheets("DataHolder").Range("I3").Value
Sheets("HOME").Select
'this section of code deals with keeping the right format and using
Range("O1").Select
'excel's ability to take the current time and date from the computer to use in the Revisions sheet
Selection.NumberFormat = "m/d/yyyy"
Selection.FormulaR1C1 = "=TODAY()"
Sheets("HOME").Select
Range("P1").Select
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Selection.FormulaR1C1 = "=NOW()"
DateHolder = Sheets("HOME").Range("O1").Value
TimeHolder = Sheets("HOME").Range("P1").Value
Sheets("HOME").Range("O1").FormulaR1C1 = ""
Sheets("HOME").Range("P1").FormulaR1C1 = ""
Name = Sheets("HOME").Range("G1").FormulaR1C1

Sheets("Revisions").Select
Call A_A_Utility_HistoryFormating_REVISIONS_RevisionsSeparate

For M = 1 To NumberItems

Sheets("HOME").Select

'====Check if Protection Range Exists, if so remove=====
Dim s As String
On Error Resume Next
s = ActiveSheet.OLEObject("TextBox" & M + 3)

'====If the TextBox does exist, then do the following..==
If Err.Number = 0 Then

'If Entry = False Then 'this makes sure this only happens once - the first time
' THIS CAN BE USED IF NEEDED
'End If

Entry = True 'not useful unless the above statement is also utilized

Sheets("Revisions").Range("A" & N + 4).FormulaR1C1 = DateHolder
Sheets("Revisions").Range("B" & N + 4).FormulaR1C1 = Name
Sheets("Revisions").Range("C" & N + 4).FormulaR1C1 = TimeHolder
Sheets("Revisions").Range("C" & N + 4).Select
'setting the cell with "TimeHolder" to display a time instead of a incoherent 6-figure number
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Sheets("Revisions").Range("D" & N + 4).FormulaR1C1 = "Number Only"
Sheets("Revisions").Range("D" & N + 5).FormulaR1C1 = "Number Only"
Sheets("Revisions").Range("E" & N + 4).FormulaR1C1 = "Before"
Sheets("Revisions").Range("E" & N + 5).FormulaR1C1 = "After"

Sheets("Revisions").Range("F" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("D" & M + 3).FormulaR1C1
Sheets("Revisions").Range("G" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("E" & M + 3).FormulaR1C1
Sheets("Revisions").Range("H" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("F" & M + 3).FormulaR1C1
Sheets("Revisions").Range("I" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("G" & M + 3).FormulaR1C1
Sheets("Revisions").Range("J" & N + 4).FormulaR1C1 = Sheets("DataHolder").Range("M" & M + 3).Value
Sheets("Revisions").Range("K" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("I" & M + 3).FormulaR1C1
Sheets("Revisions").Range("L" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("J" & M + 3).FormulaR1C1
Sheets("Revisions").Range("M" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("K" & M + 3).FormulaR1C1
Sheets("Revisions").Range("N" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("L" & M + 3).FormulaR1C1
Sheets("Revisions").Range("O" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("M" & M + 3).FormulaR1C1
Sheets("Revisions").Range("P" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("N" & M + 3).FormulaR1C1
Sheets("Revisions").Range("Q" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("O" & M + 3).FormulaR1C1
Sheets("Revisions").Range("R" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("P" & M + 3).FormulaR1C1
Sheets("Revisions").Range("S" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("Q" & M + 3).FormulaR1C1
Sheets("Revisions").Range("T" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("R" & M + 3).FormulaR1C1
Sheets("Revisions").Range("U" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("S" & M + 3).FormulaR1C1
Sheets("Revisions").Range("V" & N + 4).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("T" & M + 3).FormulaR1C1

Sheets("Revisions").Range("F" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("D" & M + 3).FormulaR1C1
Sheets("Revisions").Range("G" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("E" & M + 3).FormulaR1C1
Sheets("Revisions").Range("H" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("F" & M + 3).FormulaR1C1
Sheets("Revisions").Range("I" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("G" & M + 3).FormulaR1C1
Sheets("Revisions").Range("J" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("H" & M + 3).FormulaR1C1
Sheets("Revisions").Range("K" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("I" & M + 3).FormulaR1C1
Sheets("Revisions").Range("L" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("J" & M + 3).FormulaR1C1
Sheets("Revisions").Range("M" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("K" & M + 3).FormulaR1C1
Sheets("Revisions").Range("N" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("L" & M + 3).FormulaR1C1
Sheets("Revisions").Range("O" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("M" & M + 3).FormulaR1C1
Sheets("Revisions").Range("P" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("N" & M + 3).FormulaR1C1
Sheets("Revisions").Range("Q" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("O" & M + 3).FormulaR1C1
Sheets("Revisions").Range("R" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("P" & M + 3).FormulaR1C1
Sheets("Revisions").Range("S" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("Q" & M + 3).FormulaR1C1
Sheets("Revisions").Range("T" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).FormulaR1C1). _
Range("R" & M + 3).FormulaR1C1
Sheets("Revisions").Range("U" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).Value). _
Range("S" & M + 3).FormulaR1C1
Sheets("Revisions").Range("V" & N + 5).FormulaR1C1 = Sheets(Sheets("HOME").Range("D" & M + 3).Value). _
Range("T" & M + 3).FormulaR1C1
Sheets("Revisions").Select
Call A_A_Utility_HistoryFormating_ENTRY_RevisionsSeparate
Range(Cells(N + 4, 1), Cells(N + 5, 22)).Select 'these two lines are the coloring
Selection.Interior.ColorIndex = 36
N = N + 2
End If
Next M

Call A_A_Utility_OrganizeCopyHolder
Sheets("Revisions").Select
Call A_A_Utility_HistoryFormating_REVISIONS_RevisionsSeparate

Sheets("HOME").Range("C1").Select

End Sub

[Simon Lloyd]

Cross posted here (http://www.ozgrid.com/forum/showthread.php?t=95804) please read the link in my signature with regards to cross posting!

[BobTheBuilde]

well I would post the link, but I can't because my number of posts are less than 5 - so not my fault here

[Simon Lloyd]

it's dead simple! type the url!

[BobTheBuilde]

Does anyone have any other, more helpful, comments - I'm really stuck here..

[mikerickson]

I couldn't find Spinners in the Object Browser, I think MS has de-graded those classes in favor of the ActiveX controls.

Have you tried code like this

Dim newControl As Shape
Set newControl = ActiveSheet.Shapes.AddFormControl(Type:=xlSpinner, Left:=100, Top:=100, Width:=75, Height:=20)

[BobTheBuilde]

Well mikerickson - I'm not sure that this is the problem - but I'm willing to bet you know more about VBA than me (of course you do! I'm new) so I'm going to try your suggestion (creating and refering to the spinnres as an ActiveX control).

That said, thanks for being the only one to even attempt to help!
And, I'll reply on this forum if it works.

[lucas]

Hey Bob, I don't believe I would help you at all after reading your posts. We like to be friendly here but will not tolerate rudeness.

Remember you are the one here seeking "Free" help from people who are generously contributing their time to help you. The least you can do is be civil and read the faq.

There is a very good reason Simon brought up the issue of cross-posting. It's considered to be one of the few things you can do here to cause yourself problems. I advise that you find out why before posting any more in this forum.

[BobTheBuilde]

With all due respect, lucas, I definately did not intend to offend anyone or be rude in any way. If you read my other posts, I don't believe you would find them particularly rude at all (other than in regards to Simon, of course). I've always thanked others for trying to help, even if they were not able to.

Once alerted by Simon of the cross posting issue (which, believe me, I did not know about - I'm new) I prompted update the links to the posts I had made (I actually only made two, here and at ozgrid) EXCEPT for here. Unlike Simon had claimed, I was not able simply type in the url to make the link. So you can take my remark to him as simply a matter-as-fact statement.

I apologize, I meant no harm.

[lucas]

Good news as far as I'm concerned. Let's put it behind us and start fresh.

Please, lets keep it friendly. No one here is on anyone's payroll. Treat each other with respect. That's not too much to ask.

[BobTheBuilde]

btw, mikerickson - as promised I am back with an update as to the progress (as far as the implementation of your suggestion)

it turns out (by trail and error I found this out) - that the error has to do with the number of objects I have (I think). What I did was copy and paste most everything(which, believe me, was a lot) and started a new with a new workbook.

So everything works now, but it hadn't before.

The only things that have changed are:
-Far fewer sheets (far fewer objects)
-far smaller file (about 1 mb from 7 mb)
-the default number given to an object after creation (i.e. CheckBox 45839) is much much smaller (around 1000 at this point compared to around 65000) I attribute this to this number being the "index" (?) - and that a new workbook starts with 1?

I guess I should have given this to begin with - describe the original workbook:
Lots of sheets, a bit of data in each - but a whole lot of buttons, checkboxes, and whatnot - all of which stopped working randomly.

So, in hind site - does anyone know what can cause a 1004 runtime error due to the number of objects in a workbook (or maybe something else, and I'm completely off base)?

[mikerickson]

Have you considered replacing all those buttons with a modeless userform? or a pop-up menu?
If many of the controls do essentialy the same thing, that would be a memory saving technique.