Failure of RANGE class SELECT method

Asked

Viewed 3,521 times

0

The intention is that by clicking on the Sintese Euro tab, the macro would impute the "L" character in cell D1 of the Synthesis Local tab and then return to the Euro Synthesis tab. I was able to solve the error problem by imputing the macro in the module. So I just inserted a command in the Sintese Euro page to call the macro:

Then on the Euro Synthesis page I entered the code:

Private Sub Worksheet_Activate()
Call Module1.Macro1
End Sub

In the Module I inserted the macro code

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

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

Is there any function that activates only when I click on the tab and not in all ways to access it as is the case of Private Sub Worksheet_Activate()?

And that’s the line of error: 

Range("D1").Select

inserir a descrição da imagem aqui

Thank you, guys.

xml excel vba excel-vba
  • 1

    Where are you entering the code Private Sub Worksheet_Activate()? In a module? Could you insert a print of the VBA Project Tree.

  • So, I don’t understand anything even of VBA, but the code is inside the "Microsoft Excel Objects".

  • 1

    The event is being triggered in the right way. Does cell D1 have a Listbox or is it a common cell? Because a test with this code was performed and worked correctly.

  • So the cell has a listbox yes.

3 answers

3

You do not need to select Sheets("SINTESE_LOCAL") or cell "D1". Exchange your Macro1 code for:

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

1

Event Worksheet_activate

To use this event without looping in, you cannot use the .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 spreadsheet SINTESE_LOCAL 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

-2

The mistake:

plan3.range("G4").select

The message: RANGE CLASS SELECT METHOD FAILED!

What was done (in place of the line above):

plan3.range("G4").select

Is made:

DIM WsA AS worksheet
SET wSA= Worksheets("nome da plan3")
WsA.select
WsA.Range("G4").Activate

Browser other questions tagged xml excel vba excel-vba

You are not signed in. Login or sign up in order to post.