BobTheBuilde
06-29-2008, 09:38 AM
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
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