Getting Worksheet_Activate to only run when user clicks on the sheet (not at opening file)

[SOLVED]

Hi,

I have a file with several sheets. One of them uses the Worksheet_Activate event. This sub sets Application_EnableEvents to False in the beginning and True at the end. The code works fine. It runs when the sheet is selected by the user.

Now to my problem: When the workbook is opened the Worksheet_Activate code runs. I want the code to only run when the user actually clicks on the sheet.

I have tried to find a way so that the Worksheet_Activate code does not run at opening. I read somewhere that Workbook_Activate does not run until the workbook has finished opening. I don't know if that is true or not but anyway I tried to disable events in Workbook_Open and then enable them again in Workbook_Activate. But no... The disabling works but it is not enabled again when the file has finished opening.

And also: I am worried that I, by messing with events at opening, will mess up something that I am not aware of.

I hope that my description is clear and that someone has a suggestion for how I can solve this.

Thanks in advance.

/Zenita

Can you post a workbook example because the test file I made with activate code on sheet1 does not fire when workbook is opened and sheet1 already is active.

You might have some other code in place causing the activation

The example file does not require any data, only the code relevant to events. But the file must have the problem in it so we can understand what's happening

You can disable events in the BeforeSave event to prevent rerunning code when saving.

But your problem is more about your code corrupting the workbook at some point.
That corruption is cause by data validation list being over 255 characters long.

Public Function addDropDownStr(rng As Range, dropDownFormula As String) As String

    Dim MyList As String
    
    MyList = dropDownFormula
    If Len(dropDownFormula) > 255 Then
        MyList = Left(dropDownFormula, 255)
    End If
    
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
        xlBetween, Formula1:=MyList
        .InCellDropdown = True
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "You have entered a value that is not predefined"
        .ShowInput = True
        .ShowError = False
        colCounter = colCounter + 1
    End With

End Function

You might want to use a sheet to store the list in a range and avoid that problem completely.

I only suggested the change in the BeforeSave event passed on your request, "I want the code to only run when the user actually clicks on the sheet".

I agree that simply truncating the list is not practical, hence the use of cells suggestion, but the code should demonstrate the problem error message does not then appear on re-openning the file.