3 Replies - 2009 Views - Last Post: 25 September 2012 - 07:18 AM Rate Topic: -----

#1 GhoulGreen  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 3
  • Joined: 09-November 11

Clicking Excel buttons programatically through macro

Posted 22 September 2012 - 04:22 AM

Hiya

I'm working on a large workbook. Alot of the worksheets have a button with a macro behind them that makes a XML file using some of the data on the sheet.

I'm making a button with a macro that clicks all the other buttons. The buttons on the other pages are just normal buttons(form controls), and they all have the same name "To XML".

So far I've written this code but I can't get it to work. It does go though all worksheets and get the shapes, but I fail to select the button and click it. Does anybody have an idea how to make this work?
//This code is not exactly what i wrote, but it had the same structure. I am at home right now and can't access my work computer..

Sub clickAllXmlButtons() 
    Dim ws As Worksheet 
    For Each ws In ActiveWorkbook.Worksheets 
         Dim sh As Shape
         For Each sh in ws.Shapes
              If sh.Text = "To XML" Then
                   sh.Select
                   sendKeys(" ")
              End If
         Next sh    
    Next ws 
End Sub 



Is This A Good Question/Topic? 0
  • +

Replies To: Clicking Excel buttons programatically through macro

#2 GhoulGreen  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 3
  • Joined: 09-November 11

Re: Clicking Excel buttons programatically through macro

Posted 24 September 2012 - 01:02 AM

Ok the problem is solved so I am posting the solution here.
It is a lot easier to call the macro behind the button within the context of the worksheet.
So I activate the worksheet and if it conains the control which I want, then I execute the function.

Sub ClickAllCreateButtons()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
        Dim sh As Shape
        For Each sh In ws.Shapes
            If (sh.Type = msoFormControl) Then
                If (sh.FormControlType = xlButtonControl) Then
                    If (sh.TextFrame.Characters.Text = "To XML") Then
                        Call toXML
                    End If
                End If
            End If
        Next sh
    Next ws
End Sub


Was This Post Helpful? 2
  • +
  • -

#3 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,770
  • Joined: 26-March 09

Re: Clicking Excel buttons programatically through macro

Posted 24 September 2012 - 02:13 AM

Well done on solving the issue yourself and thanks for posting the solution for the benefit of others.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Clicking Excel buttons programatically through macro

Posted 25 September 2012 - 07:18 AM

As a further point, you can directly call your event handler too. If your button is named command1, then your event handler is Command1_Click(). Command1_Click will call your event handler just as if the user had clicked the button.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1