Form Controls - Out of Control

tlc53

Active Member
Joined
Jul 26, 2018
Messages
349
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
Do you want to delete all the form controls?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,982
Office Version
365
Platform
Windows
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

VBA Code:
'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

Active Member
Joined
Jul 26, 2018
Messages
349
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,982
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 26, 2018
Messages
349
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.

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,982
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 26, 2018
Messages
349
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 AM1chk2Check boxForm Control
GST AM1chk3Check boxForm Control
GST AM1chk4Check boxForm Control
GST AM1chk1Check boxForm Control
GST AM1chk5Check boxForm Control
GST ACheck Box 6Check boxForm Control
GST ACheck Box 7Check boxForm Control
GST ACheck Box 8Check boxForm Control
GST ACheck Box 9Check boxForm Control
GST ACheck Box 10Check boxForm Control
GST ACheck Box 11Check boxForm Control
GST ACheck Box 12Check boxForm Control
GST ACheck Box 13Check boxForm Control
GST ACheck Box 10015Check boxForm Control
GST ACheck Box 10016Check boxForm Control
GST ACheck Box 10017Check boxForm Control
GST ACheck Box 10018Check boxForm Control
GST BM1chk2Check boxForm Control
GST BM1chk3Check boxForm Control
GST BM1chk4Check boxForm Control
GST BM1chk1Check boxForm Control
GST BM1chk5Check boxForm Control
GST BCheck Box 6Check boxForm Control
GST BCheck Box 7Check boxForm Control
GST BCheck Box 8Check boxForm Control
GST BCheck Box 9Check boxForm Control
GST BCheck Box 10Check boxForm Control
GST BCheck Box 18293Check boxForm Control
GST BCheck Box 18294Check boxForm Control
GST BCheck Box 18295Check boxForm Control
GST BCheck Box 18296Check boxForm Control
GST BButton 18299ButtonForm Control
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
986
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,982
Office Version
365
Platform
Windows
Or if you want to delete unwanted checkboxes only ....

VBA Code:
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
 

Forum statistics

Threads
1,086,123
Messages
5,387,978
Members
402,092
Latest member
S_S

Some videos you may like

This Week's Hot Topics

Top