The error was Runtime error 424
Object not defined
The the yellow highlight is on UserForm1.Show which just confuses me because i tested it before i started adding data to the worksheet itself and it worked, then i typed in some text into cells, saved and tried to run it again then it stopped working.
I noticed sometimes it happens if i save, then close the workbook. On reopening it will give me the Object not defined when i click the button. When i send it to others it seems to be 50% chance the button wont work either
There user form has alot of checkboxes. The way its designed is that the user selects checkboxes and then clicks Generate which is sub command button 1
The macro goes through the first sheet takes a value and searches the remaining sheets to paste any info that has the same value.
The code is really long so ill try and paste chunks of important code.
The User form has ShowModal = True
Currently Using Excel 2003.
The user form gets unloaded when the user hits the Second Comand button which does some minor formatting to the last sheet, then has the command Unload Me so i believe its being unloaded
Code at the top of UserForm1 from the top to the first End IF:
Code:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub CommandButton1_Click() 'This is the Generate button.
Application.ScreenUpdating = False 'All this line does is set the sheet up so it doesn't flicker oddly while the TS is being genereated.
If Not CheckBox1 And Not CheckBox2 And Not CheckBox3 And Not CheckBox4 And Not CheckBox5 And Not CheckBox6 And Not CheckBox7 And Not CheckBox8 And Not CheckBox9 And Not CheckBox10 And Not CheckBox11 And Not CheckBox12 And Not CheckBox13 And Not CheckBox14 And Not CheckBox15 And Not CheckBox16 And Not CheckBox17 And Not CheckBox18 And Not CheckBox19 And Not CheckBox20 And Not CheckBox21 And Not CheckBox22 And Not CheckBox23 And Not CheckBox24 And Not CheckBox25 And Not CheckBox26 And Not CheckBox27 And Not CheckBox28 And Not CheckBox29 And Not CheckBox30 And Not CheckBox31 And Not CheckBox32 And Not CheckBox33 And Not CheckBox34 And Not CheckBox35 And Not CheckBox36 Then
MsgBox "Please select a Checklist to generate.", vbOKOnly, "Nothing Selected"
Else: MsgBox "Due to the large amount of information on this sheet, This will take some time. Please wait...", vbInformation, "Generating Test Script" 'I put this in because with out it, the Form looks like it freezes during TS Gen.
End If
If CheckBox1 Then 'Checkboxes are either True or False. This is short for "If CheckBox1 = True". It would yield the same result.
Sheets(1).Activate 'I want to know what to look for, so let's activate the ID Key Sheet.
shIndex = ActiveSheet.Index
Range("A4").Select 'This just selects the first cell in this sheet.
Cells.Find(What:="360 SP Only", After:=[A1], LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
'The line above is where the magic begins. This steps through each cell to find each identifier for the specified TS.
Do 'This is the loop where the sort begins.
'What happens here is after finding the identifier specified above, it moves one cell down and grabs that value.
ActiveCell.Offset(1, 0).Select
Call TSGen(ActiveCell.Value) 'TSGen is the procedure that actually steps through the MF - A Bugs SP sheet to find the specfied ID Key. TSGen requires a value to be sent to it, so this sends the IDKey through.
Sheets(1).Activate 'Don't forget to reactivate the ID Key sheet, or the generated TS won't have all the data on it.
Loop Until IsEmpty(ActiveCell) = True ' This line will check to see if the ActiveCell on Sheet(1) is empty. If it is, then it will break the Loop.
' This is the code to tell the user that the TS has been generated successfully.
'Call FormReset ' All this does is reset the Checkboxes to Unchecked.
Application.StatusBar = "We are done!"
Application.ScreenUpdating = False ' Turns Screen Updating back on. Flicker extreme! :)
End If
Sample code for individual checkboxes:
Code:
If CheckBox2 Then
Sheets(1).Activate
Range("A4").Select
Cells.Find(What:="360 SP/MP", After:=[A1], LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
Do
ActiveCell.Offset(1, 0).Select
Call TSGen(ActiveCell.Value)
Sheets(1).Activate
Loop Until IsEmpty(ActiveCell) = True
'Call FormReset
Application.ScreenUpdating = False
End If
The meat of the code is below including some bits for loading bar:
Code:
Public Sub TSGen(IDKey As String)
Dim sngPercent As Single
intMax = 947
Sheets(2).Activate
Range("A12").Activate
shIndex = 2
If CheckBox37 Then
shIndex2 = Sheets.Count - 1
intMax = 1710
Else: shIndex2 = Sheets.Count - 3
End If
Do Until shIndex = shIndex2
sngPercent = intIndex / intMax
ProgressStyle1 sngPercent
Label4.Caption = "Checking Sheet " & shIndex & " for " & IDKey
DoEvents
If ActiveCell.Value = IDKey Then
ActiveCell.EntireRow.Copy
Sheets(Sheets.Count).Activate
Range("A12").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.PasteSpecial
End If
Sheets(shIndex).Activate
ActiveCell.Offset(1, 0).Select
If CheckBox37 Then
If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 1 Then
shIndex = shIndex + 1
Sheets(shIndex).Activate
Range("A12").Activate
End If
Else
If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 3 Then
shIndex = shIndex + 1
Sheets(shIndex).Activate
Range("A12").Activate
End If
End If
intIndex = intIndex + 1
Loop
End Sub
Sub ProgressStyle1(Percent As Single)
'
' Progress Style 1
' Label Over Label
'
Const PAD = " "
'If ShowValue Then
labPg1v.Caption = PAD & Format(Percent, "0%")
labPg1va.Caption = labPg1v.Caption
labPg1va.Width = labPg1.Width
'End If
labPg1.Width = Int(labPg1.Tag * Percent)
End Sub
'Option Explicit
'Const PI = 3.14159265358979
Thank you very much for your help in advance, would be great to learn whats causing this.
Ill start working on a dummy sheet for you guys incase you need more information. I can have this ready for you sometime tomorrow if you need it.
Thanks again