Results 1 to 11 of 11
  1. #1
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13

    Hiding Text Boxes Based On User Selection VBA

    Heya,

    I'm kinda stuck how do you in the VBA code. How would you hide text boxes based on the user selection with a combo box.




    Kind Regards

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can use the AfterUpdate event of your ComboBox control.

    Let's say that you have a form with a TextBox named Text0 and a ComboBox with a value of Combo2. Here is what the VBA code you would use that if the user selects a value of "Dog" from the ComboBox, it will hide the TextBox.
    Code:
    Private Sub Combo2_AfterUpdate()
        
        If Combo2.Value = "Dog" Then
            Me.Text0.Visible = False
        Else
            Me.Text0.Visible = True
        End If
        
    End Sub
    Note that if you want to apply this logic not only to data entry, but also existing data, that you may need to include this same sort of logic in the Form Load event.

  3. #3
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13
    What happens if add more options in the combo box, I tried this VBA code
    Click image for larger version. 

Name:	1.JPG 
Views:	14 
Size:	138.1 KB 
ID:	25924

    But it didn't work only the dog part work but then when I chose cat in the combo box this happened.
    Click image for larger version. 

Name:	2.JPG 
Views:	14 
Size:	55.3 KB 
ID:	25926

    It came up with the catage, catname, fishname and fishage.

    How do I fix this?
    Attached Thumbnails Attached Thumbnails 2.JPG  

  4. #4
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13
    Also, when I clicked Dog in the combo box, it didn't update to show DogAge and DogName
    It shows this same thing again,
    Click image for larger version. 

Name:	3.JPG 
Views:	14 
Size:	56.0 KB 
ID:	25927

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Follow the code. Walk through the code mentally.

    In the "ELSE" statements, because "Combo2" (poor name for the control, BTW) = "DOG", "Cat" and "Fish" are set to Visible.

    If "Combo2" = "CAT", in the first IF, "Dog" is set to Visible, but then because Combo2 = CAT, Dog is set to hidden (not visible). Then Combo2 is checked to see if it is = "Fish". It is not, so "Fish" is set to visible.

    You need to work on the code logic.
    I might use "Select Case " statements.

    Maybe
    Code:
    Select Case Combo2
    
        Case "Dog"
            Me.CatAge.Visible = False
            Me.CatName.Visible = False
            Me.FishAge.Visible = False
            Me.FishName.Visible = False
            Me.DogAge.Visible = True
            Me.DogName.Visible = True
    
        Case "Cat"
            Me.CatAge.Visible = True
            Me.CatName.Visible = True
            Me.FishAge.Visible = False
            Me.FishName.Visible = False
            Me.DogAge.Visible = False
            Me.DogName.Visible = False
    
        Case "Fish"
            Me.CatAge.Visible = False
            Me.CatName.Visible = False
            Me.FishAge.Visible = True
            Me.FishName.Visible = True
            Me.DogAge.Visible = False
            Me.DogName.Visible = False
    
    End Select

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You need to use nested If Else statements or else all 3 options will run. (just saw ssanfu post, that would work too)

    If Combo2.Value = "Dog" Then
    Me.DogName.Visible = True
    Me.DogAge.Visible = True
    Me.CatName.Visible = False
    Me.CatAge.Visible = False
    Me.FishName.Visible = False
    Me.FishAge.Visible = False
    Else
    If Combo2.Value = "Cat" Then
    Me.DogName.Visible = False
    Me.DogAge.Visible = False
    Me.CatName.Visible = True
    Me.CatAge.Visible = True
    Me.FishName.Visible = False
    Me.FishAge.Visible = False
    Else
    If Combo2.Value = "Fish" Then
    Me.DogName.Visible = False
    Me.DogAge.Visible = False
    Me.CatName.Visible = False
    Me.CatAge.Visible = False
    Me.FishName.Visible = True
    Me.FishAge.Visible = True
    Else
    Msgbox "No Value Selected"
    Me.DogName.Visible = False
    Me.DogAge.Visible = False
    Me.CatName.Visible = False
    Me.CatAge.Visible = False
    Me.FishName.Visible = False
    Me.FishAge.Visible = False
    End If
    End If
    End If

  7. #7
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13
    Thank you that has helped alot, just to let you know that I'm a beginner at VBA, so thank you very much for your help.

    Do you know the best place to learn VBA programming.

    Thanks

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by something View Post
    Do you know the best place to learn VBA programming.
    Maybe Crystal's site - as a start
    Learn VBA
    http://www.accessmvp.com/Strive4Peace/VBA.htm

  9. #9
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13
    Thank you. Also if when Loading the form how do I get it to just to show only the combo box.


    Thanks ever so much.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In design view, set the text boxes visible property to No.

  11. #11
    something is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    13
    Thanks you very much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-17-2015, 05:40 PM
  2. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  3. Replies: 1
    Last Post: 12-09-2012, 05:27 PM
  4. Replies: 5
    Last Post: 08-07-2012, 11:14 AM
  5. Replies: 0
    Last Post: 12-02-2010, 11:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums