activate sheet not always calling for form...

[albertc30]

Hi all.

The code bellow seems to be working randomly.

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.

 

[Yongle]

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 )

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

Private Sub Worksheet_Activate()
    Const pWord = "******"
    Me.Unprotect pWord        
    [COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
    Columns("A:R").Select
    ActiveWindow.Zoom = True
    Range("F14").Select
    [COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
    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 )

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

 

[albertc30]

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.