Results 1 to 6 of 6

Get Data from UserForm (TextBox, ListBox) doesnt work

Threaded View

BorisMKD Get Data from UserForm... 02-04-2010, 05:54 AM
Richard Buttrey Re: Get Data from UserForm... 02-04-2010, 06:06 AM
BorisMKD Re: Get Data from UserForm... 02-04-2010, 06:11 AM
BorisMKD Re: Get Data from UserForm... 02-04-2010, 06:23 AM
hitmen Re: Get Data from UserForm... 01-15-2014, 04:53 AM
Richard Buttrey Re: Get Data from UserForm... 01-15-2014, 05:07 AM
  1. #1
    Registered User
    Join Date
    10-08-2008
    Location
    Austria
    Posts
    45

    Get Data from UserForm (TextBox, ListBox) doesnt work

    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
    Last edited by BorisMKD; 02-04-2010 at 06:26 AM.

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