Get Data from UserForm (TextBox, ListBox) doesnt work

**BorisMKD** · 02-04-2010, 05:54 AM

Hi dear friends,

I spent so long time to fix this problem, but it seems that I can't go on.

I have a simple question. How can I get the data from the UserForm and use it in the worksheet?

Everything works fine, only the UserForm makes problems

Here is the code:

 
Option Explicit

    Dim strName As String
    Dim fLanguage As String
    Dim sLanguage As String
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim filterRng As Range
    Dim rngVisible As Range
    Dim cl As Range
    Dim X As Long
    Dim R As Long

Private Sub UserForm_Initialize()

    With ListBox1
        .AddItem "English"
        .AddItem "German"
        .AddItem "Portuguese"
        .AddItem "Russian"
        .AddItem "Chinese"
    End With
    
    With ListBox2
        .AddItem "English"
        .AddItem "German"
        .AddItem "Portuguese"
        .AddItem "Russian"
        .AddItem "Chinese"
        .AddItem "(No 2nd Language)"
    End With
    
    ListBox1.ColumnWidths = "75;0"
    ListBox2.ColumnWidths = "100;0"
    
    
     strName = Me.TextBox1.Value 
    Set ws1 = ThisWorkbook.Sheets("SL1-9")
    Set ws2 = ThisWorkbook.Sheets("SCS_List for SAP P&E")
    
    
End Sub
Private Sub OKButton_Click()

    Sheets("SCS_List for SAP P&E").Range("B2:M3000").ClearContents
    Set filterRng = ws1.Cells(1, 1).CurrentRegion
    filterRng.AutoFilter
    filterRng.AutoFilter Field:=14, Criteria1:="YES"
    On Error Resume Next
    
    With ws1
        Set rngVisible = .Range(.Cells(3, 3), .Cells(.Rows.Count, 3).End(xlUp))
        X = 2
        For Each cl In rngVisible
            If cl.EntireRow.Hidden = False Then
                R = cl.Row
                .Cells(R, 1).Value = strName
                ws2.Cells(X, 2).Value = .Cells(R, 1).Value
                ws2.Cells(X, 3).Value = .Cells(R, 2).Value & .Cells(R, 3).Value
                ws2.Cells(X, 4).Value = .Cells(R, 4).Value & .Cells(R, 5).Value
                ws2.Cells(X, 5).Value = .Cells(R, 7).Value & .Cells(R, 8).Value & .Cells(R, 9).Value
                ws2.Cells(X, 6).Value = .Cells(R, 10).Value & .Cells(R, 11).Value & .Cells(R, 12).Value
                Select Case fLanguage 
                    Case "English"
                        ws2.Cells(X, 9).Value = .Cells(R, 18).Value ' English
                    Case "German"
                        ws2.Cells(X, 9).Value = .Cells(R, 17).Value ' German
                    Case "Portuguese"
                        ws2.Cells(X, 9).Value = .Cells(R, 20).Value ' Portuguese
                    Case "Russian"
                        ws2.Cells(X, 9).Value = .Cells(R, 21).Value ' Russian
                    Case "Chinese"
                        ws2.Cells(X, 9).Value = .Cells(R, 22).Value ' Chinese
                End Select
                
                Select Case sLanguage 
                    Case "English"
                        ws2.Cells(X, 10).Value = .Cells(R, 18).Value ' English
                    Case "German"
                        ws2.Cells(X, 10).Value = .Cells(R, 17).Value ' German
                    Case "Portuguese"
                        ws2.Cells(X, 10).Value = .Cells(R, 20).Value ' Portuguese
                    Case "Russian"
                        ws2.Cells(X, 10).Value = .Cells(R, 21).Value ' Russian
                    Case "Chinese"
                        ws2.Cells(X, 10).Value = .Cells(R, 22).Value ' Chinese
                    Case "(No 2nd Language)"
                        ws2.Cells(X, 10).Value = .Cells(R, 55).Value ' (No 2nd Language)
                End Select
                
                X = X + 1
            End If
        Next cl
    End With
    On Error GoTo 0
Unload Me
End Sub

Private Sub CancelButton_Click()
Unload Me
End Sub

Thanks in advance, Boris

**Richard Buttrey** · 02-04-2010, 06:06 AM

Hi Boris,

1. You are using Text Boxes to hold the languages, but the line

strName = Me.TextBox1.Value

is looking for a non existent Text Box.

