Assign value from userorm textbox to variable?

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
The following code is the calling procedure.....
Code:
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.....
Code:
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.
 
Last edited:
Upvote 0
Ok I have figured it out:). I placed the variables right after I load the form like so......
Code:
'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.......
Code:
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?
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0
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:)
 
Upvote 0
Chris

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

Couldn't you just have a cancel button on the userform?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top