Push one form control to click 5 other buttons

[jackson1990]

So, I have 5 or so buttons I want clicked all at once. They are all the same macro, but based on the location of the button they pull different data. They are all form control buttons, so I was wondering how I would go about making a button that would click all 5? I thought maybe the call function but that only seems to work with Macro's, and since the macro depends on placement in the excel that doesn't work. Is there a way to click all 5 form control buttons via VBA?

For this example, say the form buttons are Button 24, Button 27, Button 28, Button 29, Button 30

Thanks in advance everyone!

 

[mikerickson]

You could use something like

Sub test()
    Dim ButtonNames As Variant
    Dim oneName As Variant

    ButtonNames = Array("Button 24", "Button 27", "Button 28", "Button 29", "Button 30")

    For Each oneName In ButtonNames
        With ActiveSheet.Shapes(oneName)
            Application.Run .OnAction
        End With
    Next oneName
End Sub

 

[jackson1990]

I see what you're saying.

I tried running it, but now I'm getting an error in my button hit. I think because it's not longer locating the cell the button is in? Basically the button check what cell it is in then offsets from there to grab information throughout the sheet. All using the same macro.

Getting a Run-time Error 1004
Application-defined or object-defined error

The line it is referencing within the button is,

  Dim oApp As Outlook.Application  Dim oNameSpace As Namespace
  Dim oItem As AppointmentItem
  Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Link = Replace(ThisWorkbook.FullName, " ", "%20")
Offsetnum = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(-1, 1)

It's say the Offsetnum line....so I'm guessing this has a issue with finding the location and offsetting it?

 

[jackson1990]

Would it just be easier to switch them to activex buttons and use something like

