CODEWITHSUNDEEP
excelvba
Rushui Guan
Rushui Guan

Reputation: 3163

Put a user form directly on the excel sheet

Is there any way to insert a user form directly on the excel sheet?

We can add the build-in controls as well as active x controls. I don't see why we cannot add user forms within the same workbook.

Thanks

Upvotes: 4

Views: 51270

Answers (4)

Riley Carney
Riley Carney

Reputation: 860

I realize it's an old post and this solution might not have been available in the past but you can insert an ActiveX control element and have "Microsoft Forms 2.0 Frame" to control the information.

You will also need to create a custom class to handle the button presses since you can't attach macros directly onto them like a normal button.

Here is an example of mine:

buttonEventHandler

Option Explicit

Public Sub Click(Sender As Integer)

End Sub

xButton

Private WithEvents btn As MSForms.commandButton
Private bEventHandler As buttonEventHandler
Private b As Integer

Public Sub createObject(EventHandlerOf As MSForms.commandButton, EventHandler As buttonEventHandler, xB As Integer)
    Set btn = EventHandlerOf
    Set bEventHandler = EventHandler
    b = xB
End Sub

Private Sub btn_Click()
    If Not bEventHandler Is Nothing Then bEventHandler.Click (b)
End Sub

in Microsoft Excel Objects: thisWorkbook

Public Sub buttonEventHandler_Click(Sender As Integer)
Select Case Sender
Case 1
    'Do Stuff
End Select
End Sub

Private Sub workbook_open()
Sheet1.Frame1.Activate
Set xbtn = New Collection
Dim o As Object

Dim xB As New XButton
xB.createObject Sheet1.Frame1.Controls("excelCommandButton"), ThisWorkbook, 1
xbtn.Add xB
Set xB = Nothing
End Sub

Upvotes: 1

Bob22
Bob22

Reputation: 109

Place this in the WorkSheet module VB:

Private Sub Worksheet_Activate() UserForm1.Show End Sub

Upvotes: 1

jpinto3912
jpinto3912

Reputation: 1465

You could place MyForm.Show code on the Workbook.Open event to launch the form whenever you open the file... that I've done. You can even choose where to place the form.

Your wish, if possible (I'm pretty positive it's not), could trigger concurrent VB code: e.g. auto-calculate of cells, and the form code. That's an issue because XL is single VBA thread, hence it's impossible to have two things happening. Either the form is showing and it's code is running, or XL application is running and doing cells/graphs/etc stuff.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

No, I don't think it's possible.

Userforms are merely containers to hold your ActiveX controls. Spreadsheets are also ActiveX control containers, so I'm not sure what the benefit of having a container in a container would be.

You could easily color a group of cells to look like a userform and place ActiveX controls within that range. That would simulate a userform embedded on a spreadsheet. You'd be missing userform level events and probably a few other things. But if you wanted those things, you'd probably just use a userform.

If there's something you want to do that I'm missing, let me know.

Upvotes: 2

Related Questions