Why my excel sheet is not saving data input from userform


I have created a simple user form which perfectly work data entry to excel sheet. Once data entry is done, I save both excel sheet(sheet 1) and VBA file(micro enable) by clicking save button. However, my excel file is empty when I reopen it.



There are many things that could cause this, including a macro that clears worksheets before you close the workbook or after you open it. Edit your question and include a sample file and it should be pretty easy to identify the issue.
don (rep: 1890) Nov 14, '17 at 2:06 pm
From your description it seems that your code is writing to a worksheet which isn't in the same workbook as the userform and its code.If you would post the code by which you call and close your user form we could get some idea of what is going one.
Variatus (rep: 4088) Nov 14, '17 at 8:06 pm
@Variatus and Don, thanks for your response. I've attach my file. Initially, it seems wokring with excel sheet saving data. After line 17onward, i can't save my file anymore.
wp2017 Nov 15, '17 at 1:27 am
I just added rows 18 and 19. After each addition I saved, closed and re-opened. All no problem. However, I disabled both your automatic save commands. The one in 'Submit_Click' requires a path. The one in 'Workbook_BeforeClose' should work. Therefore the problem might be from the SaveAs command in 'Submit' or it might be in parts of the workbook you didn't upload. I suggest you repeat the exact test I did, with the same uploaded file and both Save and SaveAs disabled. See if the problem is still there.
Variatus (rep: 4088) Nov 15, '17 at 1:54 am
thanks. I got it. Perfect!
wp2017 Nov 15, '17 at 3:20 am
sorry one more question.

What i want to do is
-if user choose Product type "Bread", i want my $value at multiple location in same row(G18 and K18).
-if user choose Product type "Cake", i want my $value at multiple location in same row(G18 and J18). How could I write this quote?
wp2017 Nov 15, '17 at 3:23 am
If you found a solution, please post it as an Answer at the bottom of the page! Future readers of this forum will come looking for an answer to a similar issue and they will follow it better if the answer is there instead of in a discussion comment up here.
don (rep: 1890) Nov 15, '17 at 3:33 am
Add to Discussion


Selected Answer

Noted. thank you

Problem solved by removing auto save mode in my workbook and userform.


Your code is already writing the $value to column G. Therefore only an additional entry in column K would be required. I have added this to your 'Submit2_Click' code below.

Private Sub Submit2_Click()
    Dim LastRow As Long
'    Application.DisplayAlerts = False
'    Range("B3").Select
'    ActiveCell.End(xlDown).Select          ' xlDown isn't recommended
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Cells(LastRow, 2).Value = txtdate2.Text
    Cells(LastRow, 3).Value = txtexplanation2.Text
    Cells(LastRow, 5).Value = txtReseller2.Text
    Cells(LastRow, 4).Value = TxtProduct2.Text
    Cells(LastRow, 6).Value = txtTypeOfCost2.Text
    Cells(LastRow, 7).Value = txtvalue2.Text
    If InStr(1, "cake,bread", TxtProduct2.Text, vbTextCompare) Then
        Cells(LastRow, "K").Value = txtvalue2.Text
    End If
    Cells(LastRow, 1).Formula = "=Oops(ROW())"
'    ActiveWorkbook.SaveAs "userformdata_12Nov2017"
'    Application.DisplayAlerts = True
End Sub

I wonder if the extra entry in column K would upset your formula in column A. You will see that the above code writes a formula in column A, "=Oops(ROW())". 'Oops' is a UDF I have written for you to do the job of the formula you have so that you don't have to keep extra blank rows with formulas in your worksheet. Here is the UDF.

Option Explicit
Function Oops(R As Long) As String
    ' 15  Nov 2017
    Dim Rng As Range
    Dim Amt As Double
    With Application
        Set Rng = .Union(Range(Cells(R, "I"), Cells(R, "J")), _
                         Range(Cells(R, "L"), Cells(R, "AH")))
        Amt = .Sum(Cells(R, "H").Value, Cells(R, "K").Value)
        Amt = Round(Amt - .Sum(Rng), 2)
    End With
    Oops = IIf(Round(Cells(R, "G").Value, 2) = Amt, "OK", "Oops")
End Function

If you want to use it, paste this procedure in a standard code module (by default 'Module1').



@Variatus, thanks for the quote. I ignore "Oops(ROW())" first as i can change to formula on excel sheet later time. I have tried with the quote (without this line-->Cells(LastRow, 1).Formula = "=Oops(ROW())" ). There is always the 2 row for entry... means entry in K is on line 18 and the rest of the data (B~G) is next line, 19. did i do something wrong?
wp2017 Nov 16, '17 at 3:08 am
I suspect you mixed my code with yours. In the above code you have
Cells(LastRow, 2).Value = txtdate2.Text
writing to the last row,  but in your code it was
Cells(LastRow + 1, 2).Value = txtdate2.Text
which is writing one row lower.
Variatus (rep: 4088) Nov 16, '17 at 4:45 am
@Variatus.  you are right. now ok. thank you so much
wp2017 Nov 16, '17 at 7:13 am
You are welcome. I'm glad I could help.
Variatus (rep: 4088) Nov 16, '17 at 7:24 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login