invisible button?

[kgkev]

I have a sheet that when unprotected acts normally.

However when it is protected if I click anywhere on the sheet it it takes me to another sheet by running the macro - "viewnotordered()"

However on the sheet I do not have any buttons.

Also If I scroll down so row A is not visible this effect goes way.

It comes back as soon as row A is visible.

I dont have any buttons on the sheet so a "Delete all buttons" macro solution might work if someone could suggest the code.

Any ideas?

 

[Colin Legg]

Right click on the sheet's tab (at the bottom) and choose View Code.
Is there a Worksheet_SelectionChange() event handler in the VBE?

If not, is there a Workbook_SheetSelectionChange() event handler in the ThisWorkbook class module?

 

[kgkev]

No.

The event also doesn't happen on change - only on click (if A1 is visible)

The only code attached to this sheet is

Option Explicit


Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Sheets("log").unprotect password:="this is a secret"

With Sheets("log").Range("A56000").End(xlUp).Offset(1)
    .Value = Date & " " & Time
    .Offset(, 2).Value = Date & " " & Time
    .Offset(, 3).Value = "complete"
    .Offset(, 4).Value = "Print Report"
    .Offset(, 5).Value = Environ("USERNAME")
    
End With


       Sheets("log").Protect password:="this is a secret"

End Sub

 

[Colin Legg]

The event also doesn't happen on change - only on click (if A1 is visible)

SelectionChange does not get raised when the cell is changed; it is raised when a different cell is selected (ie. clicked).

The Workbook_BeforePrint() event handler would be in the ThisWorkbook class module. Have you double checked the sheet's class module?

 

[kgkev]

Another clue - When I hover my mouse anywhere on the sheet the cursor changes from an arrow to a finger.

The code below was from the Sheet class module

the code in the ThisWorkbook Modules is

Option Explicit

Private Sub Workbook_Open()
Application.ScreenUpdating = False
If Hour(Now) = 21 Then


    Sheets("log").unprotect password:="this is a secret"

With Sheets("log").Range("A56000").End(xlUp).Offset(1)
    .Value = Date & " " & Time
    .Offset(, 2).Value = Date & " " & Time
    .Offset(, 3).Value = "complete"
    .Offset(, 4).Value = "Auto Open"
    .Offset(, 5).Value = Environ("USERNAME")
    
End With


       Sheets("log").Protect password:="this is a secret"
    SuggestBOMS
    getcosts
    Me.Close True
    Application.Quit
Else



    Sheets("log").unprotect password:="this is a secret"

With Sheets("log").Range("A56000").End(xlUp).Offset(1)
    .Value = Date & " " & Time
    .Offset(, 2).Value = Date & " " & Time
    .Offset(, 3).Value = "complete"
    .Offset(, 4).Value = "Manual Open"
    .Offset(, 5).Value = Environ("USERNAME")
    
End With


       Sheets("log").Protect password:="this is a secret"

End If


End Sub


'Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Application.ScreenUpdating = False
'If Cancel = False Then'
'
 '   Sheets("log").unprotect password:="this is a secret"

'With Sheets("log").Range("A56000").End(xlUp).Offset(1)
'    .Value = Date & " " & Time
'    .Offset(, 2).Value = Date & " " & Time
'    .Offset(, 3).Value = "complete"
'    .Offset(, 4).Value = "Print " & ActiveSheet.Name
'    .Offset(, 5).Value = Environ("USERNAME")
'
'End With


    '   Sheets("log").Protect password:="this is a secret"
'End If
'End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Dim WS As Worksheet
If Sheets("input").Shapes("Button 15").TextFrame.Characters.Text <> "Admin Mode" Then
    
    
    For Each WS In Worksheets
        WS.Visible = True
        WS.unprotect password:="this is a secret"
    Next WS
    Sheets("input").Shapes("Button 15").TextFrame.Characters.Text = "Admin Mode"
    With Sheets("log").Range("A56000").End(xlUp).Offset(1)
        .Value = Date & " " & Time
        .Offset(, 3).Value = "incomplete"
        .Offset(, 4).Value = "Save & Exit Admin"
        .Offset(, 5).Value = Environ("USERNAME")
        .Offset(, 2).Value = Date & " " & Time
        .Offset(, 3).Value = "complete"
    End With
    For Each WS In Worksheets
        If WS.Name <> "Input" Then
            WS.Visible = False
            WS.Protect password:="this is a secret"
        End If
    Next WS
    Sheets("Report").Visible = True

Else
    Sheets("log").unprotect password:="this is a secret"
    With Sheets("log").Range("A56000").End(xlUp).Offset(1)
        .Value = Date & " " & Time
        .Offset(, 3).Value = "incomplete"
        .Offset(, 4).Value = "Save"
        .Offset(, 5).Value = Environ("USERNAME")
        .Offset(, 2).Value = Date & " " & Time
        .Offset(, 3).Value = "complete"
    End With
End If
End Sub

Sub WorkBook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim WS As Worksheet
If Sheets("input").Shapes("Button 15").TextFrame.Characters.Text <> "Admin Mode" Then

'    Sheets("input").Shapes("Button 15").TextFrame.Characters.Text = "Admin Mode"
    For Each WS In Worksheets
        WS.Visible = True
        WS.unprotect password:="this is a secret"
    Next WS
    With Sheets("log").Range("A56000").End(xlUp).Offset(1)
        .Value = Date & " " & Time
        .Offset(, 3).Value = "incomplete"
        .Offset(, 4).Value = "Exit & Exit Admin"
        .Offset(, 5).Value = Environ("USERNAME")
        .Offset(, 2).Value = Date & " " & Time
        .Offset(, 3).Value = "complete"
    End With
    For Each WS In Worksheets
        If WS.Name <> "Input" Then
            WS.Visible = False
            WS.Protect password:="this is a secret"
        End If
    Next WS
    Sheets("Report").Visible = True

Else
    Sheets("log").unprotect password:="this is a secret"
    With Sheets("log").Range("A56000").End(xlUp).Offset(1)
        .Value = Date & " " & Time
        .Offset(, 3).Value = "incomplete"
        .Offset(, 4).Value = "Exit"
        .Offset(, 5).Value = Environ("USERNAME")
        .Offset(, 2).Value = Date & " " & Time
        .Offset(, 3).Value = "complete"
    End With
    Sheets("log").Protect password:="this is a secret"
End If
End Sub

But I searched for "viewnotordered" and its not there!!

 

[kgkev]

I have run another test now.

i have copied the sheet to another book Deleted all macros in the sheet module,

I have no modules & No thisworkbook macros.

if I unprotect the sheet it is normal.

If it is protected then clicking it attempts to run the macro. (it opens the original book to try this now)

I have no idea what to try next? Any ideas?