2. You are using a Select Case fLanguage (and sLanguage), without first allocating a value to these two string variables.

HTH

**BorisMKD** · 02-04-2010, 06:11 AM

I fixed only the 1. I deleted:

 strName = Me.TextBox1.Vaue

and did

 .Cells(R, 1).Value = UserSelection.TextBox1.Value

How should I make the fLanguage & the sLanguage work?

Thanks

**BorisMKD** · 02-04-2010, 06:23 AM

Did it by myself.

Thank you Mr. Richard Buttrey for Your help.

The Code that Works:

 Option Explicit

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim filterRng As Range
    Dim rngVisible As Range
    Dim cl As Range
    Dim X As Long
    Dim R As Long

Private Sub UserForm_Initialize()

    With ListBox1
        .AddItem "English"
        .AddItem "German"
        .AddItem "Portuguese"
        .AddItem "Russian"
        .AddItem "Chinese"
    End With
    
    With ListBox2
        .AddItem "English"
        .AddItem "German"
        .AddItem "Portuguese"
        .AddItem "Russian"
        .AddItem "Chinese"
        .AddItem "(No 2nd Language)"
    End With
    
    ListBox1.ColumnWidths = "75;0"
    ListBox2.ColumnWidths = "100;0"
    
    Set ws1 = ThisWorkbook.Sheets("SL1-9")
    Set ws2 = ThisWorkbook.Sheets("SCS_List for SAP P&E")
    
    
End Sub
Private Sub OKButton_Click()

    Sheets("SCS_List for SAP P&E").Range("B2:M3000").ClearContents
    Set filterRng = ws1.Cells(1, 1).CurrentRegion
    filterRng.AutoFilter
    filterRng.AutoFilter Field:=14, Criteria1:="YES"
    On Error Resume Next
    
    With ws1
        Set rngVisible = .Range(.Cells(3, 3), .Cells(.Rows.Count, 3).End(xlUp))
        X = 2
        For Each cl In rngVisible
            If cl.EntireRow.Hidden = False Then
                R = cl.Row
                .Cells(R, 1).Value = UserSelection.TextBox1.Value
                ws2.Cells(X, 2).Value = .Cells(R, 1).Value
                ws2.Cells(X, 3).Value = .Cells(R, 2).Value & .Cells(R, 3).Value
                ws2.Cells(X, 4).Value = .Cells(R, 4).Value & .Cells(R, 5).Value
                ws2.Cells(X, 5).Value = .Cells(R, 7).Value & .Cells(R, 8).Value & .Cells(R, 9).Value
                ws2.Cells(X, 6).Value = .Cells(R, 10).Value & .Cells(R, 11).Value & .Cells(R, 12).Value
                Select Case UserSelection.ListBox1.Value
                    Case "English"
                        ws2.Cells(X, 9).Value = .Cells(R, 18).Value ' English
                    Case "German"
                        ws2.Cells(X, 9).Value = .Cells(R, 17).Value ' German
                    Case "Portuguese"
                        ws2.Cells(X, 9).Value = .Cells(R, 20).Value ' Portuguese
                    Case "Russian"
                        ws2.Cells(X, 9).Value = .Cells(R, 21).Value ' Russian
                    Case "Chinese"
                        ws2.Cells(X, 9).Value = .Cells(R, 22).Value ' Chinese
                End Select
                
                Select Case UserSelection.ListBox2.Value
                    Case "English"
                        ws2.Cells(X, 10).Value = .Cells(R, 18).Value ' English
                    Case "German"
                        ws2.Cells(X, 10).Value = .Cells(R, 17).Value ' German
                    Case "Portuguese"
                        ws2.Cells(X, 10).Value = .Cells(R, 20).Value ' Portuguese
                    Case "Russian"
                        ws2.Cells(X, 10).Value = .Cells(R, 21).Value ' Russian
                    Case "Chinese"
                        ws2.Cells(X, 10).Value = .Cells(R, 22).Value ' Chinese
                    Case "(No 2nd Language)"
                        ws2.Cells(X, 10).Value = .Cells(R, 55).Value ' (No 2nd Language)
                End Select
                
                X = X + 1
            End If
        Next cl
    End With
    On Error GoTo 0
Unload Me
End Sub

Private Sub CancelButton_Click()
Unload Me
End Sub

**hitmen** · 01-15-2014, 04:53 AM

can you send me done file, please.
i have problem the same to you.

Thank you so much

**Richard Buttrey** · 01-15-2014, 05:07 AM

Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.