[COLOR=#101094][FONT=inherit]Public[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CommandButton1_Click[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]    CommandButton2_Click
    CommandButton3_Click
    CommandButton4_Click
    CommandButton5_Click [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]

?

 

[mikerickson]

Does each button do the same thing, the only difference being the range involved? (i.e. the TopLeftCell)

If so you could link each of the 5 buttons to code like

Sub AnyButton(optional r as Range)

    If r Is Nothing then
        Set r = Activesheet.Shapes(Application.Caller).TopLeftCell
    End if

    ' code
End Sub

Each of the 5 buttons would be assigned to the macro AnyButton

Then the "call 5" button would be assigned to

Sub Call5()
    Dim ButtonNames As Variant
    Dim oneName As Variant

    ButtonNames = Array("Button 24", "Button 27", "Button 28", "Button 29", "Button 30")

    For Each oneName In ButtonNames
        With ActiveSheet.Shapes(oneName)
            Call AnyButton(.TopLeftCell)
        End With
    Next oneName
End Sub

 

[jackson1990]

Thanks for all the replies Mike.

So it is the same exact macro connected to multiple buttons, based on the position of the button it will offset a certain number of cells to the left and up to get information on the name of the section.

Here is the full code of the button I am pushing just so you can get a sense of what it is doing. It is essentially producing an outlook appointment and fill the subject and body based on what is above it in the title boxes.

Sub EmailProp() 
  Dim oApp As Outlook.Application
  Dim oNameSpace As Namespace
  Dim oItem As AppointmentItem
  Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Link = Replace(ThisWorkbook.FullName, " ", "%20")
Offsetnum = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(-1, 1)
      
  On Error Resume Next
  ' check if Outlook is running
  Set oApp = GetObject("Outlook.Application")
  If Err <> 0 Then
    'if not running, start it
    Set oApp = CreateObject("Outlook.Application")
  End If
  
  Set oNameSpace = oApp.GetNamespace("MAPI")
  
  Set oItem = oApp.CreateItem(olAppointmentItem)
  
  With oItem
  
    .Subject = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(Offsetnum, -5) + " - " + Worksheets("Template Generator").Range("H16") + " - " + Worksheets("Template Generator").Range("B16")
    .Start = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(0, -2) + TimeValue("9:00")
    .Body = "Click below to enter the link for this " & vbCrLf & vbCrLf & _
        "file:///" + Link
    
    .Duration = "480"
    .AllDayEvent = True
    .Importance = olImportanceNormal
    .Categories = "Red Category"
    
    
    .BusyStatus = olFree
    
    .Remindermin = "10080"
    .ReminderOverrideDefault = True
    .ReminderSet = True
    .ReminderMinutesBeforeStart = "10080"


    
    Select Case 1 ' do you want to display the entry first or save it immediately?
      Case 1
        .Display
      Case 2
        .Save
    End Select
  
  End With
    
  Set oApp = Nothing
  Set oNameSpace = Nothing
  Set oItem = Nothing
     
End Sub

So it's not longer producing an error...but not it is not producing any type of result either. Sorry to be a bother Mike. This is just head scratching for me to figure out how to accomplish it. I thought it'd be easy and that there would just be some type of code that would click a form button. But it doesn't seem that easy. To give context too, i have about 10 sheets with this same macro button with about 4-6 buttons on each sheet. Seeming to have different button names too...So I have to figure out how to include only the ones on the activesheet.

 

[jackson1990]

I guess the end result doesn't need to be that it clicks all the buttons, just that it produces all 5 of the outlook appointments at once instead of the user having to click each button separately.

 

[jackson1990]

So, I think switching to Activex buttons will make this easier if one thing works. Can you locate what cell an activex button is like a form control one? I can use,

ActiveSheet.CommandButton1.Value = True

if so.

But when I use the same code under activex I get an error on this line:

[COLOR=#333333]Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell[/COLOR]

I'm guessing because it is not able to find what cell the button is located. Is there another way to find the location with activex?

 

[jackson1990]

Changing code for a control form to activex

I'm looking to change my code to work with an activex button. Currently it is set up as control form button. Specifically I need to change variable r to a code that will find what cell the activex button is in. As the control form code does not work with activex. Here is the code,

[COLOR=#333333]Sub EmailProp() [/COLOR]  Dim oApp As Outlook.Application
  Dim oNameSpace As Namespace
  Dim oItem As AppointmentItem
  Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Link = Replace(ThisWorkbook.FullName, " ", "%20")
Offsetnum = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(-1, 1)
      
  On Error Resume Next
  ' check if Outlook is running
  Set oApp = GetObject("Outlook.Application")
  If Err <> 0 Then
    'if not running, start it
    Set oApp = CreateObject("Outlook.Application")
  End If
  
  Set oNameSpace = oApp.GetNamespace("MAPI")
  
  Set oItem = oApp.CreateItem(olAppointmentItem)
  
  With oItem
  
    .Subject = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(Offsetnum, -5) + " - " + Worksheets("Template Generator").Range("H16") + " - " + Worksheets("Template Generator").Range("B16")
    .Start = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(0, -2) + TimeValue("9:00")
    .Body = "Click below to enter the link for this " & vbCrLf & vbCrLf & _
        "file:///" + Link
    
    .Duration = "480"
    .AllDayEvent = True
    .Importance = olImportanceNormal
    .Categories = "Red Category"
    
    
    .BusyStatus = olFree
    
    .Remindermin = "10080"
    .ReminderOverrideDefault = True
    .ReminderSet = True
    .ReminderMinutesBeforeStart = "10080"


    
    Select Case 1 ' do you want to display the entry first or save it immediately?
      Case 1
        .Display
      Case 2
        .Save
    End Select
  
  End With
    
  Set oApp = Nothing
  Set oNameSpace = Nothing
  Set oItem = Nothing
      [COLOR=#333333]End Sub[/COLOR]

Specifically I need this part,

Dim r As Range
[COLOR=#333333]Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell[/COLOR]Link
[COLOR=#333333]Offsetnum = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(-1, 1)[/COLOR]

To work as it did with the control form button. To find the location of the cell the button is in (row and column) then be able to offset that cell.

Can anyone please help me with this! Thank you.

 

[Fluff]

Re: Changing code for a control form to activex

Try

   Set r = CommandButton1.TopLeftCell