CODEWITHSUNDEEP
vb.netexcel
Dr R Dizzle
Dr R Dizzle

Reputation: 272

How to create Excel Controls (Combo boxes etc) using Visual Basic

I'm working on a project to create and email a spreadsheet that is created by an ASP.NET/Visual Basic web application.

The top of this spreadsheet is one large cell, in which sits a Drop Down List, two Text Boxes, a few Labels and a Checkbox.

How do I create and organise these controls using Visual Basic? The spreadsheet is being physically created and I can manipulate cells at the moment, but I do not know how to create those controls specifically.

Upvotes: 0

Views: 700

Answers (1)

barryleajo
barryleajo

Reputation: 1952

If you want to create these controls programmatically then this code should get you going, Essentially add a control, position it and set those properties that you require.

Sub addControls()

    With Sheets("Sheet1")
        .Columns(1).ColumnWidth = 60
        .Rows(1).RowHeight = 150

        'label
        Set addedLbl = .OLEObjects.Add(ClassType:="Forms.Label.1")
            With addedLbl
                .Left = 180
                .Top = 25
                .Width = 90
                .Object.BackColor = &HC0C0FF
                .Object.Caption = "A Coloured Label"
            End With

        'combobox
        Set addedCmbo = .OLEObjects.Add(ClassType:="Forms.Combobox.1")
            With addedCmbo
                .Left = 180
                .Top = 75
                .Width = 90
                .Height = 20
            End With

        'checkbox
        Set addedChkBox = .OLEObjects.Add(ClassType:="Forms.CheckBox.1")
            With addedChkBox
                .Left = 25
                .Top = 25
                .Width = 90
                .Height = 20
                .Object.Caption = "A Checkbox"
                .Object.SpecialEffect = 2
                .Object.Value = False
            End With

        'textbox
        Set addedTextBox = .OLEObjects.Add(ClassType:="Forms.TextBox.1")
            With addedTextBox
                .Left = 25
                .Top = 75
                .Width = 90
                .Height = 50
                .Object.WordWrap = True
                .Object.MultiLine = True
                .Object.SpecialEffect = 1
                .Object.Text = "Some wrapped text in this raised textbox."
            End With

    End With

End Sub

If you want to add them manually then select the Developer Tab and in the Controls group select Insert. You can then add controls to the worksheet. I have used ActiveX controls. Then right-click the control to list its Properties.

Add Controls

Upvotes: 1

Related Questions