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

profklein

New Member
Joined
May 20, 2017
Messages
20
I put the following code in (the macro area of) Sheet 2

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


End Sub

However, nothing happens when I click into Sheet 2.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,599
.
Both of these macros go into the sheet macro area :

Code:
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

Board Regular
Joined
May 4, 2017
Messages
212
Office Version
  1. 365
Platform
  1. Windows
I put the following code in (the macro area of) Sheet 2

Sub Auto_Open()
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.
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
 
Last edited:

profklein

New Member
Joined
May 20, 2017
Messages
20
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.
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

Board Regular
Joined
May 4, 2017
Messages
212
Office Version
  1. 365
Platform
  1. Windows
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 :) )
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,434
Messages
5,528,742
Members
409,832
Latest member
Misspears10

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top