Why isn't this code working

[amoverton2]

Hey All!

Background info:

I have a workbook with 12 sheets, 1 sheet labeled 99999 (all data), and individual entities labeled AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, III, JJJ, KKK.
I have a vba userform with 14 textboxes (labeled TextBox1-14)
On each of the sheets they are 14 pieces of information in columns A thru N (hence 14 textboxes)

Want:
I want the code to transfer all the information residing in the 14 textboxes to the "all data" sheet (99999) and the sheet specified in TextBox1 (which will be AAA, BBB,...KKK) in the next empty row (on both the "all data" and specified sheet) from column A to N.

Problem:
I've inputted information and clicked the button and the information does not go into the sheets, its like it disappears.

Code:

Private Sub CommandButton1_Click()
    Dim wsDestination As Worksheet
    Dim wsMain As Worksheet
    Dim userInput As String
    Dim lastRow As Long
    Dim i As Integer

  
    userInput = UCase(TextBox1.Value)
    Select Case userInput
        Case "AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG", "HHH", "III", "JJJ", "KKK"
            Set wsDestination = ThisWorkbook.Sheets(userInput)
        Case Else
            MsgBox "Invalid sheet name entered."
            Exit Sub
    End Select

    With wsDestination
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        For i = 2 To 14
            .Cells(lastRow, i - 1).Value = Controls("TextBox" & i).Value
        Next i
    End With

    Set wsMain = ThisWorkbook.Sheets("99999")
    With wsMain
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(lastRow, 1).Value = TextBox1.Value ' Assumed to be the sheet name
        For i = 2 To 14
            .Cells(lastRow, i).Value = Controls("TextBox" & i).Value
        Next i
    End With

    'Clear textboxes after transfer
    For i = 1 To 14
        Controls("TextBox" & i).Value = ""
    Next i
End Sub

Thanks!!

 

[NdNoviceHlp]

Hi amovertan2. You can trial adding either Me. or the the UserformName. before the "Controls" (ie. Me.Controls("TextBox" & i).Value). HTH. Dave
ps. Please use code tags

 

[dmt32]

Hi,
I ran your code unaltered & it posted data to the sheets ok for me without issue.

09-01-2024.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Head1	Head2	Head3	Head4	Head5	Head6	Head7	Head8	Head9	Head10	Head11	Head12	Head13	Head14
2	BBB	2	3	4	5	6	7	8	9	10	11	12	13	14
99999

If still not able to resolve, suggest place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it.

Also, please use code tags when posting code (menubar above > VBA) & place code between the tags

Private Sub CommandButton1_Click()
    Dim wsDestination As Worksheet
    Dim wsMain        As Worksheet
    Dim userInput     As String
    Dim lastRow       As Long
    Dim i             As Integer
  
    userInput = UCase(TextBox1.Value)
  
    Select Case userInput
        Case "AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG", "HHH", "III", "JJJ", "KKK"
            Set wsDestination = ThisWorkbook.Sheets(userInput)
        Case Else
            MsgBox "Invalid sheet name entered."
            Exit Sub
    End Select
  
    With wsDestination
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        For i = 2 To 14
            .Cells(lastRow, i - 1).Value = Controls("TextBox" & i).Value
        Next i
    End With
  
    Set wsMain = ThisWorkbook.Sheets("99999")
    With wsMain
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(lastRow, 1).Value = TextBox1.Value        ' Assumed to be the sheet name
        For i = 2 To 14
            .Cells(lastRow, i).Value = Controls("TextBox" & i).Value
        Next i
    End With
  
    'Clear textboxes after transfer
    For i = 1 To 14
        Controls("TextBox" & i).Value = ""
    Next i
End Sub

Dave

 

[amoverton2]

Copy all about the code tags... I was frustrated and completely forgot about that.

Is there anything you can think of that would hinder the ability of the code to run? Also, I have the code running on the command button within the form vice being written into a module. Does that make a difference?

Adam

 

[dmt32]

Hi
I too have your code is in a the userforms code page & as I have already said, it works ok for me - can only conclude at this stage that you something else going on in your project that is causing your issue but without seeing it, can only make guesses.

To better understand the issue, it would be helpful to forum if you could share your workbook (with dummy data) on a file sharing site like dropbox & provide a link to it.

As an aside, rather that type the destination sheet name in textbox1 - consider changing it for a combobox which provides a list of all available worksheets.

Dave

 

[amoverton2]

Update since I've been out of my office, and today is the first day back I could look at this. Code works, I had a table for the first 60 rows and all data was being place on row 61 and down. I modified the code to add it to the next empty row in the table. Thanks for the help