VBA run-time error 1004 when double-clicking values in non-visible sheet

[ave10]

In a workbook I have, I would like ALL sheets, except the sheet being used, to be hidden at all times.

In the 'ThisWorkbook' Object I have applied this code:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim MySh As Worksheet


  For Each MySh In ThisWorkbook.Worksheets
      If MySh.Name <> Sh.Name Then MySh.Visible = 0
  Next MySh
End Sub

On multiple sheets within my workbook, I am able to double-click values in the A Column and the values being double-clicked, populate the C column in my main sheet/form in a sheet called "JE". Right now, when I try to double-click on those values in the A column, the message i get it, "Method 'Visible' of object '_Worksheet' failed" and the "...If MySh.Name <> Sh.Name Then MySh.Visible = 0" is highlighted as the issue.

When I double-click on a value in a separate sheet that is meant to populate the main sheet as well as navigate back to the main sheet, it does populate the C column of the main sheet (JE) however, it does not navigate back the main sheet as I would like. That is the main issue.

Does anyone know of a modification I can make to this code to keep ALL of the sheets except the active sheet, hidden, and still allow me to double-click values and have those values populate the C column of my main sheet (JE) as I normally did?

Thanks in advance!

 

[cerfani]

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim MySh As Worksheet

    For Each MySh In ThisWorkbook.Worksheets
        If Not MySh.Visible And MySh.Name <> Sh.Name Then MySh.Visible = 0
    Next MySh
End Sub

just taking a wild guess... untested

 

[ave10]

that worked, sort of. I no longer get the run-time error but, when I'm on the sheets where I double-click values, it does not return to the main sheet (JE) after double-clicking the values. The values themselves populate the main sheet properly but it doesn't switch sheets. Any idea on how I can get that functionality to work?

 

[cerfani]

after looking at my post i think i meant to have the code set visible to 0 if the sheet IS visible... so remove the Not

try that, maybe that is the bug

i read your OP but it is hard to picture what you are doing with your vba code so it would be hard for me to say what exactly is wrong

 

[ave10]

When I keep the 'Not' it works well. The run-time error doesn't pop-up and when I double-click values in other sheets, they populate my main sheet/form, as they should.

The only issue left is when I double-click a value, it populates the cells in the C column of sheet "JE" but, doesn't navigate back to the sheet itself. That seems to be the only issue left.

Here is an example of the code I have for one of the sheets that has values I double-click, maybe it will be of some help:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Target.Column = 1 Then
    For j = 7 To 447
        If Worksheets("JE").Range("C" & j).Value = "" Then
             Worksheets("JE").Range("C" & j).Value = ActiveCell.Value
             Worksheets("JE").Activate
             Exit For
        End If
     Next j
End If
Sheets("JE").Visible = False
Cancel = True


Worksheets("JE").Activate
Application.ScreenUpdating = True


End Sub

 

[cerfani]

Maybe it avoids the error but it doesn't make sense... "if your sheet is not visible then set visible to 0" . If a sheet is not visible then setting the visible property to 0 will have no effect. With Not, it is stopping visible sheets from getting set to 0. It seems that only "JE" sheet is activated and is Sh Object in the activate event. So you are trying to set every sheet that is not "JE" to invisible? If yes then you would want to check for Visible sheets and remove the Not.

You probably are doing something wrong with Visible. If it is boolean, then try using True/False. VBA should be able to handle numbers for boolean values but not sure. If I know the type used I use it instead of making the compiler correct it or force a conversion.

 

[cerfani]

if you use this... XlSheetVisibility Enumeration (Excel)

then the code could be...

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim MySh As Worksheet

    For Each MySh In ThisWorkbook.Worksheets
        If MySh.Visible = -1 And MySh.Name <> Sh.Name Then MySh.Visible = 0
    Next MySh
End Sub

i think your error was vba not being able to handle the enum value -1 in the If condition for a boolean but with Not it coerced it to convert to boolean

 

[ave10]

So, I changed the code in my 'ThisWorkbook' Object to this:

Sub hideAllSheets()


    Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets


    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then


    ws.Visible = xlSheetHidden
    End If


    Next ws
End Sub

no error message but, on one sheet where I double-click account values in a sheet called "acct_codes" when I double-click a value it populates the main sheet but does not navigate back to the main sheet ("JE"). The acct_code vba is what I posted in post #5.

Thanks!

 

[ave10]

I added:

Worksheets("JE").Visible = 1
Application.ScreenUpdating = True

after the "Cancel = True and that seemed to work.