Random UserForm1.Show error 424

[Neltu]

Hi,

Currently i have a worksheet that has a command button that was drawn directly into the worksheet. This button is tied to code in a module does some formatting to the final sheet, and then is supposed to call UserForm1 and display it so that the user can select some items and run another macro.

Currently however if i add more normal data into excel (NOT modifying the buttons or VBA code in any way) after a few saves the command button will stop functioning and give me an error 424 and highlight the UserForm1.Show line.

I end up having to delete the command button, add a new one, and rewrite the code back in and it will work just fine. Which is really odd because i will add more data and save it several times, test the macro to see if it is including the data etc. After a few times of doing this, or if i send it to someone else and they try to use it randomly the button will just stop functioning all together and give the 424 error.

Is there a way to set this up so it stops breaking randomly?

The code in the module is as follows:

Sub Button8_Click()
 
    Sheets("C Bugs").Select
    Cells.Select
    Selection.Copy
    Sheets("Checklist").Select
    Cells.Select
    ActiveSheet.Paste
    Rows("12:1330").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets(1).Activate
    UserForm1.Show
 
End Sub

Any help would be greatly appreciated.

 

[Smitty]

I don't think this will do anything for the error, but it eliminates the select statements, which are generally unnecessary:

<font face=Calibri>    <SPAN style="color:#00007F">With</SPAN> Sheets("C Bugs")<br>        .Cells.Copy Sheets("Checklist").Cells<br>        .Rows("12:1330").ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    UserForm1.Show</FONT>

HTH,

 

[Neltu]

Thank you for that i am still learning how to code and wasnt sure if some of that was unnecessary.

Now back to trying figure out why my button stops working randomly

 

[Colin Legg]

Hi,

What is the error message that accompanies that error number?

What code do you have in the Userform's Initialize and Activate event handlers?

 

[mikerickson]

Is the userform modal or modeless?
When the userform is dismissed, is it hidden or unloaded?
What version of Excel are you using? Some versions are not very stable re: userforms and require frequent Saves.

 

[Neltu]

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:

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:

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:

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

 

[ACCtionMan]

Is there any way the form can be closed/hidden, other than the 'X' (close button). I have had similar problems to this in the past and a search of the code found I had left the form loaded.

Make sure you have 'Unload UserForm1' at any exit point to your code.

If your code is rock solid, this can also happen if the user just closes the spreadsheet with the form still loaded.

 

[Neltu]

There is a second command button titled Done for the user's to press which formats the final sheet to the desired outcome.

That code is as follows:

Private Sub CommandButton2_Click()                                                              ' Done Button
 
    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 "This should appear if no boxes are checked.", vbOKOnly, "YAR!!"
        Unload Me
    Else
 
        Sheet7.Range("TRACKER").Copy 'This will copy the needed range based on a defined name
        Sheets("Checklist").Activate
        ActiveCell.Offset(1).PasteSpecial 'This will put the named range where it needs to go
        Unload Me
    End If
 
End Sub

Thanks for that suggestion. I think i'll change all instances of Unload Me to Unload UserForm1 to be safe and see the button stops working randomly again.

This morning so far it has worked but i still need to add data, so ill see if this prevents the button from breaking.

I suspect that when i send it to others that it might stop working because they may have another excel sheet running that may also have a UserForm1 and there is alot of activesheet and activecell in this macro. (But i never have more then one excel sheet open on my machine and it would still occur, but at alot less rate then when i send it to others.

I think ill try changing the UserForm1 to UserFormA and ill let you guys know later on today if the error pops up.

Thanks for all your suggestions again.

 

[Neltu]

So far the button hasnt broken, i think changing the Unload Me has fixed the issue.

Thanks to you All :D