RANGE class SELECT method failed

Question

RANGE class SELECT method failed

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

The intention is that when you click on the Euro Synths tab, the macro impute the "L" character in cell D1 of the Local Synthesis tab and then return to the Euro Overview tab. I was able to solve the error problem by allocating the macro in the module. So I just entered a command on the Euro Synth page to call the macro:

Then on the Euro Overview page I inserted the code:

Private Sub Worksheet_Activate()
Call Module1.Macro1
End Sub

In the Module insert the macro code

Sub Macro1()
Sheets("SINTESE_LOCAL").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "L"
Sheets("SINTESE_EURO").Select
End Sub

The problem is that since I used the command Private Sub Worksheet_Activate() , it understands that all the ways to access the tab can trigger the macro, and as I put the command Sheets("SINTESE_EURO").Select , in Macro it ends up in looping.

Is there a function that activates only when I click on the tab and not in all ways to access it, such as Private Sub Worksheet_Activate() ?

And this is the error line:

Range("D1").Select

Thank you, people.

excel xml vba excel-vba

asked by anonymous 24.07.2018 / 15:55

2 answers

2597 - Uri Online Judge - C ++ Button does not run Javascript

score 3 · Answer 1

3

You do not have to select Sheets ("SYNTHESE_LOCAL") or cell "D1". Change the code for your Macro1 by:

Sub Macro1()
    Sheets("SINTESE_LOCAL").Range("D1").Value= "L"
End Sub

01.08.2018 / 01:57

score 1 · Answer 2

Worksheet_Activate Event

To use this event without looping, you can not use .Select within the event.

In fact, .Select / .Activate / .ActiveCell should be avoided almost always (except to activate events or other occasions that is possible only with Select).

Then you declare the SINTESE_LOCAL worksheet and insert the value into the cell or range as follows.

Private Sub Worksheet_Activate()
    Dim SINTESE_LOCAL As Worksheet
    Set SINTESE_LOCAL = ThisWorkbook.Worksheets("SINTESE_LOCAL")
    SINTESE_LOCAL.Range("D1") = "L"
End Sub

You activate the Macro as follows:

Sub Macro1()
    Sheets("SINTESE_LOCAL").Select
    Sheets("SINTESE_EURO").Select
End Sub