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

 

[Smitty]

Can you post the code you've got now?

 

[tinkythomas]

The following code is the calling procedure.....

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

    'turn off screen updating
    Application.ScreenUpdating = False

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

    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    'show the form
    Load UserForm1
    UserForm1.Show

    '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").Select
        wsCrit.Rows(2).Delete
        wsNew.Range("Y:Y").Clear

    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

    'clear memory
    Set wsCrit = Nothing

End Sub

This is the code behind the OK button on the form.....

Sub CommandButton1_Click()

    MyVal1 = Me.TextBox1.Value
    MyVal2 = Me.TextBox2.Value
    MyVal3 = Me.TextBox3.Value

    Unload UserForm1

End Sub

The variables MyVal1 etc have been declared public at the top of the module that houses the SplitSystemNewSheet sub.

 

[tinkythomas]

Ok I have figured it out. I placed the variables right after I load the form like so......

'show the form
    Load UserForm1
    UserForm1.Show
    
    'assign values from userform
    MyVal1 = UserForm1.TextBox1.Value
    MyVal2 = UserForm1.TextBox2.Value
    MyVal3 = UserForm1.TextBox3.Value

and behind the command button of the form just hide like so.......

Sub CommandButton1_Click()

    Me.Hide

End Sub

Then finally at the end of the calling procedure unload the form.

Thanks for taking a look at the code I posted.

I assume this is the correct way to assign values from controls on forms?

 

[tinkythomas]

Hi,

Although I have managed to figure out using the values from the userform, how can I exit the calling sub if the user clicks the 'X' button?

At the moment if a user clicks this button they get a type mismatch error.

Any help would be welcomed. Thanks

 

[Norie]

Chris

Where have you put this, rather familiar, code?

Couldn't it just go in the userform module?

Then perhaps you could have a command button that hides the userform, does the split and then Unloads the userform.

If you do that you should be able to access the values on the userform even if it's hidden.

 

[tinkythomas]

Chris

Where have you put this, rather familiar, code?

Norie you are right the code is familiar You very kindly helped me out in this thread...http://www.mrexcel.com/forum/showthread.php?t=335766 I have now made the code a little more dynamic by adding a userform.

I just need some code now to exit the sub from which the form is called if the user decides to click the cancel button (x) on the form and does not enter any value.

I have no problem with retrieving the values entered from the form and passing them to the calling sub variables, as I said I just need code to handle the user cancelling.

Thanks once again for offering to help me out

 

[Norie]

Chris

I'm a little confused, I thought what you wanted to do was something to do with variables not exiting a sub.

Couldn't you just have a cancel button on the userform?

 

[tinkythomas]

Norie

Yes I suppose I could add a cancel button to the form. I just wondered if it was possible with code to utilize the forms x button?

Thanks again.

 

[Norie]

Chris

Utilize it to do what exactly?