Assign macro to Button inside ActiveX Frame

[Llupo01]

Hello experts,

as I did not find solution anywhere and dont know how to solve it myself, I would like to ask you for help

I have sheet (for example sheet1), on that sheet I have Frame(Active X controls), inside this frame is button and I dont know, how to assign macro to this button.
Usual way "double click to button" to assign macro does not work, in design mode right click on frame -> Frame object -> edit and then trying to assign macro also does not work (not available to assign code), view code also does not work (it shows only code of the Frame).

Please can anybody help, thank you very much in adavance.

Best regards,
Tom

 

[Norie]

Tom

Why are you using a Frame?

 

[Llupo01]

I wanted to learn something new...also I do not want to protect sheet, and I dont want user to move group of controls (if its in group box, they still can move groupbox without turning on designer mode) and I also cannot have it in front of some shape, because they could delete it. And I need to have controls on different color background then is rest of the sheet.

And I also dont want to use UserForm for this

 

[ZVI]

Well, try this if you are ready to learn
Put the below code into the Module1 standard-module

' Code of Module1
Option Explicit
 
Dim objButtons(1 To 10) As New Class1
 
Sub CreateObjects()
  With Sheet1.OLEObjects
    .Delete
    With .Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False)
      .Name = "Frame1"
      .Left = 10
      .Top = 10
      .Width = 200
      .Height = 75
      With .Object.Controls.Add("Forms.CommandButton.1", "cmdButton1", True)
        .Caption = "OK"
        .Left = 5
        .Top = 5
        .Width = 75
        .Height = 30
      End With
      With .Object.Controls.Add("Forms.CommandButton.1", "cmdButton2", True)
        .Caption = "Cancel"
        .Left = 100
        .Top = 5
        .Width = 75
        .Height = 30
      End With
    End With
  End With
  ' We need to break the execution time here!
  Application.OnTime Now, "SetOnAction"
End Sub
 
Private Sub SetOnAction()
  ' Link frame controls to the Class1.CmdBtn
  Dim i As Long
  Dim x As Control
  For Each x In Sheet1.OLEObjects(1).Object.Controls
    If TypeOf x Is MSForms.CommandButton Then
      i = i + 1
      Set objButtons(i).CmdBtn = x
    End If
  Next
  ' Refresh Excel to allow selecting controls in a frame
  On Error Resume Next
  If ActiveWorkbook.Sheets.Count = 1 Then
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets.Add.Delete
    Application.DisplayAlerts = True
  Else
    With ActiveSheet
      .Next.Activate
      .Activate
    End With
  End If
End Sub

This code goes to the Class1 class-module

'Code at Сlass1 module
 
Option Explicit
Public WithEvents CmdBtn As MSForms.CommandButton
 
Private Sub CmdBtn_Click()
 
  Select Case Me.CmdBtn.Caption
    Case "Ok"
      MsgBox "Ok"
      '...
    Case "Cancel"
      MsgBox "Cancel"
      '...
    Case Else
      MsgBox Me.CmdBtn.Caption
      '...
  End Select
 
End Sub

Then run CreateObject and click the created buttons.

 

[Llupo01]

Thank you very much...I see, that this must run with every workbook open event, which might be complicated for me, because my Frames are quite larger and they have a lot of controls Anyway I will go thru and thank you again very much for this piece of code.
Have a good day

 

[ZVI]

You are welcome!
May be Ribbon customization is a better way,
Ron de Bruin provides excelent examples on that - Ribbon Examples files and Tips