Form Controls - Out of Control

[tlc53]

Hi,
I have a spreadsheet which visually isn't large. One particular sheet for example ranges from A1:U159 and plenty of those cells are blank/unused. There are 4 sheets in total, 2 sheets have a bit going on and the other 2 basically just have a bit of text.
However, this spreadsheet takes forever to open as it's 10,359KB large. When I check the Workbook Statistics it tells me there are 18,288 Form Controls!! I have no idea how, what or why.
Does anyone know how I can get to the bottom of this problem and delete Form Controls?
Thank you!

 

[Fluff]

Do you want to delete all the form controls?

 

[Yongle]

Before deleting them, consider finding out what they are, and determine how that are reproducing, to prevent recurrence

I suspect that your 18,288 controls are all in worksheets
- below is code to list controls in each worksheet in a new sheet
- all modules (without detailing controls) are listed

WILL NOT WORK UNLESS - reference added to Microsoft Visual Basic For Applications Extensibility
- in VBA editor
- click on Tools \ References \ scroll down to it \ click in the box \ OK

'add reference to Microsoft Visual Basic For Applications Extensibility
Sub ListControls()
    Dim wb As Workbook, Output As Worksheet, sh As Worksheet, vbc As VBIDE.VBComponent
    Dim OleObj As OLEObject, mso As MsoControlType, shp As Shape, rng As Range, x As Long
   
    Set wb = ActiveWorkbook
    Set Output = wb.Sheets.Add(before:=wb.Sheets(1))
    Output.Range("A:D").ColumnWidth = 20
    Application.ScreenUpdating = False
    On Error Resume Next
'modules
    Output.Range("A1") = "MODULES:"
    For x = 1 To wb.VBProject.VBComponents.Count
        With wb.VBProject.VBComponents(x)
            Select Case .Type
                Case 1, 2, 3: GetRange(Output)(1) = .Name
            End Select
        End With
    Next x
'userforms
    GetRange(Output).Offset(1)(1) = "USERFORM NAMES:"
    For Each vbc In wb.VBProject.VBComponents
        If vbc.Type = vbext_ct_MSForm Then GetRange(Output)(1) = vbc.Name
    Next vbc
    If Output.Range("A2") = "" Then GetRange(Output)(1) = "No UserForms"
'sheets
    GetRange(Output).Offset(1)(1) = "SHEET CONTROLS:"
    For Each sh In wb.Worksheets
        For Each shp In sh.Shapes
            If shp.Type = msoFormControl Then
                GetRange(Output) = Array(sh.Name, shp.Name, GetDesc(shp.FormControlType), "Form Control")
            End If
            If shp.Type = msoOLEControlObject Then
                'Debug.Print "  msoOLEControlObject: " & TypeName(shp.OLEFormat.Object.Object)
                GetRange(Output) = Array(sh.Name, shp.Name, TypeName(shp.OLEFormat.Object.Object), "Active-X")
            End If
        Next shp
    Next sh
End Sub

Private Function GetDesc(CtrlType As Integer) As String
    Dim T As String
    Select Case CtrlType
        Case 0: T = "Button"
        Case 1: T = "Check box"
        Case 2: T = "Combo box"
        Case 3: T = "Text box"
        Case 4: T = "Group box"
        Case 5: T = "Label"
        Case 6: T = " List box"
        Case 7: T = "Option button"
        Case 8: T = "Scroll bar"
        Case 9: T = "Spinner"
    End Select
    GetDesc = T
End Function

Private Function GetRange(ws As Worksheet) As Range
    Set GetRange = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4)
End Function

 

[tlc53]

Before deleting them, consider finding out what they are, and determine how that are reproducing, to prevent recurrence

Thank you this! Very clever and your instructions were very easy to follow.
I ran the code and it created a sheet showing me all the form controls and I can easily see where it's going wrong.

There are two main tabs, they are called
- GST A
- GST B

GST A has 9x check boxes but the report shows me there are 10,018 Check boxes
GST B has no check boxes but the report shows me there are 18,296 Check boxes

It's also strange that the 1x button I have on GST B is called Button 18299 (however, it has not duplicated like the Check boxes).

I guess now I want to try and delete the check boxes but also stop it automatically creating any more in future (which I assume is what it has been doing).

Any suggestions on how I proceed from here please? Thank you!

 

[Yongle]

It appears that whenever one particular VBA procedure (or more) is run it is generating (or has previously generated) "new" checkboxes and buttons
I suspect that "old" checkboxes have been rendered non-visible, by the code whereas "old" buttons have been deleted "on the fly"
The button number (18299) is so amazingly close to 18296 (check boxes) that there is a likely connection!

Let's identify which bit of code is causing the problem. It may be the button code itself
- so let's start there
- I need to see the code behind the button

Right-Click on the button \ Assign macro \ Edit - the code should now be visible (I hope!)
- if the code is not visible let me know and we will look somewhere else

Select, copy the code and do the following:
- click on </> icon (above reply area)
- click to the right of the 1 (simply puts cursor in correct place)
- paste the code
- amend language to VBA Code
- click continue

