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!

 

[jackson1990]

Re: Changing code for a control form to activex

So, this would work, but i have one macro that is used like 5-6 on one sheet (and used about the same on 9 other sheets). So I need it to be able to pull the name of the command button that is clicked. Is there a way to do that in activex buttons?

 

[Fluff]

Re: Changing code for a control form to activex

Why are you changing to ActiveX buttons?
Form Controls are better & you don't need to change anything.

 

[jackson1990]

Re: Changing code for a control form to activex

There's another thread that I started that explains this.

https://www.mrexcel.com/forum/excel...ontrol-click-5-other-buttons.html#post5058500

Basically I have one macro that is used many time over. Depending on the position of the button it produces different things in the outlook appointment. I want to create a button that will basically "push all the buttons at once" so that the user doesn't necessarily have to go through and hit each button separately. Yet there is no clear way to do that with form controls from what I can tell. You can call a macro, but you cannot command it to click a button. The goal here is to have all the buttons on the active sheet be pushed or at least produce all 5 or 6 outlook appointments pop up to the users screen. I did not see a way of doing that so. If you have suggestions on how to achieve it I am willing to listen. But it seemed difficult or not possible with so many buttons (60 or so throughout 9 sheets) all with different named buttons to produce this.

 

[Fluff]

Re: Changing code for a control form to activex

Do the buttons move or are they in fixed positions?

 

[jackson1990]

Re: Changing code for a control form to activex

They don't move per say, but i mean on each different sheet there could be a little variation in position. Were you think of some type of click function associated with a part of the screen?

 

[Fluff]

Re: Changing code for a control form to activex

If they are fixed, then so are the ranges so you can run a loop along the lines of

Dim AddAry As Variant
Dim RngAdd As Variant
Dim r As Range
AddAry = Array("pcode!B2", "New!A5")
For Each RngAdd In AddAry
   Set r = Range(RngAdd)
   ' your code
Next RngAdd

 

[jackson1990]

Re: Changing code for a control form to activex

Sorry, I'm not the best with VBA. So your saying if they are in the same position through the sheets then i could use this code?

I'm asking to try to understand. Cause each sheet varies a little one sheet the button maybe 10 rows down and on the next 12 rows down. Does that mean his would not work?

 

[Fluff]

Re: Changing code for a control form to activex

If the buttons do not move then the range they represent don't move. So rather than trying to find where each button is, you can simply hard code it.
If you create 2 sheets, 1 called "Pcode" & the other called "New" then run this

Sub chk()
Dim AddAry As Variant
Dim RngAdd As Variant
Dim r As Range
AddAry = Array("pcode!B2", "pcode!H5", "pcode!A11", "New!A5", "New!H7", "New!B19")
For Each RngAdd In AddAry
   Set r = Range(RngAdd)
   r.Interior.Color = 45678
Next RngAdd
End Sub

You'll see that the cells mentioned in the code will be coloured.
Where the values in the array represent the location of a "button"

 

[jackson1990]

Re: Changing code for a control form to activex

Fluff that worked beautifully! Thanks for sticking with me for the process and explaining it there at the end! Major kudos to you!

 

[Fluff]

Re: Changing code for a control form to activex

Using this system, you can then do something like

Sub OneButton()
   Dim AddAry As Variant
   Dim RngAdd As Variant
   AddAry = Array("[COLOR=#ff0000]'pcode'!B2[/COLOR]", "[COLOR=#ff0000]'pcode'!H5[/COLOR]", "[COLOR=#ff0000]'pcode'!A11[/COLOR]", "[COLOR=#ff0000]'New'!A5[/COLOR]", "[COLOR=#ff0000]'New'!H7[/COLOR]", "[COLOR=#ff0000]'New'!B19[/COLOR]")
   For Each RngAdd In AddAry
      Call EmailProp(Range(RngAdd))
   Next RngAdd
End Sub

Sub EmailProp(r As Range)
   Dim oApp As Outlook.Application
   Dim oNameSpace As Namespace
   Dim oItem As AppointmentItem
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

If you replace the values in red with the ranges that you want to pass to the EmailProp routine, this should do what I think you're after.
Simply call OneButton from the button