Consulting

Results 1 to 9 of 9

Thread: Userform is not displaying anything when i change a "box" to visible or not visible

  1. #1
    VBAX Newbie Doug2009's Avatar
    Joined
    Aug 2022
    Location
    Wheaton, Illinois
    Posts
    4
    Location

    Question Userform is not displaying anything when i change a "box" to visible or not visible

    The fields get changed, but when I UserForm1.Show, the form comes up blank. I want to be able to show or hide items in the form as needed.
    I can get it to work if i explicitly type in the code. ie Userform1.Listbox1.Visible = False
    For Multipage ... it will select the proper page and display or not as the program chooses, but that is as far as it goes.

    What am I doing wrong?

        For Each objControl In UserForm1.Controls
                'Sheets("Controls").Columns(1) is a list of items i want to be visible, the rest should be hidden
    
    
                iVisibleFieldsColumn = 0
                iVisibleFieldsColumn = Application.Match(objControl.Name, Sheets("Controls").Columns(1), 0)
                If iVisibleFieldsColumn > 0 Then
                    objControl.Visible = True
                Else
                    objControl.Visible = False
                End If
            End If
        Next objControl

  2. #2
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    416
    Location
    as far as your code is concern, if initially they are Set (True) in the sheet, they will
    be all set to False.

    maybe you add an Optional "control" name as a parameter to the sub/func so you can
    explicitly turn it on/off.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,617
    Best attach a workbook with only the user form and associated code still misbehaving. Then we can be sure when your code runs and how you've set up your user form at design time. Otherwise we're just guessing, and probably guessing wrongly.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Newbie Doug2009's Avatar
    Joined
    Aug 2022
    Location
    Wheaton, Illinois
    Posts
    4
    Location
    Thank you for your comments.

    They are all set true and are reset true or false as my list dictates. The list represents all I wish true (visible) and includes listboxes, textboxes, and command buttons. I have actually tried setting them all false, and that did not change the end result with is the form comes up, but is completely blank. Again, i checked to see if the visible fields get changed, and they do.

  5. #5
    VBAX Newbie Doug2009's Avatar
    Joined
    Aug 2022
    Location
    Wheaton, Illinois
    Posts
    4
    Location
    There are two macros. The one shows the modified userform is: VisibleSwitch the other DisplayFormControls shows all the objects and holds my list of items I wish on, with the others off.
    Attached Files Attached Files

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,783
    Location
    My way
    Dim objControl as Object
    Dim i As Long
    Dim Arr
    
    For each objControl in Userform.Controls
       objControl.Visible = False
    Next
    
    'Use array for speed
    Arr =  Intersect(Sheets("Controls").UsedRange, Range("A:A")).Value
    For 1 = Lbound(Arr, 2) to Ubound(Arr, 2)
       'Use Error for typos, etc.
       On Error Resume Next
       UserForm.Controls(Arr(1, i)).Visible = True
        Error = 0
    Next
    Your way
     For Each objControl In UserForm1.Controls
        objControl.Visible = Not(Application.Match(objControl.Name, Sheets("Controls").Columns(1), 0) = "#N/A")
       Next objControl
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,581
    I think your approach is not correct: don't use a worksheet to determine whether a control in a Userform should be visible or not,
    In Design Mode you should decide which controls should be visible when showing the Userform for the first time (Userform_Initialize).
    If anything should be changed after that you should use the Userform's eventprocedures to adapt the lay-out/visibility in the Userform.

    Your initial question (though based on a wrong concept) could be answered this way:

    Private Sub UserForm_Initialize()
      sn = Sheet1.Cells(1).CurrentRegion
       
      For j = 3 To UBound(sn)
        Me(sn(j, 1)).Visible = sn(j, 3)
      Next
    End Sub

  8. #8
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    416
    Location
    here is what i have.
    i added another sheet and add Table.
    see the Initialize event of the Userform.
    Attached Files Attached Files

  9. #9
    VBAX Newbie Doug2009's Avatar
    Joined
    Aug 2022
    Location
    Wheaton, Illinois
    Posts
    4
    Location
    Thanks to all of you!

    You have no idea how long I struggled with this! Your solutions are simple and elegant! Thank you very much!
    Doug
    Wheaton, IL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •