Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2011
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Userform - Make fields visible based on the number chosen from spinbutton

    Hi uber geniuses!

    I've got a couple of queries, but I've separated them across different threads - I hope that's ok! I thought it would make it easier.

    For this one, I need to only make visible the LabelDirector(number) and txtDirector(number) fields appropriate for the number chosen by the spinbutton at the top of the Directors page.

    I started out with an If statement (if txtDirectorsAsk.Value = 1 then txtDirector2.Visible = False, LabelDirector2.Visible = False, etc), but that got too long and cumbersome, so Iím sure thereís another way.

    Iíll need a similar thing for the first Guarantors page, but I can just base that on this once Iíve got it sorted!

    I've attached my test database so you can see what I mean. I know my coding is horrible - forgive me, I'm a beginner!

    Help!.zip

  2. #2
    New Lounger
    Join Date
    Nov 2010
    Location
    Denver
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This will work; brute force but effective

    This is my first reply on this forum and it's very confusing how to do it. I hope I haven't made any gaffe, like putting it in twice.
    Anyhow, below will do what you want. Pasting code here is a chore. I got it working in you Help.zip, then pasted it here. I pasted without any formatting, and it took me longer to format it here to make it pretty than it took me to write and debug it!
    Code:
    Private Sub txtDirectorsAsk_Change()
    Call subDisableAllDirectors
    Select Case txtDirectorsAsk.Value
        Case 1
            txtDirector1.Visible = True
        Case 2
            txtDirector1.Visible = True
            txtDirector2.Visible = True    
        Case 3
            txtDirector1.Visible = True
            txtDirector2.Visible = True
            txtDirector3.Visible = True    
        Case 4
            txtDirector1.Visible = True
            txtDirector2.Visible = True
            txtDirector3.Visible = True
            txtDirector4.Visible = True    
        Case 5
            txtDirector1.Visible = True
            txtDirector2.Visible = True
            txtDirector3.Visible = True
            txtDirector4.Visible = True
            txtDirector5.Visible = True
        Case 6
            txtDirector1.Visible = True
            txtDirector2.Visible = True
            txtDirector3.Visible = True
            txtDirector4.Visible = True
            txtDirector5.Visible = True
            txtDirector6.Visible = True
    ' and so on...
    
    End Select
    End Sub
    
    Sub subDisableAllDirectors()
        txtDirector1.Visible = False
        txtDirector2.Visible = False
        txtDirector3.Visible = False
        txtDirector4.Visible = False
        txtDirector5.Visible = False
        txtDirector6.Visible = False
        txtDirector7.Visible = False
        txtDirector8.Visible = False
        txtDirector9.Visible = False
        txtDirector10.Visible = False
        txtDirector11.Visible = False
        txtDirector12.Visible = False
        txtDirector13.Visible = False
        txtDirector14.Visible = False
    End Sub
    Last edited by macropod; 2012-09-23 at 01:47. Reason: Added code tags

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Dave,

    Actually, posting code here is a snack - simply use the code tags (on the Advanced tab) and put your pre-formatted code between them. None of your laborious reformatting is required. I've applied the code tags, instead of your formatting, to your post.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi ,

    For the directors, I'd suggest setting:
    • the default 'txtDirectorsAsk' value to 1;
    • all of 'txtDirector' 2-14 visibility to false;
    • all of 'LabelDirector' 2-14 visibility to false; and
    • using code along the lines of:
    Code:
    Private Sub SBDirectorsAsk_Change()
    Dim i As Long, j As Long
    'Links the SpinButton to the TextBox
    txtDirectorsAsk.Text = SBDirectorsAsk.Value
    j = SBDirectorsAsk.Value + 1
    If j = 15 Then j = 14
    For i = SBDirectorsAsk.Value To j
      If i = SBDirectorsAsk.Value Then
        Me.Controls("txtDirector" & i).Visible = True
        Me.Controls("LabelDirector" & i).Visible = True
      Else
        Me.Controls("txtDirector" & i).Visible = False
        Me.Controls("LabelDirector" & i).Visible = False
      End If
    Next
    End Sub
    The only thing left for you to decide is what to do if a 'txtDirector' box has data and the user then tries to make it invisible. Do you:
    • force them to erase the data first;
    • erase the data programmatically;
    • retain the data for re-display if the # is increased again - and not write it out if it's not visible.
    Last edited by macropod; 2012-09-23 at 03:26.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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