Search code examples
vbaexcelexcel-2007

Setting Button Location for Macro that Generates Form Control Buttons

I currently have data that I am splitting into multiple sheets and allowing the user to select which sheet they run the macro on. The work sheet is used as a filtration between multiple systems

I have a named range (dayList) that groups the data into days, and creates a new sheet for each day (day1, day2, etc). Each sheet must then have another macro applied (screen data) that will filter the data in each of these sheets in exactly the same way.

I am trying to create a UI that will approximate the user being able to click on one of the cells in the named range to run the macro for that particular day. The code snippet im currently working with is below. 

 Sheets("LaunchScreen").Activate
    Cells(rowCounter, 6).Value = "Day" & dayCounter
    ActiveSheet.Buttons.Add(538.5, 56.25, 48.75, 13.5).Name = "Day" & dayCounter
    ActiveSheet.Buttons("Day" & dayCounter).Select
    Selection.OnAction = "JoinTransactionAndFMMS"

Im looping this to create a new button for each day then pass the button name to another macro as a parameter to find the worksheet in this workbook that shares the same name.

TLDR: I need to:

  1. Set the location of a button using VBA, preferably matching location to a cell reference e.g. Range("A1").Button.Insert
  2. Pass a cell reference from a named range into a macro

An excel noob in way over his head here. Any help would be greatly appreciated!

Solution

  • 1) The .Add method expects 4 parameters. The first two are TOP and LEFT positions to get the UPPER LEFT corner of the button, the 3rd and 4th are height and width settings for the button.

    So, if you know you want to add a button and the reference is to position it at cell C10 (a better example than A1), then this would do it:

    ActiveSheet.Buttons.Add(Range("C10").Top, Range("C10").Left, 48.75, 13.5).Name = "Day" & dayCounter

    2) Your macro has to first be designed to accept a parameter being "passed" into it. So, something like

    Sub MyMacro(MyRange As Range)
    MsgBox MyRange.Address
End Sub

    Now, your other macro can call MyMacro and you must pass in the parameter as it is called:

    Sub test2()
   Call MyMacro(Range("Animals"))
End Sub