2

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!

pnuts
  • 58,317
  • 11
  • 87
  • 139
BiGXERO
  • 7,104
  • 8
  • 23
  • 25

1 Answers1

2

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
Jerry Beaucaire
  • 3,133
  • 16
  • 14
  • + 1 Nicely explained :) I think you have an extra "=" sign before `"Day" & dayCounter` – Siddharth Rout May 09 '12 at 10:18
  • 1
    Your formula definately got me in the right direction, however, the button would not go where they were supposed to. For example: ActiveSheet.Buttons.Add(Range("B1").Top, Range("A1").Left, 48.75, 13.5).Text = "B1" would place a button in the very top left of the screen (A1) and: ActiveSheet.Buttons.Add(Range("C5").Top, Range("C5").Left, 48.75, 13.5).Text = "C5" places a button halfway between row 7 and 8, and left of center between columns B and c. Though passing parameters bit works like a charm. – BiGXERO May 10 '12 at 05:04
  • 1
    JUst to be clear, [B1].Top means "top of the screen" and [A1].Left means "left of the screen", so together they WOULD result in TOP/LEFT of the screen, equivalent to A1. – Jerry Beaucaire May 10 '12 at 09:07
  • That makes sense. Using your code as a base ive now adapted my original plans to use a user form instead of trying to add controls directly to the sheet. Ive started another question due to the change of scope. If you have any advice it would greatly be appreciated. Thanks for the help so far. [link]http://stackoverflow.com/questions/10592641/assign-event-handlers-to-controls-on-user-form-created-dynamically-in-vba – BiGXERO May 15 '12 at 00:17