If there is a button on both sheets, paste codes from both into your reply.
thanks

 

[tlc53]

Thanks. The button on sheet GST B is the only button that exists in this spreadsheet and it is assigned to Sub GSTVariance() as shown below. I have included the code above also, as this is the only other VBA code that exists in this spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Intersect(Target, [B91:B108])
If Not rng Is Nothing Then rng(2, 1).EntireRow.Hidden = False
End Sub

Sub GSTVariance()
    Rows("63:73").Hidden = Not Rows("63:73").Hidden
End Sub

 

[Yongle]

Thanks - that is clear of any problems

Q1 Is there any other code in sheet module for either GST A or GST B ? If so, paste code into your reply
right click on each sheet tab \ View Code

Q2 Is their any code in ThisWorkbook module ? If so, paste code into your reply
from Excel window: {ALT}{F11} \ double-click on ThisWorkbook in Project window

Q3 When you ran the code I gave you, was anything (other than sheet names) listed under MODULES ?

 

[tlc53]

Q1 - No modules exist
Q2 - There was no code under ThisWorkbook module (however, there is now, because that's where I posted your code)
Q3 - Below is a snippet of the report (I took out the bulk/middle of the Check Box lines). As you can see, nothing under Modules.

One thing that I may be guilty of, is sometimes I copy a Form Control Check Box from another spreadsheet and paste it in to my new spreadsheet, so the cell alignment and check box size is already how I want it. Now I'm having difficulties with this strange thing happening, I think it's probably bad practise!

Is there a way I can fix the current file and stop it from doing this? Perhaps deleting all the Form Controls and recreating the ones that need to be there? I think I may have other spreadsheets with the same problem, as they strangely grew very large in size as well.

MODULES:
USERFORM NAMES:
No UserForms
SHEET CONTROLS:
GST A	M1chk2	Check box	Form Control
GST A	M1chk3	Check box	Form Control
GST A	M1chk4	Check box	Form Control
GST A	M1chk1	Check box	Form Control
GST A	M1chk5	Check box	Form Control
GST A	Check Box 6	Check box	Form Control
GST A	Check Box 7	Check box	Form Control
GST A	Check Box 8	Check box	Form Control
GST A	Check Box 9	Check box	Form Control
GST A	Check Box 10	Check box	Form Control
GST A	Check Box 11	Check box	Form Control
GST A	Check Box 12	Check box	Form Control
GST A	Check Box 13	Check box	Form Control
GST A	Check Box 10015	Check box	Form Control
GST A	Check Box 10016	Check box	Form Control
GST A	Check Box 10017	Check box	Form Control
GST A	Check Box 10018	Check box	Form Control
GST B	M1chk2	Check box	Form Control
GST B	M1chk3	Check box	Form Control
GST B	M1chk4	Check box	Form Control
GST B	M1chk1	Check box	Form Control
GST B	M1chk5	Check box	Form Control
GST B	Check Box 6	Check box	Form Control
GST B	Check Box 7	Check box	Form Control
GST B	Check Box 8	Check box	Form Control
GST B	Check Box 9	Check box	Form Control
GST B	Check Box 10	Check box	Form Control
GST B	Check Box 18293	Check box	Form Control
GST B	Check Box 18294	Check box	Form Control
GST B	Check Box 18295	Check box	Form Control
GST B	Check Box 18296	Check box	Form Control
GST B	Button 18299	Button	Form Control

 

[jkpieterse]

I strongly suspect the huge number of objects is caused by copying and pasting cells (which happen to have those controls on top of them) over and over again. I would suggest to simply delete all shapes and recreate the one(s) you need as that is a lot faster than trying to delete the unneeded ones. To get rid of all objects: Hit the F5 function key, click "Special", select "Objects", click OK. Now hit the DEL key and wait for Excel to finish (it may take a while with over 10,000 shapes). Tip: to see shapes on a sheet, click the "Find & Select" magnifying glass on the Home tab and choose "Selection pane"

 

[Yongle]

Or if you want to delete unwanted checkboxes only ....

Sub DeleteCheckBoxes()
    Dim wb As Workbook, shp As Shape
    Set wb = ActiveWorkbook
    Application.ScreenUpdating = False
    On Error Resume Next
   
    For Each shp In wb.Sheets("GST A").Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = 1 Then     'ie a Check Box
                Select Case shp.Name
                    Case "Check Box 4", "Check Box 5", "Check Box 10", "Check Box 11"   '*** checkboxes to be retained in GST A
                        'do nothing
                    Case Else
                        shp.Delete
                End Select
            End If
        End If
    Next shp
         
    For Each shp In wb.Sheets("GST B").Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = 1 Then     'ie a Check Box
                Select Case shp.Name
                    Case "Check Box 4", "Check Box 6", "Check Box 7"   '*** checkboxes to be retained in GST B
                            'do nothing
                    Case Else
                        shp.Delete
                End Select
            End If
        End If
    Next shp
   
End Sub

Amend the 2 lines to list the checkboxes to be retained in each sheet