Any way to force selection on userform?

  • I have a user 4 that has 4 selections, and an accept button. Currently, if the user doesn't make a selection, but clicks the "ACCEPT" button, I get this error:


    Run-time error '9'
    Subscript out of range


    Is there a way to popup a message box, or anything else, that would prompt the user to make a selection? Here is my userform code:



    Thanks very much,
    D0M

  • Re: Any way to force selection on userform?


    Set the button to Enabled False, then in each option button code

  • Re: Any way to force selection on userform?


    Try this:



    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Any way to force selection on userform?


    Thanks for the help Roy.


    That works fine, but for some reason, in doing that, the userform no longer unloads once the ACCEPT button is clicked. I haven't changed any of the code in this area, other than setting the ACCEPT button to False as you suggested. Any ideas?


    Code
    Private Sub Accept_Click() 
        Dim ws As Worksheet 
        Worksheets(wsName).Select 
        For Each ws In ActiveWorkbook.Worksheets 
            If ws.Name <> ActiveSheet.Name Then 
                ws.Visible = xlSheetVeryHidden 
            End If 
        Next ws 
        Unload UserForm1 
    End Sub


    Thanks very much,
    D0M

  • Re: Any way to force selection on userform?


    Alastair,


    Thanks for your reply also. I have tried your suggestion, as well as Roy's, and both solutions, for some reason, stop the user form from unloading.


    Thanks,
    D0M

  • Re: Any way to force selection on userform?


    Do you still want the user form to unload if the user does not make a selection?


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Any way to force selection on userform?


    The unload command must be in the accept code, add a cancel Button that could be enabled if accept is disabled, or If you don't want the user to try againadd adapt Alastair's code

    Code
    If wsname = "" Then 
           Unload Me
        End If
  • Re: Any way to force selection on userform?


    I thought having the code below, already included the unload command in the accept code.


    Whats happening is ...
    1. If I don't make a selection, and just click accept, MsgBox "Error - Please Make a Selection" pops up (Great)
    2. If I make a selection, and click accept, the macro runs, but the userform doesn't do away (Not Good)



    I hope this was a little more clear,
    Thanks for the help
    D0M

  • Re: Any way to force selection on userform?


    Andy,
    Thanks for your post. Yes I did, and still do have that. I was under the impression that, from others threads here, that particular code was just to disable the "red X" on the user form.


    So, I guess, the new question is, How do I disable the "red X', while still having the form unload once a selection has been made, and the accept button is pressed?


    Thanks very much,
    D0M

  • Re: Any way to force selection on userform?


    Use a module level variable to determine whether that line of code is executed.[vba]Private m_blnAllowClose as Boolean
    Private Sub Accept_Click()
    Dim ws As Worksheet
    If wsName = "" Then
    MsgBox "Error - Please Make a Selection"
    m_blnAllowClose = False
    Exit Sub
    End If

    Worksheets(wsName).Select
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then
    ws.Visible = xlSheetVeryHidden
    End If
    Next ws
    m_blnAllowClose = True
    Unload UserForm1
    End Sub
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    if not m_blnAllowClose then Cancel = Not CloseMode
    End Sub[/vba]

  • Re: Any way to force selection on userform?


    Woah! You just went from zero to 220mph in under 5 seconds.
    you lost me at "Use a module level variable"


    Where do I put

    Code
    Private m_blnAllowClose As Boolean


    I assume that

    is in the same spot it already was (in the userform code)


    Where do I put

    Code
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
        If Not m_blnAllowClose Then Cancel = Not CloseMode 
    End Sub


    Thanks very much for your help, unfortunately, this is so far above me, I can't even begin to understand it. (Upper Troposhere stuff)


    D0M

  • Re: Any way to force selection on userform?


    Sorry D0M didn't mean to lose you there :lol: judging from you first code post I thought you would get it.


    A module level variable is one that is declared outside of any procedure and placed at the top of the module.
    This makes it visible within all procedures in that module. I used Private to restrict it to just the module within which it was declared.
    If I had used Public then as well as be visible to all procedures in the module it would be visible to all modules within the project.


    The prodecures themselves simple replace the existing ones you have, which are all in the userform code module.

  • Re: Any way to force selection on userform?


    Andy,
    Thanks for the clarification, the explanation, and the direction. My original post was code that RoyUK had helped me with, or rather wrote for me. It is a little tough for my level, but I was able to work my way through it.


    Your code is definately out of my league. Although, it works perfectly. I know I have a lot more to learn, but ... thats why I am here :)


    Thanks so much to all of you that helped. This project is now complete, and is better than I anticipated.


    D0M

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!