Results 1 to 3 of 3

Thread: activate sheet not always calling for form...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default activate sheet not always calling for form...

    Hi all.

    The code bellow seems to be working randomly.

    Code:
    Private Sub Worksheet_Activate()    
             Sheets("customers").Unprotect Password:="******" 'UnProtects the customers sheet.
        
        Columns("A:R").Select
        ActiveWindow.Zoom = True
        Range("F14").Select
        
             Sheets("customers").Protect Password:="******" 'Protects the customers sheet.
             
        CustForm.Show
    
    
    End Sub
    The goal here is every time user click the customers sheet, the form customers loads up.

    Many thanks.

    Cheers.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,876
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: activate sheet not always calling for form...

    Your code is triggered when the sheet is activated but your code leaves the sheet active - clicking does nothing if the sheet is already active
    - probably explains apparently random behaviour

    either
    - activate a different sheet BEFORE user wants the userform to pop up again (eg when the userform is unloaded )
    or
    - add an additional trigger ( eg when any cell in columns A to R is clicked on by user as demonstrated below )

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Not Intersect(Columns("A:R"), Target) Is Nothing Then custform.Show
    End Sub
    but this would require Worksheet_Activate to be tailored to prevent unnecessary multiple triggers

    Code:
    Private Sub Worksheet_Activate()
        Const pWord = "******"
        Me.Unprotect pWord        
        Application.EnableEvents = False
        Columns("A:R").Select
        ActiveWindow.Zoom = True
        Range("F14").Select
        Application.EnableEvents = True
        Me.Protect pWord      
        custform.Show
    End Sub
    Consider forcing a different sheet to be active when workbook is opened
    place in ThisWorkbook module ( not a standard or sheet module )
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Sheets("othersheet").Activate
    End Sub
    
    Private Sub Workbook_Open()
        Sheets("othersheet").Activate
    End Sub
    How you tackle this depends on what the user is required to do and also what needs to happen when the userform is active and after it has been hidden or unloaded etc

  3. #3
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: activate sheet not always calling for form...

    Hi Yongle.

    What I meant to say was not necessarily click on an already opened sheet.

    When they do that, the code runs first time and opens the form. Then when they close the userform then they would go to another sheet say Invoices.

    Then when coming back to customers by clicking on the sheet, code should run again?

    Many thanks.

    I shall give your code a try.

    Many thanks for your time and help.

    Cheers.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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