Solved How To Run A Macro When Sheet is activated?

April 8, 2019 at 07:44:21
Specs: Windows 10
This code runs partially. The sheet is activated but the subroutine with the dates doesn't entry the dates in the range of the sheet activated (D3:D8). Here you go the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim response As Double
Dim sourceBook As Workbook
Dim wsNew As Worksheet
 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
  If Sheet1.Range("B2").Value = "2" And ShtExist("Test") Then
   response = MsgBox("Nutrition care plan already exists. Would you like to revaluate it?", _
                      vbYesNo + vbQuestion)
    If response = vbYes Then
    ActiveWorkbook.Sheets("Test").Activate
    With Sheets("Test")
    
'Determine next empty row in Sheets(2).Range("D3:D8")
       nxtRw = WorksheetFunction.CountA(.Range("D3:D8")) + 1

'If D3:D8 is not full, place current date in next empty row
         If nxtRw < 9 Then
           .Range("D" & nxtRw) = Date
           
'If D3:D8is full, Shift dates up one row, Place current date in D8
         Else
           .Range("D3:D8").Copy .Range("D3")
           .Range("D8") = Date & "a"
         End If
         
    End With
  End If
 Else
 Set sourceBook = Workbooks.Open("C:\Users\Tom\Desktop\Book5.xlsx")
    sourceBook.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    wsNew.Name = "Test"
    sourceBook.Close
 End If
End If
End Sub 

Function ShtExist(strSheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    ShtExist = False
    On Error Resume Next
    ShtExist = Len(Sheets(strSheetName).Name) > 0
    On Error GoTo 0
End Function

This code works perfectly on a generic Blank Sheet called "Test" but it doesn't work with a sheet copied from another source. So when the sheet "Test" is copied and pasted from the sourcebook I am able to see the sheet copied and activated but the dates are not entried in their range as the code should do and it does with a generic blank sheet called "Test"(something that has not been copied from a source document). The document I want to copy in the active worksheet is a normal excel worksheet with no macro in it but just a table and where column 4 (D) is completely blank. I hope to have been clear. Thanks for your assistance

See More: How To Run A Macro When Sheet is activated?

Reply ↓  Report •

#1
April 8, 2019 at 12:57:47
I'm not quite sure I understand what you are trying to say. Let's define some terms so that we are both on the same page:

First: The code that you included in your post is a Worksheet_Change macro. A Worksheet Change macro is one of many macros that can be triggered by an “Event”. In this case, the Change Event, whereby a change was made to a cell within the worksheet.

There are 2 very important items that are associated with a Worksheet_Change macro.

1 – A Worksheet_Change macro must be stored in the VBA Sheet Module for the sheet in which you want it to run. If you right-click the sheet tab for the sheet in which you want it to run and chose "View code", the VBA editor will open to the sheet module for that sheet. If the code isn't there, it will never run for that sheet.

2 – A Worksheet Change macro will run (i.e. execute) whenever any change is made to the sheet. Whether or not you see anything happen depends on what change the code is testing for. For example, your code is testing for a change to B2. If you make any change to the sheet, the code will run. The first instruction that it will execute is to determine if the change was made to B2:

If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then

If the change was made to B2, then the code will continue to the next instruction, then the next, etc. If change was not made to B2, then the If instruction shown above will be False and the code will jump down to the associated End If and exit.

To see this for yourself, put a breakpoint next to that instruction and then make any change to the Sheet. The code will begin to execute and the VBA editor will highlight the line with the breakpoint then wait for you to take further action

Next, let’s take a look at the Subject Line of your post:

"How To Run A Macro When Sheet Is Activated?"

Again, the code you posted is a Worksheet Change macro. If you want the code to run when you Activate the sheet, then you need to use a macro that is triggered by the Sheet Activation event:

Private Sub Worksheet_Activate()

  'Your instructions go here.

End Sub

Finally, let’s look at this sentence from your post:

"The document I want to copy in the active worksheet is a normal excel worksheet … "

Do you mean the “The document I want to copy in the active workbook is a normal excel worksheet”? You can’t copy a Worksheet into a Worksheet. You can copy the contents of a Worksheet into a Worksheet or you can copy a Worksheet into a Workbook, but you can’t copy a Worksheet into a Worksheet.

"… with no macro in it "

If there is no macro in the sheet that you are copying into the Workbook, then there is no macro to react to an Event. The Event code must already be in the Sheet module for that sheet if you want the code to execute when the Event (Change, Activate, SelectionChange, Calculate, etc.) occurs.


message edited by DerbyDad03


Reply ↓  Report •

#2
April 9, 2019 at 08:19:35
Thanks a lot for your reply. I have followed through all the advice you gave me and I can see that the problem of the macro lies on

WorksheetFunction.CountA(.Range("D3:D8")) + 1

The macro it is executed but it won't return the date in D3 if the precedent cells D1,D2 are empty.

This is the range I am trying to use for my Test worksheet:

With Sheets("Test")
    nxtRw = WorksheetFunction.CountA(.Range("D3:D15")) + 1
    
    'ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Range("C2").Value = Date
    If nxtRw < 16 Then
           .Range("D" & nxtRw) = Date
    

    'oldDate = Date
    'newDate = DateAdd("m", 1, oldDate)
    'ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Range("C2").Offset(0, 1).Value = newDate
    
     Else
           .Range("D4:D15").Copy .Range("D3")
           .Range("D15") = Date & "a"
         End If

    End With
    ActiveWorkbook.Sheets("Test").Activate


It will work fine with the condition that cell D1, D2 are not empty.

Through the breakpoints I have seen the macro executes well and I have inserted the macro in the view code tab of the sheet as suggested. I would like to leave empty cell D1 and D2. Is it anyway the macro can carry on to insert dates in the range indicated? Many thanks

message edited by TOMMASO


Reply ↓  Report •

#3
April 9, 2019 at 19:09:29
✔ Best Answer
*** Untested ***

Try this:

nxtRw = WorksheetFunction.CountA(.Range("D3:D15")) + 3

The CountA is simply returning the number of items in D3:D15, not a Row number. It knows nothing about Rows. As long as D1:D2 are empty, you need to add 2 to the item count to get that number to match the Row number with the last piece of data. Adding another 1 then gets you to the next empty Row.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
April 9, 2019 at 19:37:07
I still don't know why your subject line says "How To Run A Macro When Sheet Is Activated?"

Once again, you've posted a Worksheet_Change macro, not a Worksheet_Activate macro.

What am I missing?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question