userform submit button saving document, but not imputting data into worksheets

So to go into a little more detail. this document IS opening and saving the document, as verified by the documents properties, but it is not storing the values of the userform. I believe the issue to be a part of this portion of the code, but im not sure as to why it is not loading in. could the issue possibly be that the worksheets are established as tables?

Private Sub sbmt_Click()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim irow As Long

    Select Case NHdata.nhNme.Value
        Case "john", "jordy", "stan"
             Set wb = Workbooks.Open("L:\names\helper\harold\harold.xlsm")
             Set ws = Worksheets("Data")
        Case "chris", "dave", "christina"
             Set wb = Workbooks.Open("L:\names\helper\jose\jose.xlsm")
             Set ws = Worksheets("Data")
        Case Else
        MsgBox "There is no Trainer set for this Apprentice Agent. Please contact David Francisco to have the trainer added for this Apprentice Agent."
    End Select
    

 irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    With ws
        .Range("A" & irow) = Me.Date1.Value
        .Range("B" & irow) = Me.typSel.Value
        .Range("C" & irow) = Me.acntNmbr.Value
        .Range("D" & irow) = Me.nhNme.Value
        .Range("E" & irow) = Me.ojtNme.Value
        .Range("F" & irow) = Me.rslvChk.Value
        .Range("G" & irow) = Me.pvntChk.Value
        .Range("H" & irow) = Me.prmtChk.Value
        .Range("I" & irow) = Me.profChk.Value
        .Range("J" & irow) = Me.efctChk.Value
        .Range("K" & irow) = Me.srtText1.Value
        .Range("L" & irow) = Me.stpText1.Value
        .Range("M" & irow) = Me.conText1.Value
        .Range("N" & irow) = Me.srtText2.Value
        .Range("O" & irow) = Me.stpText2.Value
        .Range("P" & irow) = Me.conText2.Value
   End With
    wb.Close True
 End Sub

Open in new window

Tester.xlsm
david franciscoAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
If you want to write to the first empty row and ignore the rows at 5000 then change your iRow calculation to
 irow = ws.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1

Open in new window

0
 
Martin LissConnect With a Mentor Older than dirtCommented:
When I send the data to the "Data" sheet in the workbook, it shows up. Do the workbooks that you refer to in the code exist? Are the names and locations correct?
0
 
david franciscoAuthor Commented:
all the addresses are correct, but in each workbook used for saving data, the entire workbook is establish as a table called "Table1" that I think is where I am missing the references to the table
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
david franciscoAuthor Commented:
the document is opening, and saving, but the data is not transferring from the user form to the worksheet, and I believe it is because I am not referencing the Table  ("Table1") in the code, but im not sure how to integrate that in
0
 
david franciscoAuthor Commented:
the example document to be saved in is this
jose.xlsm
0
 
david franciscoAuthor Commented:
rather, after looking at the document, it appears the info is being saved, but only on line 5001 and beyond, not the next open line.
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
The data is getting there but there's data in rows 5000+. Delete those rows or move them up and try it again.
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
Your workbook may be corrupted because when I try to refer the comboboxes I get a" user defined type not defined" error. When I add an "On Error Resume Next" line before trying to refer to it, I get the error on the "On Error Resume Next" line! Let me see if I can rebuild the workbook.
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
I found the problem!!! It had nothing to do with the comboboxes, but rather the problem was in the Jose.xlsm workbook. It had a Worksheet_TableUpdate sub that was invalid and if the sub is commented out, the problem goes away. Do you know what that sub was supposed to do? I also made a few changes in the main workbook that I've attached. The changes I made are marked with 'new
28984486.xlsm
0
 
david franciscoAuthor Commented:
the sub is to set color changes for true and false in the target worksheet. but those are not necessary, I think I actually resolved it with your bit of code above. thanks for all the help.
0
 
david franciscoAuthor Commented:
Great help and very timely!
0
 
Martin LissOlder than dirtCommented:
If you want the colors then...
1) Uncomment the Worksheet_TableUpdate sub and change it to Private Sub UpdateColors(ByVal Target As Range)
2) In the Data sheet in Jose.xlsm delete the rows 2 to 5010.
3) At the end of the Worksheet_Change event in that workbook, add UpdateColors Target
I've attached the modified workbook.

In any case you're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
jose.xlsm
0
All Courses

From novice to tech pro — start learning today.