VBA Userform no longer populating cells in spreadsheet?

[Claire Jackson]

I had an excel workbook which had a userform in it which was working perfectly until I pointed to a new datafile. Now everything works ie bringing static data into the form but it's not populating the cells back into the excel workbook? I've attached both files for you to check what I'm doing wrong..

This is the code:

Private Sub Cmd_Access_Click()

Dim Job_Name As Variant
Dim Cur_Comp As Date
Dim ws As Worksheet
Dim What_Stage As String

Set ws = Worksheets("Sheet1")
With ws
.Range("B15").Value = Txt_Rev_Comp_Date.Text
.Range("B17").Value = Txt_Delay_Reason.Text
.Range("B19").Value = Txt_Cur_Percentage.Text
.Range("A23").Value = Txt_Job_Update.Text
.Range("A33").Value = Txt_Asbestos.Text
.Range("A37").Value = Txt_HS_Issues.Text
.Range("A41").Value = Txt_Security.Text
.Range("A47").Value = Txt_Further_Info.Text
.Range("A51").Value = Txt_Complaint.Text
.Range("H1").Value = Txt_Job_Ref.Text

 

[Anthony47]

I am afraid that data are now written in the wring "Sheet1", i.e. in the wrong workbook
I guess the code is written in a "userform vba module", then it points to Sheet1 that belongs to the same workbook that the userform belong to.
Also, if you change the selected workbook or worksheet while a userform is shown, then I remember that XL2013 and maybe also XL2016 shows the addressed sheet but it still keep the focus on the starting sheet.
To prevent confusion I suggest you modify to

 Set ws = Workbooks("WhichWorkbook").Worksheets("Sheet1")

And there is no any workbook attached to your message

Bye

 

[Claire Jackson]

Sorry. I think it’s the fact that the Userform is not initialising?

 

[Anthony47]

Did you evaluate what I wrote?
-do you have 2 or more workbook open when populating the userform?
-if Yes, did you check if the wrong "Sheet1" is modified?
-do you chenge selected sheet or workbook while the userfom is shown?
Also: which command you use to show the userform (I want to check if modal/modeless)

Which is the code for the events Userform_Initialize and Userform_Activate?

Bye

 

[Claire Jackson]

Im not sure how to upload the file to here?

 

[Anthony47]

You have to upload your file to a filesharing service; the easiest one to use is www.filedropper.com
You will then publish the download link you will be assigned

However you have also to ask the questions, don't assume we are more interested than you in finding the solution

Bye

 

[Claire Jackson]

Thank you. The link is <a href=http://www.filedropper.com/weeklyjobupdates><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=Free File Hosting - Online Storage; Upload Mp3, Videos, Music. Backup Files >file backup online</a></div>

I only have one spreadsheet open when it's meant to populate so the "sheet1" thing is only looking at one workbook. I just need it to populate the sheet on the save and email command button and don't know what I've done as it was working previously. Sorry couldn't answer you yesterday as we had a powercut

 

[Anthony47]

I don't have information to emulate your process, however I see that, when you press the CommandButton "Validate Ref", the following values are written from the several textboxes to the addressed cells:

Set ws = Worksheets("Sheet1")
With ws
.Range("B15").Value = Txt_Rev_Comp_Date.Text
.Range("B17").Value = Txt_Delay_Reason.Text
.Range("B19").Value = Txt_Cur_Percentage.Text
.Range("A23").Value = Txt_Job_Update.Text
.Range("A33").Value = Txt_Asbestos.Text
.Range("A37").Value = Txt_HS_Issues.Text
.Range("A41").Value = Txt_Security.Text
.Range("A47").Value = Txt_Further_Info.Text
.Range("A51").Value = Txt_Complaint.Text
.Range("H1").Value = Txt_Job_Ref.Text

When H1 is modified, the following macro is executed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" Then
    Call GetData
End If
End Sub

In the Call GetData you open the file Daily Report CRM.xlsx, do some operations that I am not able to decode, then close that file and clear many cells on your Sheet1:

Range("B15").ClearContents   '*
Range("B17").ClearContents   '*
Range("B19").ClearContents   '*
Range("E25").ClearContents
Range("B27").ClearContents
Range("D27").ClearContents
Range("E27").ClearContents
Range("G27").ClearContents
Range("E25").ClearContents
Range("E31").ClearContents
Range("E35").ClearContents
Range("E39").ClearContents
Range("E45").ClearContents
Range("E49").ClearContents
Range("A33").ClearContents   '*
Range("A37").ClearContents   '*
Range("A41").ClearContents   '*
Range("A47").ClearContents   '*
Range("A51").ClearContents   '*

I have marked with an Asterix (*) the cells that are initialli loaded and then cleared; only A23 (Txt_Job_Update) is not cleared

Since your question was "Now everything works ie bringing static data into the form but it's not populating the cells back into the excel workbook" I comment that, for what I see, the cells are populated when you start Sub Cmd_Access_Click but are then cleared when your Sub GetData is started on populating cell H1

Does this first analysis switch on a light?

If Not, can you explain which operations have to be done to replicate what you experience?

Bye

 

[Claire Jackson]

Hi,

The cells which ARE populated come directly from a vlookup and not from the data input into the form. Its the data thats inputted into the form thats not populating? I "messaged" all the clear contents lines and it still doesnt work

 

[Anthony47]

Which cell are you talking about?
How do I replicate your process?