Activating a different worksheet using VBA does not change the focus on it permanently

0
votes

I want to select and modify different worksheets programmatically every time the workbook is saved. At the end however, I want to set the focus on a particular worksheet so that the workbook is saved with that particular worksheet in focus. What I'm noticing is that whenever the code executes it activates the worksheets, modifies them but at the end it goes back to the worksheet that I had selected before running the code. Here's my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Sheets(1).Activate
   Debug.Print Sheets(1).Name
End Sub

The code above is executed in an empty, local workbook with 2 empty worksheets Sheet1 and Sheet2. Whenever I save the workbook with Sheet2 selected, I see that it is indeed activated because the console log prints Sheet1, in the workbook however, the selected worksheet remains Sheet2.I'm using SAP's BusinessObjects Analysis but as noted above, the workbook is a local macro-enabled workbook that is not saved on the SAP NetWeaver platform.

Is it possible for me to permanetly set the focus to a different worksheet so that it's visible in the workbook?

Thanks

EDIT:

Oh no!!! I have the annoying problem of inconsistent behavior with the different save buttons once again and that is yet to be resolved! I just realized that if I save through the workbook save button the sheet permanently changes, however when I save through the code editor it doesn't. The previous problem I had experienced was on workbooks saved on SAP NetWeaver where VBA code is not executed through the workbook save button but is, through the code editor save button. I guess I will have to log an Oss with SAP for this inconsistency.

3
vbaexcelbusiness-objects
"Whenever I save the workbook with Sheet2 selected, I see that it is indeed activated because the console log prints Sheet1, in the workbook however, the selected worksheet remains Sheet2" - did you mean this? It is confusing. – SJR
The code works for me. Does your workbook include any other code that changes the current tab? – David Rushton
@SJR I meant to say that whenever I select Sheet2 in the workbook and then save the workbook, the code switches to Sheet1, but it doesn't remain selected permanently and I practically only ever see Sheet2 selected because Sheet1 is selected and unslected in a flash. – Dimitar Stratiev
@destination-data, there's no other except the code posted above – Dimitar Stratiev
Please could you amend your question to clarify then? You do realise that Sheet1 is not necessarily the same as Sheets(1). The latter is the index which is the position of the sheet in the workbook. I have no idea if that is the problem here though. I think as others have said the problem must be other code in the file. – SJR

3 Answers

0
votes

What you are saying is only possible, if someone has written:

Private Sub Worksheet_Activate()
    Sheets(2).Activate
End Sub

At Worksheets(1).

Otherwise, the code you are using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Sheets(1).Activate
   Debug.Print Sheets(1).Name
End Sub

should activate the first Sheet and it should not be changed later.

0
votes

I have no idea where Sheet2 is compared to Sheet1.

You say

The code (above) is executed in an empty, local workbook with 2 empty worksheets Sheet1 and Sheet2. Whenever I save the workbook with Sheet2 selected, I see that it is indeed activated because the console log prints Sheet1, in the workbook however, the selected worksheet remains Sheet2

Your code doesn't do anything to a sheet called Sheet2. It only looks at the first sheet in the tab order - the sheet could be called anything.
It activates the first sheet and then puts the name of the first sheet in the immediate window.

This code will select the sheet with the tab name Sheet2, it will then put the name of the activesheet (Sheet2) in cell A1 of the sheet with the tab name Sheet1.
Finally it selects the sheet with the codename Sheet3 (The codename is the name not in brackets in the Project Explorer).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With ThisWorkbook

        .Worksheets("Sheet2").Select
        .Worksheets("Sheet1").Range("A1") = ActiveSheet.Name

    End With

    Sheet3.Select

End Sub
0
votes

Just use following code:

Sub activateSheet(sheetname As String)
'activates sheet of specific name you want.
    Worksheets(sheetname).Activate
End Sub

then for select another sheet:

Sub activateSheet(sheetname As String)
'selects sheet of specific name you want.
    Sheets(sheetname).Select
End Sub

Regards

Xsi