ontime macro button activates all sheets in woorkbook intead of just the one it is pressed

Hi,

I think I'd be inclined to declare a Module level variable
Dim ws As Worksheet

then as the first line in your OnTimeMacro set the variable with
Set ws = ActiveSheet

Then change

Please Login or Register  to view this content.

to just

ws. Range("A1").Value = Format(Now, "hh:mm")

Hi Peter,

A Module level variable just means declare it outside of a procedure and above all the procedures at the top of the code in the same area as the Option Explicit code line.

Using a Dim statement outside a procedure will mean the variable is available to all procedures in that module only. Using a Public statement means it will be available to all procedures in all modules. When you use a Dim statement at the Module level it's equivalent to using a third statement called Private. i.e. the two operate essentially the same and arguably it makes code easier to understand.

See the attached.

OK I think I understand how you're wanting this to operate. Try the attached.

I've pointed the buttons to a new Procedure 'SetSheet' which identifies which sheet button was pressed and sets the sheet variable accordingly with the Select Case statement code. This SetSheet Procedure ends by calling the OnTimeMacro.

Sorry Peter,

I pointed the Sheet 2 button to the SetSheet macro but forgot to point the Sheet1 button to the same macro. Just change that.

Yes, you'll need to modify the Select Case statement in a similar way to the first two sheets by adding additional Case Statements.

Regards