Run Macro on different worksheet

[mikegs1]

Hi - My Excel 2013 Spreadsheet/Workbook has two sheets, Tool and Positions. The Positions sheet is simply a number of columns with filters applied. I created checkboxes that call macros that apply filtering. Everything works perfectly fine as long as the checkboxes are on the Positions sheet where the data is. Issue is - I need the checkboxes on the Tool sheet. I have searched for a few hours now and cannot seem to find what I have to think is a small bit of code that let's me create the checkboxes on the Tool sheet while having the macro on the checkbox run on the Positions sheet. My macro code follows:

Please Login or Register  to view this content.

To reiterate the code above is on the checkboxes that are sitting on the Positions sheet. I need tthe checkboxes on the Tool sheet. Is there a line of code or two I can add to the code above allowing me to create the checkboxes on the Tool sheet and tell the marco to run on the Positions sheet where the filtered data is?

Thanks in advance for any assistance.

[adyteo]

you probably have the macro under the Positions sheet. what you need is to move that code to Tools sheet and make sure that the codes (AppBarYes and AppBarNo) are in a Module, not under a sheet, in order for them to work.

[mikegs1]

Thank you adyteo. I think I completed what you stated - I added a checkbox named AppBar to the Tool sheet and when in design mode selected the checkbox, added the code above and saved. I now see the cde in VBA under Microsoft Excel Objects - Sheet1 (Tool) and in Module1. Module 2 looks to be referencing the macro's and the cell range the macro's call. That seems to be the issue.

Using the checkbox as a user would produces the error "We couldn't do this for the selected renge of cells. Select a sinble cell withing a range of data and then try again." Selecting debug brings me to VBA, on the Tool sheet object within the AppBar macro:
Module2 page within the area calling the AppBar marco:

Sub AppBarYes()
'
' AppBarYes Macro
' AppBarYes
'

'
Range("H1").Select
ActiveSheet.Range("$D$1:$Q$381").AutoFilter Field:=4, Criteria1:="Yes"
End Sub

ActiveSheet.Range...ect...is the hilighting error.

Again it appears that Excel is trying to run the code on the active sheet (which is the Tool sheet where the checkbox is) and not the Positions sheet where the data to be filtered is.

Any guesses?

[adyteo]

insert a line like this:

Please Login or Register  to view this content.

[mikegs1]

adyteo - perfect, that worked.