+ Reply to Thread
Results 1 to 3 of 3

userform won't use entered values, hide, or cancel properly

  1. #1
    Registered User
    Join Date
    05-24-2004
    Posts
    52

    userform won't use entered values, hide, or cancel properly

    Hi.

    This is the first time I've ever tried building/using a userform and it turns out it's not as easy as I'd expected. Can anyone help?

    I've set things up so that when my "Main" macro is initiated, it immediately opens up a userform "MyForm" so the user can enter values into the 2 text boxes & decide (via checkbox "Chk1") if they want to run the "Secondary" macro before "Main".
    If the user presses the CANCEL button, the form is (ideally) supposed to close not only "MyForm" but also "Main" too. But if they press the RUN button, it should either (a) hide itself, and then run "Secondary" (if checked) before running "Main", using the values in "Box1" & "Box2" as variables in "Main", (b) hide itself, and then run "Main" using the values in "Box1" & "Box2" as variables, or (3) if applicable, return a message box saying they've forgotten to enter data or entered an invalid number.
    And to get really fancy (tho it's definitely not necessary), I'd love it if the text boxes could, say, refer back to cells F7 & F9 in "Sheet1" as their default value (since users can also enter values directly into the sheet itself) but that, if the user entered numbers into the form, those values would replace the ones on the sheet. Not only would it mean that you wouldn't have to refer back to the sheet to see what your last setting were, but "Main" is now set up to take the values from the sheet (e.g. the variable iNum1 is set to cell F7) and I'd rather not force users to use the form if they'd prefer to work directly off the sheet. Understand?

    But right now the contents of "Box1" & "Box2" aren't actually used by "Main"; "Chk1" works but it runs "Secondary" immediately.. even before I press RUN; CANCEL returns a 'Type Mismatch' error, and the whole "MyForm" stays visible while "Main" runs which is annoying b/c it's a huge macro & can take minutes or even hours depending on the number of loops required.

    I've read up on how to write userform code & spent lots of time reading forum postings on the subject, but I'm still pretty lost. Here's what I have so far...
    "Main" contains this code after all the Dim's:
    Please Login or Register  to view this content.
    And then the "MyForm" code contains all of this (sorry for how long it is):
    Please Login or Register  to view this content.
    So where am I going wrong???

  2. #2
    Forum Contributor
    Join Date
    11-16-2004
    Posts
    282
    I've attempted to redo your code. I basically moved most of the code to the CmdBttnRun procedure:

    Here's the code for the 'Main' procedure:

    Sub Main_v2()
    UserForm1.Show 'show the form so users can enter info
    End Sub

    ---------------------------------------------------------------------------------------------------

    Here's the code for the form and its' objects (note the UserForm_Initialize procedure; it sets the defaults for the user form when it is shown to the user):

    Private Sub UserForm_Initialize()
    Dim myVal1 As String, myVal2 As String
    myVal1 = ActiveWorkbook.Worksheets("Sheet1").Range("F7").Value
    myVal2 = ActiveWorkbook.Worksheets("Sheet1").Range("F9").Value
    ' When the form opens, set default values for text boxes...
    UserForm1.TextBox1.Text = myVal1
    UserForm1.TextBox2.Text = myVal2
    ' When the form opens, set default value for check box to unchecked...
    UserForm1.CheckBox1.Value = False
    End Sub

    Private Sub CmdBttnCancel_Click()
    ' If user cancels form, close it...
    Unload Me
    End Sub

    Private Sub CmdBttnRun_Click()
    Dim Box1Num As Integer, Box2Num As Integer
    Dim MyForm, Box1, Box2, Chk1
    Set MyForm = UserForm1
    Set Box1 = MyForm.TextBox1
    Set Box2 = MyForm.TextBox2
    Set Chk1 = MyForm.CheckBox1
    Set iNum1 = Worksheets("Sheet1").Range("F7").Value 'should be the default value (it's also what "Main" is coded to accept)
    If Box1.Text = "" Then
    MsgBox "Invalid entry:" & vbCrLf & "You forgot to say how many subsamples you wanted pulled" _
    & vbCrLf & "Re-enter an integer >1"
    With Box1
    .SelStart = 0
    .SelLength = Len(Box1.Text)
    .SetFocus
    End With
    Exit Sub
    ElseIf Not IsNumeric(Box1.Text) Then
    MsgBox "Invalid entry:" & vbCrLf & "You forgot to say how many subsamples you wanted pulled" _
    & vbCrLf & "Re-enter an integer >=1"
    With Box1
    .SelStart = 0
    .SelLength = Len(Box1.Text)
    .SetFocus
    End With
    Exit Sub
    ElseIf Val(Box1.Text) < 1 Then
    MsgBox "Invalid entry:" & vbCrLf & "Re-enter an integer >=1"
    With Box1
    .SelStart = 0
    .SelLength = Len(Box1.Text)
    .SetFocus
    End With
    Exit Sub
    Else
    Box1Num = Box1.Text
    End If

    Set iNum2 = Worksheets("Sheet1").Range("F9").Value 'should be the default value (it's also what "Main" is coded to accept)
    If Box2.Text = "" Then
    MsgBox "Invalid entry:" & vbCrLf & "You forgot to say how many columns you wanted copied" _
    & vbCrLf & "Re-enter an integer >1"
    With Box2
    .SelStart = 0
    .SelLength = Len(Box2.Text)
    .SetFocus
    End With
    Exit Sub
    ElseIf Not IsNumeric(Box2.Text) Then
    MsgBox "Invalid entry:" & vbCrLf & "You forgot to say how many columns you wanted copied" _
    & vbCrLf & "Re-enter an integer >1"
    With Box2
    .SelStart = 0
    .SelLength = Len(Box2.Text)
    .SetFocus
    End With
    Exit Sub
    ElseIf Val(Box2.Text) <= 1 Then
    MsgBox "Invalid entry:" & vbCrLf & "Re-enter an integer >1"
    With Box2
    .SelStart = 0
    .SelLength = Len(Box2.Text)
    .SetFocus
    End With
    Exit Sub
    Else
    Box2Num = Box2.Text
    End If

    ' Hide form when it passes data validation...
    MyForm.Hide

    ' Copy text box values to worksheet...
    Worksheets("Sheet1").Range("F7").Value = Box1Num
    Worksheets("Sheet1").Range("F9").Value = Box2Num

    ' Run Secondary code...
    If Chk1.Value = True Then 'if the checkbox is ticked, then...
    ' Copy your code for Secondary procedure here...
    End If

    ' Copy remainder of your code for 'Main' procedure here...
    End Sub

    Hope this helps,
    theDude

  3. #3
    Registered User
    Join Date
    05-24-2004
    Posts
    52
    Thanks theDude! Your code helped to get the majority of my form to work (hallelujah!) and I'm REALLY grateful. I can now input & output selections, roll from one script to another like I'd wanted, AND the run/cancel buttons work perfectly. Thanks for your careful coding!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1