Hide labels based on a value when userforms loads

[gmazza76]

Good afternoon,

I am trying to pull information from a spreadsheet into an Excel Userform, but I am having trouble on how to hide some of the none relevant data that is in label form that maynot be needed to be visible on the form.

So as the userform has 40 ish labels that dont always need to be visible I am hoping to update the relevant ones and hide the none relevant based on a number in my worksheet "Data" as below.
If "B1" is 1 as per below I need the labels below to show data, but the rest of the labels to be removed. I have put only 1 below but it could be 10 plus so I am unsure if I can hide them all as one in an array?

Sorry for being a bit vague, but I am unsure how to proceed.

Private Sub UserForm_Initialize()

Q1LoadFig = Worksheets("Data").Range("B1")

If Q1LoadFig >= 1 Then

Label3.Caption = Worksheets("pop Up Data").Range("B4").Text
Label27.Caption = Worksheets("pop Up Data").Range("B5").Text
Label28.Caption = Worksheets("pop Up Data").Range("B6").Text
Label29.Caption = Worksheets("pop Up Data").Range("B7").Text
Label30.Caption = Worksheets("pop Up Data").Range("B8").Text
Label31.Caption = Worksheets("pop Up Data").Range("B9").Text
'hide the following labels
Q1.Label14.Visible = False
End sub

thanks in advance
Gavin

 

[Akuini]

Try something like this:

For Each a In Split("2 4 10 14 66")
Me.Controls("Label" & a).Visible = False
Next

 

[gmazza76]

Thanks for this Akuini,

The above code works if I put it in after the "if" statement prior to the label updates, but is there any way I can do this in the if part outside the update.
I have tried something like the below, but I keep getting errors and am unsure what direction to go in.

'Proj 1.1
If Q1LoadFig = 1 Then
    For Each a In Split("9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
'Unsure if this should be "end if" , "ElseIf" or something else
If Q1LoadFig = 2 Then
    For Each a In Split("26 25 24 23 22 21 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False

If Q1LoadFig = 3 Then
    For Each a In Split("26 25 24 23 22 21 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
    End If
If Q1LoadFig = 4 Then
    For Each a In Split("26 25 24 23 22 21 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
    End If
If Q1LoadFig = 5 Then
    For Each a In Split("26 25 24 23 22 21 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
    End If
    Next
        'Complete data upload into Userform
        Label3.Caption = Worksheets("pop Up Data").Range("B4").Text
        Label27.Caption = Worksheets("pop Up Data").Range("B5").Text

thanks

 

[Akuini]

Try something like this:

Select Case Q1LoadFig

Case 1
    For Each a In Split("9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
    Next

Case 2, 3, 4, 5
   For Each a In Split("26 25 24 23 22 21 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61")
        Me.Controls("Label" & a).Visible = False
    Next

End Select

        'Complete data upload into Userform
        Label3.Caption = Worksheets("pop Up Data").Range("B4").Text
        Label27.Caption = Worksheets("pop Up Data").Range("B5").Text

 

[gmazza76]

thank you this worked a treat

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.