Solved

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

Posted on 2016-11-21
12
47 Views
Last Modified: 2017-01-17
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
0
Comment
Question by:david francisco
  • 6
  • 6
12 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41896141
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
 

Author Comment

by:david francisco
ID: 41896148
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
 

Author Comment

by:david francisco
ID: 41896150
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:david francisco
ID: 41896152
the example document to be saved in is this
jose.xlsm
0
 

Author Comment

by:david francisco
ID: 41896157
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41896169
The data is getting there but there's data in rows 5000+. Delete those rows or move them up and try it again.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41896181
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41896551
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41896639
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
 

Author Comment

by:david francisco
ID: 41896719
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
 

Author Closing Comment

by:david francisco
ID: 41896720
Great help and very timely!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41896736
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question