Consulting

Results 1 to 5 of 5

Thread: "This Workbook" script ignored on opening

  1. #1
    VBAX Newbie
    Joined
    Oct 2023
    Posts
    3
    Location

    "This Workbook" script ignored on opening

    Hi,

    I have the following script in "ThisWorkbook", because I need these actions to be performed on opening.

    Private Sub Ouverture()
        Sheets("Gestion").Activate
        Dim ws As Worksheet
        Dim gestion As String
        gestion = ActiveSheet.Name
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> gestion Then
            ws.Visible = xlSheetHidden
            End If
        Next ws
    End Sub
    I know the code works because if I put it in a module and run it, it works perfectly fine. It just doesn't work the way I want it to (on opening). What am I missing here? I feel like this should be pretty simple...

    Thanks!
    Last edited by Aussiebear; 10-25-2023 at 12:26 PM. Reason: Added code tags to supplied code

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Welcome to the forum MyriamSL,

    The name for the sub should be: Private Sub Workbook_Open()

    Private Sub Workbook_Open()
        Dim ws As Worksheet
        
        For Each ws In Sheets
            If ws.Name <> "Gestion" Then
                ws.Visible = xlSheetHidden
            End If
        Next ws
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  3. #3
    VBAX Newbie
    Joined
    Oct 2023
    Posts
    3
    Location
    Great, thanks! I knew it was super simple! So the name of the subs in "ThisWorkbook" need to have specific names then. I already had a Workbook_Open sub (also have a message box on opening), but I just combined the two and everything works fine. Thanks for your help!

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    You are welcome,

    You are correct in what you did, you can only have one workbook_open sub so you need to combine all workbook_open actions within the one sub.

    You can see the different types of code that go into the ThisWorkbook module. Try this:

    1, Goto to VBE (code window)
    2, At the top of the white area (where the code resides) you should see the word '(General)' in a dropdown box
    3, Change that dropdown box to 'Workbook'
    4, Then on the dropdown to the right of the 'Workbook' dropdown should now say 'Open'
    5, If you hit the dropdown where the word 'Open' is, you will see all of the options available to use.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  5. #5
    VBAX Newbie
    Joined
    Oct 2023
    Posts
    3
    Location
    That is very good to know! I'm used to work with modules, but not with this. This information will be handy that's for sure! Thanks again!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •