How do get a macro to automatically activate when you click on a sheet.

[profklein]

I put the following code in (the macro area of) Sheet 2

Sub Autpen()
Sheets("Home").Activate
MsgBox "Welcome "


End Sub

However, nothing happens when I click into Sheet 2.

 

[Logit]

.
Both of these macros go into the sheet macro area :

Option Explicit


Private Sub Worksheet_Activate()
    MsgBox "Hi"
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            MsgBox "Hi"
        End If
    End If
End Sub

The top macro will show a msgbox when the sheet is loaded. You go to sheet2, then come back to sheet1.

The bottom macro will show a msgbox if you click on cell D4.

 

[TotallyConfused]

I put the following code in (the macro area of) Sheet 2

Sub Autpen()
Sheets("Home").Activate
MsgBox "Welcome "

End Sub

However, nothing happens when I click into Sheet 2.

Hello Profklein

If I'm reading your question correctly, you want a macro that will automatically run the moment you click on the tab for Sheet2. That is quite easy to accomplish. I'm not sure what you mean when you said you put your code in '(the macro area)' and this could be your problem. To make a macro run automatically follow these steps:

1 ) Click on the sheet tab with the RIGHT mouse button. (you MUST use the RIGHT mouse button)
2 ) Select VIEW CODE from the menu that pops up with the LEFT mouse button.
3 ) In the blank Visual Basic editor window, paste this code.

           Private Sub Worksheet_Activate()
                   MsgBox "Welcome"
           End Sub

4 ) Save and close this code window.
5 ) To test this, click on another sheet tab, then click on the Sheet2 tab as you normally would.
6 ) You should see the message of "Welcome" appear.

I hope this is what you are looking for.

TotallyConfused

 

[profklein]

Hello Profklein

If I'm reading your question correctly, you want a macro that will automatically run the moment you click on the tab for Sheet2. That is quite easy to accomplish. I'm not sure what you mean when you said you put your code in '(the macro area)' and this could be your problem. To make a macro run automatically follow these steps:

1 ) Click on the sheet tab with the RIGHT mouse button. (you MUST use the RIGHT mouse button)
2 ) Select VIEW CODE from the menu that pops up with the LEFT mouse button.
3 ) In the blank Visual Basic editor window, paste this code.

           Private Sub Worksheet_Activate()
                   MsgBox "Welcome"
           End Sub

4 ) Save and close this code window.
5 ) To test this, click on another sheet tab, then click on the Sheet2 tab as you normally would.
6 ) You should see the message of "Welcome" appear.

I hope this is what you are looking for.

TotallyConfused

This is exactly what I was looking for. Totally confused - you are NOT confused at all.

Gene

 

[TotallyConfused]

This is exactly what I was looking for. Totally confused - you are NOT confused at all.

Gene

Hello Profklein

Thank you for your feedback. I'm glad I was able to solve your problem. As far as me being 'not confused', I'm not so sure about that. I'm a relative new-comer to Excel and VBA, so compared to some of the other people here, I don't know a single thing about either one. I try to spend time here everyday and study the questions/solutions offered, and there is hardly a day goes by I don't see a command or series of commands used that I'd never heard of before. This site is a virtual goldmine of information.

TotallyConfused (yes, I still am )