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
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
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.
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.Code:Private Sub Combo2_AfterUpdate() If Combo2.Value = "Dog" Then Me.Text0.Visible = False Else Me.Text0.Visible = True End If End Sub
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
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" ThenMe.DogName.Visible = TrueElse
Me.DogAge.Visible = True
Me.CatName.Visible = False
Me.CatAge.Visible = False
Me.FishName.Visible = False
Me.FishAge.Visible = FalseIf Combo2.Value = "Cat" ThenMe.DogName.Visible = False
Me.DogAge.Visible = False
Me.CatName.Visible = True
Me.CatAge.Visible = True
Me.FishName.Visible = False
Me.FishAge.Visible = FalseElseIf Combo2.Value = "Fish" ThenMe.DogName.Visible = False
Me.DogAge.Visible = False
Me.CatName.Visible = False
Me.CatAge.Visible = False
Me.FishName.Visible = True
Me.FishAge.Visible = TrueElseMsgbox "No Value Selected"Me.DogName.Visible = False
Me.DogAge.Visible = False
Me.CatName.Visible = False
Me.CatAge.Visible = False
Me.FishName.Visible = FalseMe.FishAge.Visible = FalseEnd If
End IfEnd If
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
Maybe Crystal's site - as a start
Learn VBA
http://www.accessmvp.com/Strive4Peace/VBA.htm
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.
In design view, set the text boxes visible property to No.
Thanks you very much!