Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    XL97 Capturing button push (Excel)

    I have several buttons assigned to various macros. Is there a way to capture which button is pushed? That way I can use a more generic macro with variables instead of the 10 then I now have. Thanx

    Dennis

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 Capturing button push (Excel)

    Is something like this what you had in mind?

    sub cmdButton1_click
    call CommonRoutine("A")
    end sub

    sub cmdButton2_click
    call CommonRoutine("B")
    end sub

    sub CommonRoutine (strType as String)
    msgbox "the type youu want is " & strType
    end sub


    I assume you're talking about buttons on a spreadsheet rather than on a Userform. On a Userform, you can use a Control Array- an array of controls with the same name, which can be referenced by the index.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: XL97 Capturing button push (Excel)

    Close... here is one of the codes (there are 15, all assigned to buttons) but your reply does give me an idea

    WhatChart = 78
    Do
    WhatChart = WhatChart + 1
    If WhatChart >= 93 Then Exit Do
    Sheets("Data135PlotArea").Select
    Application.Goto reference:="R1C1"
    Application.Goto reference:="R152C1"
    ActiveCell.FormulaR1C1 = WhatChart
    Sheets("Chart135").Select
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut copies:=1
    Loop

    The thing that changes for each is the start value for WhatChart and the max value for WhatChart. Otherwise, the code is the same for all. What I wanted to do is that depending on which button was pushed, assign a value to WhatChart (probably using a Case statement)

    Dennis

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: XL97 Capturing button push (Excel)

    Could you decalre WhatChart as a Public variable, and then have each button assign the appropriate value before calling a common subroutine, like the following

    Private Sub CommandButton1_Click()
    WhatChart = 1
    'Call Common Routine
    End Sub

    Andrew C

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 Capturing button push (Excel)

    Create a procedure that looks like this:

    <pre>Public Sub PrintChart(WhatChart As Long)
    Do
    WhatChart = WhatChart + 1
    If WhatChart >= 93 Then Exit Do
    Sheets("Data135PlotArea").Select
    Application.Goto reference:="R1C1"
    Application.Goto reference:="R152C1"
    ActiveCell.FormulaR1C1 = WhatChart
    Sheets("Chart135").Select
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut copies:=1
    Loop
    End Sub
    </pre>


    Then button click routines that look like this:

    <pre>Public Sub Button1_Click()
    Call PrintChart(78)
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •