Macro Run

[D@SE]

I am kind of green here. I have various macros in a workbook and i want one
particular macro to run each time a particular tab is clicked. i have read
other similar questions and responses and can not seem to follow. i guess i
need a little more detail. Thank you so much.

 

[Mike H]

Hi,

Right click the sheet tab of the worksheet you want to call the macro and
paste this in. I've assumed you macro is in a general module. Change MySub to
the name of your sub

Private Sub Worksheet_Activate()
Call MySub
End Sub

Mike

 

[Mike H]

I meant

Right click the sheet tab of the worksheet you want to call the macro, view
code and paste this in.

 

[D@SE]

Thanks Mike, it seems to work but when i click the tab it goes to the tab
where it is to do the macro function and the screen flashes for 5 or 10
seconds and then it comes back as it should. i do not understand it.

 

[D@SE]

i think every time it comes back to my tab it is trying to run through the
whole process again and again.

 

[Mike H]

Hi,

It will only call your code once but without seeing what your code is I
think the best solution might be

Sub YourSub()
Application.screenupdating=false

'Your code

application.screenupdating =true
end sub

Mike

 

[Mike H]

i think every time it comes back to my tab it is trying to run through the

whole process again and again.

It will do that because you asked for that

I want one particular macro to run each time a particular tab is clicked.

Mike

 

[D@SE]

Thanks for your help. When i click on tab titled "BID" , it is to go run a
macro titled "NOW" which puts the date on tab "LOG IN" in cell BF5 and then
returns to tab "BID". Here is my code, '
Sheets("LOG IN").Select
Range("BF5").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("BG5").Select
Range("E11:E12").Select
Sheets("BID").Select
End Sub
thank you so much

 

[D@SE]

As i said i know i am green. How do i make it run the macro only once and
land back on my tab without running it again and again? I hate to be a bother
here and do appreciate your help to a green horn.

 

[Mike H]

Hi,

Simplify your macro to this, it's selecting the sheets and then re-selecting
BID that causes the macro to execute twice

Sheets("LOG IN").Range("BG5").FormulaR1C1 = "=NOW()"

Mike

 

[Don Guillett]

Put in the sheet code of the BID sheet by right click sheet tab>view
code>copy/paste this.
Now, each time you activate the BID sheet it will check to see if the cell
matches today's date. If so, it does nothing. If not, it enters today's
date. Is this what you want?

Private Sub Worksheet_Activate()
With Sheets("LOG IN").Range("BF5")
If .Value <> Date Then .Value = Date
End With
End Sub

 

[D@SE]

Thanks Don that works great!
--
David

Put in the sheet code of the BID sheet by right click sheet tab>view
code>copy/paste this.
Now, each time you activate the BID sheet it will check to see if the cell
matches today's date. If so, it does nothing. If not, it enters today's
date. Is this what you want?

Private Sub Worksheet_Activate()
With Sheets("LOG IN").Range("BF5")
If .Value <> Date Then .Value = Date
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help

 

[D@SE]

Thanks for all your help Mike, that works very good.