How do you control a button inside a multipage in excel VBA

Question

I have a multipage, I was successfully able to copy elements of the first page which is my reference page to new pages which is created dynamically.

My question is, how do I set a commandbutton's actions inside a page in a multipage control? My goal is to click on the button from any page then pops up another form.

How do I do this? It's pretty hard to adjust from Android to VB. I really appreciate any help from you guys.

This is my code in cloning pages.

i = 0
MultiPage1.Pages.Add
MultiPage1.Pages(i).Controls.Copy
i = i + 1
MultiPage1.Pages(i).Paste
For Each ctl In Me.MultiPage1.Pages(i).Controls
     If TypeOf ctl Is MSForms.Label Then
           '~~~ code omitted
           Select Case ctl.Tag
                Case "startTime"
                        ctl.Caption = "4:00pm"
           End Select
     End If
Next

this is how it's going to look like. enter image description here

the button will concatenate all strings inside the page. the concatenated string will be shown on another userform.

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

You probably would be better off creating a button in the ribbon so that it is available on all pages:

http://chandoo.org/wp/2012/02/27/how-to-add-your-own-macros-to-excel-ribbon/

EDIT:

My bad, I thought you meant a worksheet instead of a VBA MultiPage in a userform.

Check this out. I was able to make this work for me:

Assign code to a button created dynamically

Class1:

Option Explicit

Public WithEvents CmdEvents As MSForms.CommandButton

Private Sub CmdEvents_Click()
    MsgBox "yo"
End Sub

Userform with MultiPage object:

Option Explicit

Dim cmdArray() As New Class1

Private Sub CommandButton1_Click()
    Dim newControl As Control

    Set newControl = Me.MultiPage1.Pages(0).Controls.Add("Forms.CommandButton.1", "NewCommand", True)

    newControl.Object.Caption = "hello"


    newControl.Left = 50
    newControl.Top = 50

    ReDim Preserve cmdArray(1 To 1)
    Set cmdArray(1).CmdEvents = newControl

    Set newControl = Nothing
End Sub

edited May 23 '17 at 11:44

Community

1
1

answered Mar 04 '13 at 03:53

Joseph

5,070
1
25
26

Thank you for your answer joseph. I was thinking of that way as well for it is easier but, I have a reason why I raised this question. I wanted to know if there is a way to do it. what if there are buttons inside a page which is different from the buttons on the other page. how will you solve it? that is why I raised this question. – Jovanni G Mar 04 '13 at 06:19
Ah, I see. It used to be much easier and I haven't had the chance to really look at customizing the ribbon. However, there is a really nice resource that I found that you can take a look at: http://www.rondebruin.nl/ribbon.htm - take a look at the example workbooks. I'll come back to this when I have time to check it out. – Joseph Mar 04 '13 at 14:34
Thanks Joseph. I really appreciate your suggestion. If all else fails, then my choice will be Ribbons. – Jovanni G Mar 05 '13 at 03:52
@JovanniG sure thing. I misunderstood your request. When you said pages, I thought you meant worksheets. I see you are using a userform with a MultiPage control. I'll adjust my answer accordingly. – Joseph Mar 05 '13 at 04:28
dude it worked. I guess it's much better if I create the buttons and labels rather than copying them from the first page. Thanks a lot dude. you rock. – Jovanni G Mar 18 '13 at 09:35
@JovanniG nice! Glad to have helped :) – Joseph Mar 18 '13 at 15:38

score 0 · Answer 2 · answered Mar 04 '13 at 14:36

0

You can do this with a custom class. The class basically has one member Public WithEvents b as CommandButton.

The trick is the WithEvents keyword. You can now insert some code to generically handle the click of a button that is assigned to this class:

Private Sub b_Click()
    MsgBox "You clicked " & b.Name 'Modify this event so that different code is executed base on the page/name/etc.
End Sub

In order to make this work, you need to assign the button you create in your code to an object of this new class:

Private objButtonHandler as New MyClass 'this should be scope a UserForm wide

Sub YourSub
    Dim objYourButton as CommandButton
    Set objYourButton = ... `your code here
    Set objButtonHandler.b = objYourButton
End Sub

answered Mar 04 '13 at 14:36

Peter Albert

16,917
5
64
88

Thanks for you response Peter, I don't somehow understand what to put on the "`your code here". The buttons were a clone from the reference page which is Page(0) on the Multipage. And the pages as well as it's contents are dynamically created. I can post my code here if you'd like. – Jovanni G Mar 05 '13 at 04:07
@JovanniG: How do you clone the page? Maybe you can access the button with something like this: `Set objYourButton = MyMultipage.Pages(MyMultipage.Pages.Count).Controls(1)` You might need to replace the `1` with the number for the button... – Peter Albert Mar 05 '13 at 10:42
I see. Now I get it. I will try this one out. Thanks a lot. – Jovanni G Mar 06 '13 at 04:14
@JovanniG: Great! Let me know how it went! – Peter Albert Mar 06 '13 at 07:29
I tried your suggestion, but it did not worked. I added images on my question and a code snippet to clarify everything as well. – Jovanni G Mar 06 '13 at 12:53

How do you control a button inside a multipage in excel VBA

2 Answers2