DialogSheets("Name").Labels.Visible = True not working on all DialogSheets in Excel VBA

user_1134726 0
Viewed 394

I've been given an ancient but, for the most part, working Excel solution. It uses a lot of DialogSheets (an Excel 5/95 technique before UserForms).

Now I'm trying to figure out what the author tried to do, the code is pretty horrible. One problem I stumbled across is this:

Some sheets work fine with code like:

DialogSheets("Name1").Show
DialogSheets("Name1").Labels.Visible = True
DialogSheets("Name1").EditBoxes.Visible = True
DialogSheets("Name1").CheckBoxes.Visible = True
DialogSheets("Name1").DropDowns.Visible = True

On others, I get a:

runtime error 1004 "Unable to set the Visible property of the Labels class"

I cannot quite work out the difference, and the documentation on Dialogsheets is pretty sparse. If anyone had any idea I'd appreciate a suggestion.

Update

The answers suggest that some DialogSheets may not have Labels, thus throwing the 1004. This is not the case. This happens in the ImmediateWindow:

?DialogSheets("Name1").Labels.Count
205 
?DialogSheets("Name2").Labels.Count
7 
DialogSheets("Name1").Labels.Visible = True ' Throws 1004
DialogSheets("Name2").Labels.Visible = True ' executes fine
?DialogSheets("Name2").Labels.Visible = True ' returns true
True
vba excel
2 Answers

The code above sets the visibility of the Labels, DropDowns, EditBoxes and CheckBoxes to True.

It throws error 1004, because on some DialogSheets the Labels, DropDowns, EditBoxes or CheckBoxes are missing. And it works on those, where all 4 are present.

To see it working, close all Excel applications. Then open a brand new one and run this code:

Sub TestMe()

    Dim someDialogSheet As DialogSheet
    Set someDialogSheet = ThisWorkbook.Sheets.Add(Type:=xlDialogSheet)

    With someDialogSheet
        .Name = "SoReadyToHelp"
        .Labels.Add(5, 5, 5, 5).Select
        .DropDowns.Add(10, 10, 10, 10).Select
        .CheckBoxes.Add(15, 15, 15, 15).Select
        Debug.Print .Labels.Visible
        Debug.Print .CheckBoxes.Visible
        Debug.Print .DropDowns.Visible
        .Show
    End With

End Sub

Or there is On Error Resume Next somewhere on the code where it works.

user_54486260

The code will fail if there are no labels to make visible.
Add a check like this, to make sure there are any before making them visible: 

If DialogSheets("Name1").Labels.Count > 0 Then
    DialogSheets("Name1").Labels.Visible = True
End If

Edit after comment
Then I suggest you try something like this:

For Each lbl In DialogSheets("Name1").Labels
    lbl.Visible = True
Next
user_32463620
Related