Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    87

    Unanswered: Combobox to make subforms visible and invisible

    Good morning all,

    I have a puzzle I am trying to solve. The problem: I want to replace 12 command buttons and the resultant long page of code with a combobox and, hopefully,a little less code.

    I am trying to make subforms visible and then invisible again. I am stacking the subforms on a main form and need the real estate. The make visible part is done. The invisible part I cannot figure out. Here is the code I amusing for the combobox that works.

    Code:
    Private Sub cboTestFormOpen_AfterUpdate()
        If Me.cboTestFormOpen = "Address" Then
            Me.sfmMembersAddress.Visible = True
        ElseIf Me.cboTestFormOpen = "Contact" Then
            Me.sfmMembersContact.Visible = True
        ElseIf Me.cboTestFormOpen = "EMT" Then
            Me.sfmMembersEMT.Visible = True
        ElseIf Me.cboTestFormOpen = "Gear" Then
            Me.sfmMembersGear.Visible = True
        ElseIf Me.cboTestFormOpen = "Misc" Then
            Me.sfmMembersMisc.Visible = True
        ElseIf Me.cboTestFormOpen = "Position" Then
            Me.sfmMembersPosition.Visible = True
        ElseIf Me.cboTestFormOpen = "Status" Then
            Me.sfmMembersTeams.Visible = True
        ElseIf Me.cboTestFormOpen = "Team" Then
            Me.sfmMembersTeams.Visible = True
        ElseIf Me.cboTestFormOpen = "Training" Then
            Me.sfmMembersTraining.Visible = True
        End If
        cboTestFormOpen = Null
    End Sub
    I am thinking I need to check to see if any of these are visible and if I make another visible, the currently visible one becomes invisible. Or am I on the wrong track? I do not have any idea at this point.

    Here is the code I am using on the command buttons. This works fine but it is getting unwieldy with 12 buttons. First I set the state of the forms and the buttons:

    Code:
    Private Sub Form_Load()
    ' Set visibility of subforms to hidden on form opening
            Me.sfmMembersAddress.Visible = False
            Me.sfmMembersContact.Visible = False
            Me.sfmMembersEMT.Visible = False
            Me.sfmMembersGear.Visible = False
            Me.sfmMembersMisc.Visible = False
            Me.sfmMembersPosition.Visible = False
            Me.sfmMembersStatus.Visible = False
            Me.sfmMembersTeams.Visible = False
            Me.sfmMembersTraining.Visible = False
            Me.sfmMembersGearAll.Visible = False
            Me.sfmMembersPositionAll.Visible = False
    ' Set the caption of the button for each form to default on form opening
            Me.cmdAddressSfm.Caption = "Address"
            Me.cmdContactSfm.Caption = "Contact"
            Me.cmdEMTSfm.Caption = "EMTs"
            Me.cmdGearSfm.Caption = "Gear"
            Me.cmdMiscSfm.Caption = "Misc."
            Me.cmdPositionSfm.Caption = "Position"
            Me.cmdStatusSfm.Caption = "Status"
            Me.cmdTeamSfm.Caption = "Team"
            Me.cmdTrainingSfm.Caption = "Training"
            Me.cmdGearSfmAll.Caption = "See All"
            Me.cmdPositionSfmAll.Caption = "See All"
    ' Set the font color of the button to black on form opening
            Me.cmdAddressSfm.ForeColor = vbBlack
            Me.cmdContactSfm.ForeColor = vbBlack
            Me.cmdEMTSfm.ForeColor = vbBlack
            Me.cmdGearSfm.ForeColor = vbBlack
            Me.cmdMiscSfm.ForeColor = vbBlack
            Me.cmdPositionSfm.ForeColor = vbBlack
            Me.cmdStatusSfm.ForeColor = vbBlack
            Me.cmdTeamSfm.ForeColor = vbBlack
            Me.cmdTrainingSfm.ForeColor = vbBlack
            Me.cmdGearSfmAll.ForeColor = vbBlack
            Me.cmdPositionSfmAll.ForeColor = vbBlack
    End Sub
    and, then the code for the button:

    Code:
    Private Sub cmdAddressSfm_Click()
    
    ' set Address subform visibility on or off, set button captions, set button font colors
    
        If Me.sfmMembersAddress.Visible = False Then
            
            Me.sfmMembersAddress.Visible = True
            Me.cmdAddressSfm.Caption = "Hide Address"
            Me.cmdAddressSfm.ForeColor = vbRed
    
        ElseIf Me.sfmMembersAddress.Visible = True Then
        
            Me.sfmMembersAddress.Visible = False
            Me.cmdAddressSfm.Caption = "See Address"
            Me.cmdAddressSfm.ForeColor = vbBlack
    
        End If
    
    End Sub
    I there a way to accomplish this and maybe cut down 12 times this much code? It would also cleanup the form by removing the clutter of 12 buttons (YEAH!) and replace them with one combobox (elegant).

    Thanks, Scott

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 32
    'hide it or show based on combo box value:

    subform.visible = cboBox = "Use SubForm"

  3. #3
    Join Date
    Mar 2013
    Posts
    87
    I'm not sure I follow. Do you mean that I should add a line to each of the 12 combox values to make invisible the "SubForm" that is currently visible then make subform from value visible if combobox value does not equal value in combobox used to make the subform in that line visible? If so I do not know how. If not,then I do not understand your answer. Sorry I am confused. Many thanks. Scott

  4. #4
    Join Date
    Mar 2013
    Posts
    87
    Good evening all, just a quick note say thank you for the recent response, but i really don't understand it. How do I apply that to 12 subforms? How do I notate the syntax in the code for the combobox. How do I woord it for multiple subforms. Iwould need to check for several at a time. Have I included enough code for anyone to understand what I am asking? Thank, Scott

  5. #5
    Join Date
    Mar 2013
    Posts
    87
    Please see below, I have attempted to integrate your answer as a test. Selecting "Address" in the combobox now turns the subform to not visible from visible. The reverse of what I had. I am not proficient enough in VBA to figure out how to make an on/off switch. Any direction on what the code I should use to make a switch, if possible.

    Example code (this works to make the subform visible:

    Code:
    Private Sub cboTestFormOpen_AfterUpdate()
    
        If Me.cboTestFormOpen = "Address" Then
           Me.sfmMembersAddress.Visible = True
        End If
    
        cboTestFormOpen = Null
    
    End Sub
    This make it not visible:

    Code:
    Private Sub cboTestFormOpen_AfterUpdate()
    
        If Me.cboTestFormOpen = "Address" Then
               sfmMembersAddress.visible = cboTestFormOpen = "Use sfmMembersAddress"
        End If
    
        cboTestFormOpen = Null
    
    End Sub
    Thanks in advance, Scott

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
  •