VBA runs on all active sheets and I only want it to run on 1 specific sheet

**Doolski** · 05-12-2017, 12:21 PM

I know this is probably an easy solve and I'm over looking something.
I have a timer that starts once I load data from a source and I need it to stay running for when specific events happen I want the user to record the time. And found this timer works well as it sits on the sheet they need to enter the data in to and it's in sync with the network. So I have an accurate time capture.

Problem with my code below.. It is active on ALL sheets. I have two sheets in this file.. but If they open up ANY other workbook it populates the time in cell A9.
Can I define the clock to only appear on one specific sheet only like "Sheet 1"?
Much thanks.

Please Login or Register  to view this content.

**Marc L** · 05-12-2017, 12:27 PM

Do you know there is only one active sheet ?!

**Doolski** · 05-12-2017, 01:58 PM

Sorry My apologies. Thread has been edited.

I do know there is only one active sheet.. but when I click a new tab or open a new workbook it is now the active sheet and the VBA is applied to that new active sheet.

Thx

**Marc L** · 05-12-2017, 02:04 PM

So instead of ActiveSheet in code just use a valid reference to the desired worksheet …

**Arkadi** · 05-12-2017, 02:06 PM

I think the OP meant ANY active sheet, not "ALL"... yes best way is to specify the sheet instead of using active... the other way is If ActiveSheet.Name = "the sheet you want" then....

**Arkadi** · 05-12-2017, 02:08 PM

I think the OP meant ANY active sheet, not "ALL"... yes best way is to specify the sheet instead of using active... the other way is If ActiveSheet.Name = "the sheet you want" then....
I guess it depends a little on what you want... no matter which sheet is active, you want to put the time in a specific sheet then use specific sheet. If you only want to execute the code if that specific sheet is active, then use an if statement to control when the code runs.

**Doolski** · 05-12-2017, 02:21 PM

Thanks for troubleshooting. I think it's a problem with the code...
I define the sheet as you indicated but then I get errors as soon as I click on another tab and/or open a new workbook.

So it must be re-running itself on each sheet and/or new workbook as it's trying to re-use the sheet name.

01.jpg

and this is the line error

02.jpg

**Doolski** · 05-12-2017, 02:30 PM

I found out that when I stop the timer using this code"

Please Login or Register  to view this content.

It does not re-create the error but the clock stops running.

**Arkadi** · 05-12-2017, 02:46 PM

ActiveSheet.Name = ... sets the name...
If what you want is (no matter which sheet is active), just put the time in that specific worksheet then :

Please Login or Register  to view this content.

If for some reason you need to activate that sheet (which is not what one should usually do just to put values, but only when truly needed): "Worksheets("Flt Watch Desk").Activate"

**Doolski** · 05-13-2017, 08:10 PM

So that may explain part of the coding issue.

I defined the Worksheet can now run the code

Please Login or Register  to view this content.

and start a new sheet and the code won't run on the new sheet. This is good!

But when I open a new workbook while I have this one running I get an error.

1.jpg

Do I need to define the workbook as well?
Thx

**Doolski** · 05-13-2017, 08:17 PM

Here is the code I'm now using and the file.

Please Login or Register  to view this content.

When I open the workbook and click the box to run the macro. One the time populates and runs in 9, 1 then I open a new workbook.. error.
Thx

**Arkadi** · 05-15-2017, 09:16 AM

try changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

**Doolski** · 05-16-2017, 10:39 PM

Thanks Arkadi!
This code works and solves the problem for this specific workbook. I had used this code in my working workbook with much more code and VBA. This works perfect so it tells me that my code in my other workbook is the problem.

A new thread!
Thanks for supporting this issue!

VBA runs on all active sheets and I only want it to run on 1 specific sheet

LinkBack

Thread Tools

Rate This Thread

Display

VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Re: VBA runs on all active sheets and I only want it to run on 1 specific sheet

Thread Information

Users Browsing this Thread

Similar Threads

Copy specific range from the new existing Active Sheet and paste value to Master sheet.

Application.onkey, only active on specific sheets!

VBA To Open Template From Active Sheet, Copy Specific Data To Template,Close Active Sheet

[SOLVED] Macro only runs on specific sheet name

Macro stores Active Sheet range but I need it to find a new range each time it runs

Copy the data in any active row in Sheet 1 to specific cells in Sheet 6

[SOLVED] How to get (range)macro to always run code on a specific sheet regardless of active sheet?

Bookmarks

Bookmarks

Posting Permissions