CODEWITHSUNDEEP
excelvba
Keizzerweiss
Keizzerweiss

Reputation: 81

Having a sheet that contains buttons for macros that run off of selections

Is it possible to use Selection to define data used in a macro button that is in another sheet.

I'm trying to make a template workbook that's first sheet is a bunch of macro buttons. I want to be able to select data in another sheet, click my button sheet, and click my button to run a macro on the data I have selected in the other sheet.

The problem that I'm running into is that sheets seem to have independent selections at the same time. So my macro always runs on the selection from the button sheet instead of the sheet I was on. Any thoughts on how I can make this work? The selection of the data needs to be dynamic, so I can't just say .Cells(1,1).value because it might not be the data I need.

Upvotes: 0

Views: 56

Answers (3)

Keizzerweiss
Keizzerweiss

Reputation: 81

To Accomplish this I made rng a public variable and stored my selection from sheet2 in it. Then I used a click event in my buttons to to call the macros that I wanted to run on my range. Here are some screenshots of the codes in my modules and in my sheets. This answer is for anyone that finds this in the future.

https://i.sstatic.net/PUAex.jpg

The Biggest thing to take away from this is that Public Variables in vba only have to be declared once in any of your modules. NOT IN YOUR SHEET CODE. This was the biggest thing that held me back from getting this done. Good luck and contact me with any questions.

Upvotes: 0

Keizzerweiss
Keizzerweiss

Reputation: 81

https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/workbook-sheetselectionchange-event-excel

In this link is where I need to start. Every time that I change my selection it runs an event where I could maybe grab the cell values and store them in an array that will stay static when I change sheets. If I get it right I'll post it here. 5/31/2018

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96763

Say there are two types of sheets:

  • a set of data-sheets
  • a single button-sheet which controls processing via macros tied to buttons

We code a single global range variable in a standard module. We code selection change event macros in all the data-sheets. Then:

  1. We click on a data-sheet
  2. We select cells on the data-sheet
  3. the event macro on that data-sheet records the selection in the global variable
  4. We click to get on the button-sheet
  5. We click on a button
  6. the button macro retrieves the global range
  7. the button macro determines the sheet associated with the stored range (from the Parent Property) and also the cells on that sheet which we selected
  8. the button macro processes the data

EDIT#1:

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Also just Google: Excel VBA Event Macro

(there are many examples in this forum, for example)

Upvotes: 1

Related Questions