Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    MultiPage UserForm (XL2002)

    I need help with the code that will disable page 10 of a userform until a certain condition is met within the spreadsheet.

    Specifically, I want page10 of the form to become enabled when the range C1:C400 contains 35 (or more) numerical entries. I can use <font color=blue>=Count(C1:C400)</font color=blue> in cell C401 and could base the enabling of Page10 on that one cell.

    FWIW, the range C1:C400 would become filled with data from that very userform and it will always be numerical - I just want an additional page of the multipage form to become enabled once a certain number of entries have been made to the database.

    Guidance would be appreciated... <img src=/S/please.gif border=0 alt=please width=31 height=23>
    - Ricky

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    <P ID="edit" class=small>(Edited by SammyB on 12-Feb-03 12:25. Attachment deleted)</P>Not exactly sure if I understand the problem, but that's never stopped me before. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    To keep it simple, I just made a two page MultiPage on a userform with the code:
    <pre>Option Explicit

    Private Sub UserForm_Activate()
    MultiPage1.Pages(1).Enabled = False
    End Sub</pre>


    Then on the sheet with the range, I entered the following code (right-click on the sheet-tab and view code):
    <pre>Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.WorksheetFunction.Count(Range("$2:$2") ) >= 3 Then
    UserForm1.MultiPage1.Pages(1).Enabled = True
    UserForm1.MultiPage1.Value = 1
    Else
    UserForm1.MultiPage1.Pages(1).Enabled = False
    UserForm1.MultiPage1.Value = 0
    End If
    Target.Select
    AppActivate Application.Caption
    End Sub</pre>


    Note, this code enables/disables page2, based on 3 or more values in row 2. You should be able to apply it to your situation. Since everythig has to be in the right place, I'm also attaching my sample workbook. HTH --Sam

    Note, the attachment dosn't run in my Internet Explorer browser. The next post may work better.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    <P ID="edit" class=small>(Edited by SammyB on 12-Feb-03 12:29. Attachment will not run in IE. Right-click and download to your computer.)</P>That first attempt did not seem to work very well on other computer configurations, so here is a more robust attempt.
    The userform has the default multipage control on it and has no code.
    The worksheet has a button to show the userform and a selection change event to enable/disable page 2. Here is the sheet code:
    <pre>Option Explicit

    Private Sub CommandButton1_Click()
    UserForm1.Show vbModeless
    Worksheet_SelectionChange ActiveWindow.RangeSelection
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.WorksheetFunction.Count(Range("$2:$2") ) >= 3 Then
    UserForm1.MultiPage1.Pages(1).Enabled = True
    UserForm1.MultiPage1.Value = 1
    Else
    UserForm1.MultiPage1.Pages(1).Enabled = False
    UserForm1.MultiPage1.Value = 0
    End If
    Target.Select
    AppActivate Application.Caption
    End Sub</pre>


    I'm attaching the new workbook. --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    <P ID="edit" class=small>(Edited by Tricky on 12-Feb-03 12:40. Added the button code for clarity...)</P>Sammy -

    I haven't plugged in the code as you've suggested just yet... I trust that it will work great, it's just not the response I was anticipating, so I though I'd better make sure I was asking the question correctly! I was thinking that we could just add a few lines to the <font color=blue>Private Sub UserForm.Initialize()</font color=blue> code that would do the trick...

    Currently, the user would click a button on the spreadsheet that would trigger this code:
    <pre>Sub CmdSetupBtn()
    On Error Resume Next
    Sheets("Clean").Activate
    FirstTime.Show
    End Sub</pre>

    <pre>
    </pre>

    <pre>

    Private Sub UserForm.Initialize()
    MultiPage1.Value = "0" 'Opens UserForm to first page
    Application.ScreenUpdating = False
    ...
    Application.ScreenUpdating = True
    End Sub</pre>


    You think I could just add to the UserForm code and not tamper with the underlying sheet code? Something immediately after: <font color=blue>MultiPage1.Value = "0"</font color=blue>?
    Thanks
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    Ah, I had assumed that your user-form was modeless like my example. If it is modal, then you do not have to be worried about the user entering data on the worksheet while the form is displayed. In this case it's a one-liner:
    <pre> If Application.WorksheetFunction.Count(Range("$3:$3") ) < 30 Then _
    UserForm1.MultiPage1.Pages(9).Enabled = False</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    Thanks for your continued assistance: this is where I am now...

    The following code creates this error message when the button is clicked:

    <font color=red>Run-time error '424':
    Object required</font color=red>

    Modules / StartForms
    <pre>Sub CmdSetupBtn()
    'On Error Resume Next
    Sheets("Clean").Activate
    FirstTime.Show
    End Sub</pre>


    Forms / FirstTime
    <pre>Private Sub UserForm_Initialize()
    MultiPage1.Value = "0"<font color=448800>
    If Application.WorksheetFunction.Count(Sheets("DB1"). Range("testarea")) < 30 Then _
    UserForm1.MultiPage1.Pages(11).Enabled = False</font color=448800>
    Application.ScreenUpdating = False
    ...........
    End Sub</pre>


    When I click [Debug], the line: <span style="background-color: #FFFF00; color: #000000; font-weight: bold">FirstTime.Show</span hi> is highlighted.

    But, if I just remove the green lines in the code above, the UserForm initializes without problem. I'm sure those two lines are correct but how could it be that if I remove them, everything works? I'm puzzled...
    - Ricky

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    After you are at the yellow "FirstTime.Show," press F8 to single step into the Initialize event. Continue to single-step to the Application.Work... line. Now look at individual pieces of the if...then statement to see what is causing the error. Easiest way to do this is to right-click on any toolbar, select customize, commands tab, Debug category, scroll down to Quick Watch, then drag the quick watch button to a toolbar and close the customize dialog. Now when you are single-stepping you can highlight variables and expressions, then press the quick watch button to see the value (or error). Now you are armed and dangerous. BTW, I would use 0 instead of "0", but that's not your error. I suspect that your named range is not testarea. Are you sure it's not TestArea or some other variation? Or that testarea is not a valid range? Let us know how it goes. I'll check the thread one more time tonight. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    Quick Watch produced this...
    Is this a problem?
    Attached Images Attached Images
    - Ricky

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    <P ID="edit" class=small>(Edited by Tricky on 12-Feb-03 20:34. Smaller picture...)</P>I can get this far - if I hit [F8] one more time, it will send the same error msg to the screen.
    Attached Images Attached Images
    - Ricky

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    I even tried to replace the range name with "B5:B378" but that didn't help, either...
    Attached Images Attached Images
    - Ricky

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    > UserForm1.MultiPage1.Pages(11).Enabled (Object Required)

    Means you don't have a UserForm1, or a UserForm1.MultiPage1, or a UserForm1.MultiPage1.Pages(11). Highlight each of them in turn and see which one fails. Remember the Pages collection is zero-based, so 11 is page 12.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    There it is! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> It's 8:00 now and I've been at this since 11 today. I think I'll go have a sandwich.

    I simply changed the line <font color=blue>UserForm1.MultiPage1.Pages(11).Enabled = False</font color=blue> to
    MultiPage1.Pages(11).Enabled = False, removing the UserForm1..

    Works like a charm
    Thanks for your ongoing support today, I really appreciate it.

    BTW - That [F8] and QuickWatch trick was good stuff. New to me and I know I'll make regular use of those tools.
    - Ricky

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiPage UserForm (XL2002)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, I had nightmares last night because of that. My userform had the default name of UserForm1. Your userform's name is FirstTime! That means that you didn't have "Option Explicit" at the top. If you had had it at the top, then the compiler would have told you not to use UserForm1. Putting on my teacher's hat and ruler, <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>, hold out your knuckels for a good whack. But, better yet, in the VBA editor, use the Tools, Options menu and place a checkmark in the Require-Variable-Declaration checkbox. If you always place Option Explicit at the top of every module, you will cut your debug time in half.

    > That [F8] and QuickWatch trick was good stuff
    I have added another section to the standard toolbar that has Find, Replace, Quick Watch, Compile, Comment Block, and Uncomment Block. These are all very useful tools. The other nice thing about the Quick Watch button is that it allows you to press the Add button which adds the object to the Watch window. Then in the Watch window, you can use the +'s to look at the various properties of an object. It's very useful when you don't know much about an object.

    One final word and then I'll take off my teacher's hat. If you use standard prefixes with each variable/object, then you will be able to come back to your code and remember it much easier. So, FirstTime should be frmFirstTime, MultiPage1 should be mpgSteps, and Pages(11) can also have a name like, pgComplete. Then your code is much more readable, and you will cut your maintence time in half:
    frmFirstTime.mpgSteps.pgComplete.Enable = True

    Have a great day and don't work so long today. After all, you're the supervisor! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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