Assign value from userorm textbox to variable?

[tinkythomas]

Hi,

I have created a userform with 3 textboxes and I want to return the values entered to variables for use in a procedure. I cannot figure out how to return the values.

Behind the command button I am unloading the form so that the calling procedure continues executing but my vars cannot see the controls.

I have tried making them public outside of the calling sub, but nothing. Can anyone point out to me what I'm doing wrong?

Thank-you

 

[tinkythomas]

Im sorry if I am confusing matters here. I wondered if was possible to exit the sub that calls the form if the user clicks the x button on the form?

At the moment if the user does this then a type mismatch error occurs.

This is the sub that calls the userform...

Sub SplitSystemNewSheet()
    Dim wsAll As Worksheet
    Dim wsCrit As Worksheet
    Dim wsNew As Worksheet
    Dim LastRow As Long
    Dim LastRowCrit As Long
    Dim i As Long
    Dim Message1, Message2, Message3, Title, Default1, Default2, _
        Default3, MyVal1, MyVal2, MyVal3

    'turn off screen updating
    Application.ScreenUpdating = False

    'select the first worksheet with the data
    Set wsAll = Worksheets(1)

    'check if data exists, if not exit sub
    If wsAll.Cells(2, 1).Value = "" Then
        MsgBox "No data exists press OK to exit", vbInformation, "No data!"
        Exit Sub
    End If

    'find the last row of data
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row

    'show the form
    Load UserForm1
    UserForm1.Show

    'assign values from userform
    MyVal1 = UserForm1.TextBox1.Value
    MyVal2 = UserForm1.TextBox2.Value
    MyVal3 = UserForm1.TextBox3.Value

    'use col z for the formula to extract the required characters
    With wsAll.Range("Z1")
        .Value = "ID"
        .Offset(1).Resize(LastRow - 1).Value = "=MID(" & MyVal1 & "2," & MyVal2 & "," & MyVal3 & ")"
    End With

    'add sheet for the criteria
    Set wsCrit = Worksheets.Add

    'column y has the criteria eg ID
    wsAll.Range("Z1:Z" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRowCrit

        'add new sheet for each system
        Set wsNew = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
                                                  CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsNew.Cells.WrapText = False
        wsNew.Cells.EntireColumn.AutoFit
        wsNew.Range("A1").AutoFilter
        wsCrit.Rows(2).Delete
        wsNew.Range("Z:Z").Clear
        'freeze panes
        wsNew.Range("A2").Select
        ActiveWindow.FreezePanes = True

    Next i

    'disable confirm sheet delete dialogue and delete sheets
    Application.DisplayAlerts = False
    wsCrit.Delete
    wsAll.Delete
    Application.DisplayAlerts = True

    'turn on screen updating
    Application.ScreenUpdating = True

    'close the userform
    Unload UserForm1

    'clear memory
    Set wsCrit = Nothing

End Sub

Then I have this code behind the OK button on the form....

Sub CommandButton1_Click()

    Me.Hide

End Sub

So the procedure runs and loads the userform, then user enter the values, click OK then the rest of the sub is executed. But if the user clicks the x button without entering any value then the type mismatch error occurs.

So my question is how do I handle this event?

Sorry to be a pain

 

[Norie]

Chris

I still don't see why all the code can't go in the userform.

But what the hey, here's how you can end all code when the user hits the X.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    End
End Sub

 

[tinkythomas]

Chris

I still don't see why all the code can't go in the userform.

Norie thank-you for your patience and help The code at the moment is contained in a standard module within an .xla (add-in).

Does this have any implications or is it ok to put all the code in the userform?

Thank-you for your continued help.

 

[Norie]

Chris

I've got to admit I'm no expert, or even a beginner in fact, on add-ins.

Is there a reason the code is in an add-in?

As far as I know the main reason for putting code into add-ins is to make it available to other workbooks.:

 

[tinkythomas]

Norie

Yes the add-in is so that the code is available to other users/workbooks. This add-in also creates a custom menu from which the macro's can be run.

I tried placing the code in the userform and tried to run the sub from the custom menu but it now can't see the sub.

I will revert back to the prevoius setup. The code you kindly gave me works perfectly by the way