Using multipage to add data to worksheet

B

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
233
Office Version
  1. 2016
Platform
  1. Windows
Hello all,

I got the following form;
Capture.PNG


With this form i can add observations to my excel sheet named "Observations".
With the following code the data is added to Observations.

VBA Code: 
Private Sub cmdAddOberservation_Click()
    Dim fTextBox As Object
    Dim xEptTxtName As String
    
    For Each fTextBox In Productionreturns.Controls
        If TypeName(fTextBox) = "TextBox" Then
            If fTextBox.Text = "" Then
                xEptTxtName = xEptTxtName & fTextBox.Name & " is empty " & vbNewLine
            End If
        End If
  
    
    Next
        If xEptTxtName <> "" Then
            MsgBox "The following required fields are not complete;" & vbNewLine & vbNewLine & Chr(13) & xEptTxtName, vbInformation
        Exit Sub
        Else
    End If

If Worksheets("Observations").Range("A2").Value = "" Then
Worksheets("Observations").Range("A2:G2").FormulaArray = "1"
End If
Set Drng = Worksheets("Observations").Range("A1")

Drng.End(xlDown).Offset(1, 0).Value = Me.lblGelCodeR.Caption
Drng.End(xlDown).Offset(0, 1).Value = Me.lblProductNameR.Caption
Drng.End(xlDown).Offset(0, 2).Value = Me.lblBatchNumberR.Caption
Drng.End(xlDown).Offset(0, 3).Value = Me.lblBoxR.Caption
Drng.End(xlDown).Offset(0, 4).Value = Me.txtStDate.Value
Drng.End(xlDown).Offset(0, 5).Value = Me.txtCorrectBy.Value
Drng.End(xlDown).Offset(0, 6).Value = Me.txtEndDate.Value
Drng.End(xlDown).Offset(0, 7).Value = Me.txtCheckDate.Value
Drng.End(xlDown).Offset(0, 8).Value = Me.cboDocument.Value
Drng.End(xlDown).Offset(0, 9).Value = Me.cboDocumentPart.Value
Drng.End(xlDown).Offset(0, 10).Value = Me.cboPart.Value
Drng.End(xlDown).Offset(0, 11).Value = Drng.End(xlDown).Offset(-1, 8).Value + 1

Call MsgBox("A new observation has been added", vbInformation, "Add observation")

SortIt
Unload Me
On Error GoTo 0
Exit Sub

cmdAdd_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddObservation_Click of Form Productionreturns"

End Sub

Now i would like the same for Page 2 of the multipage. So i can add 2 observations at once.
For page 1 it works but how do I make it that when data is added to page 2 this data is entered beneath the data of page 1 in the sheet "observations"?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I changed the way I put the data on the sheet, it was a bit complicated to read.
I put a couple of examples to put the following pages.

VBA Code: 
Private Sub cmdAddOberservation_Click()
  Dim fTextBox As Object, xEptTxtName As String
  Dim sh As Worksheet, lr As Long
  
  Set sh = Sheets("Observations")
  
  For Each fTextBox In Productionreturns.Controls
    If TypeName(fTextBox) = "TextBox" Then
      If fTextBox.Text = "" Then
        xEptTxtName = xEptTxtName & fTextBox.Name & " is empty " & vbNewLine
      End If
    End If
  Next
  
  If xEptTxtName <> "" Then
    MsgBox "The following required fields are not complete;" & vbNewLine & vbNewLine & Chr(13) & xEptTxtName, vbInformation
    Exit Sub
  End If

  If Worksheets("Observations").Range("A2").Value = "" Then
    Worksheets("Observations").Range("A2:G2").FormulaArray = "1"
  End If
    
  'PAGE 1
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
  sh.Cells(lr, "A").Value = Me.lblGelCodeR.Caption
  sh.Cells(lr, "B").Value = Me.lblProductNameR.Caption
  sh.Cells(lr, "C").Value = Me.lblBatchNumberR.Caption
  sh.Cells(lr, "D").Value = Me.lblBoxR.Caption
  sh.Cells(lr, "E").Value = Me.txtStDate.Value
  sh.Cells(lr, "F").Value = Me.txtCorrectBy.Value
  sh.Cells(lr, "G").Value = Me.txtEndDate.Value
  sh.Cells(lr, "H").Value = Me.txtCheckDate.Value
  sh.Cells(lr, "I").Value = Me.cboDocument.Value
  sh.Cells(lr, "J").Value = Me.cboDocumentPart.Value
  sh.Cells(lr, "K").Value = Me.cboPart.Value
  sh.Cells(lr, "L").Value = sh.Cells(lr - 1, "L").Value + 1
  
  'PAGE 2
  lr = lr + 1
  sh.Cells(lr, "A").Value = Me.control_x.Caption
  sh.Cells(lr, "B").Value = Me.control_y.Caption
  sh.Cells(lr, "C").Value = Me.control_z.Caption

  'PAGE 3
  lr = lr + 2
  sh.Cells(lr, "A").Value = Me.control_a.Value
  sh.Cells(lr, "B").Value = Me.control_b.Value
  sh.Cells(lr, "C").Value = Me.control_c.Value

  Call MsgBox("A new observation has been added", vbInformation, "Add observation")
    
  SortIt
  Unload Me
  On Error GoTo 0
  Exit Sub
  
cmdAdd_Click_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddObservation_Click of Form Productionreturns"
End Sub
 
Upvote 0
Hello,

Thank you so much for your reply and your effort.

I'm getting a error on the following line;

Code: 
sh.Cells(lr, "A").value = Me.control_x.Caption

It tells me: Compile error: Method or data membmer not found and highlighting this: .control_x

Any idea what causes this error?
 
Upvote 0
Bandito1 said:
Hello,

Thank you so much for your reply and your effort.

I'm getting a error on the following line;

Code: 
sh.Cells(lr, "A").value = Me.control_x.Caption

It tells me: Compile error: Method or data membmer not found and highlighting this: .control_x

Any idea what causes this error?
Click to expand...

Me.control_x.Caption

This is an example, I don't know the names of your controls.
So you must put the names. I only gave you an example, you must change the example names to the names of your controls.

lr = lr + 1
sh.Cells(lr, "A").Value = Me.control_x.Caption
sh.Cells(lr, "B").Value = Me.control_y.Caption
sh.Cells(lr, "C").Value = Me.control_z.Caption

'PAGE 3
lr = lr + 1
sh.Cells(lr, "A").Value = Me.control_a.Value
sh.Cells(lr, "B").Value = Me.control_b.Value
sh.Cells(lr, "C").Value = Me.control_c.Value

Marking it in red, are examples, you must put the real names of your controls.
In addition I only put 3 examples, I do not know how many controls you have on each Page, so if you have 20 controls, you must put the 20 controls.
 
Upvote 0
Hey,

Thanks for the fast reply.
I get it and i fixed it, thank you very much.

One thing i notice now. It now requires me to fill in page 2, 3 and onward.

It should not be mandatory.
It's possible that some documents have only 1 correction (only page 1 needs to be filled in).
It can happen that i need 2 pages but not manatory.

Hope i explained it clear enough

Thanks in advance
 
Upvote 0
How do you know that it should be sent to the sheet?
Perhaps, if a control is empty then nothing go to the sheet, if that control is full then pass the data from the Page to the sheet.

I give you an example, but you have to adapt it to the data of your userform.

VBA Code: 
Private Sub cmdAddOberservation_Click()
  Dim fTextBox As Object, xEptTxtName As String
  Dim sh As Worksheet, lr As Long
 
  Set sh = Sheets("Observations")
 
  For Each fTextBox In Productionreturns.Controls
    If TypeName(fTextBox) = "TextBox" Then
      If fTextBox.Text = "" Then
        xEptTxtName = xEptTxtName & fTextBox.Name & " is empty " & vbNewLine
      End If
    End If
  Next
 
  If xEptTxtName <> "" Then
    MsgBox "The following required fields are not complete;" & vbNewLine & vbNewLine & Chr(13) & xEptTxtName, vbInformation
    Exit Sub
  End If

  If Worksheets("Observations").Range("A2").Value = "" Then
    Worksheets("Observations").Range("A2:G2").FormulaArray = "1"
  End If
    
  'PAGE 1
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
  sh.Cells(lr, "A").Value = Me.lblGelCodeR.Caption
  sh.Cells(lr, "B").Value = Me.lblProductNameR.Caption
  sh.Cells(lr, "C").Value = Me.lblBatchNumberR.Caption
  sh.Cells(lr, "D").Value = Me.lblBoxR.Caption
  sh.Cells(lr, "E").Value = Me.txtStDate.Value
  sh.Cells(lr, "F").Value = Me.txtCorrectBy.Value
  sh.Cells(lr, "G").Value = Me.txtEndDate.Value
  sh.Cells(lr, "H").Value = Me.txtCheckDate.Value
  sh.Cells(lr, "I").Value = Me.cboDocument.Value
  sh.Cells(lr, "J").Value = Me.cboDocumentPart.Value
  sh.Cells(lr, "K").Value = Me.cboPart.Value
  sh.Cells(lr, "L").Value = sh.Cells(lr - 1, "L").Value + 1
 
  'PAGE 2
  if Me.control_x.Caption <> "" then
  lr = lr + 1
  sh.Cells(lr, "A").Value = Me.control_x.Caption
  sh.Cells(lr, "B").Value = Me.control_y.Caption
  sh.Cells(lr, "C").Value = Me.control_z.Caption
  End If

  'PAGE 3
  if Me.control_a.Value <> "" then
  lr = lr + 1
  sh.Cells(lr, "A").Value = Me.control_a.Value
  sh.Cells(lr, "B").Value = Me.control_b.Value
  sh.Cells(lr, "C").Value = Me.control_c.Value
  End If

  Call MsgBox("A new observation has been added", vbInformation, "Add observation")
    
  SortIt
  Unload Me
  On Error GoTo 0
  Exit Sub
 
cmdAdd_Click_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddObservation_Click of Form Productionreturns"
End Sub

I remind you, they are only examples, you must put the real data.
I also only put 2 example pages, you must put the additional pages, with their respective controls.
 
Upvote 0
Thank you very much again for your fast reply.

I'm gonna try it out tomorrow.
Yes, when page 2 empty there isn't a second correction.

Will reply tomorrow with my findings
 
Upvote 0
Of course.
I gladly support you with any questions.
 
Upvote 0
Hey Dante,

1 day later as intended but i tried it out.

The latest changes don't resolve the problem.
When i only want to fill in page 1 it keeps saying that i need to fill page 2;

Code: 
Private Sub cmdAddOberservation_Click()
  Dim fTextBox As Object, xEptTxtName As String
  Dim sh As Worksheet, lr As Long
 
  Set sh = Sheets("Observations")
 
  For Each fTextBox In Productionreturns.Controls
    If TypeName(fTextBox) = "TextBox" Then
      If fTextBox.Text = "" Then
        xEptTxtName = xEptTxtName & fTextBox.Name & " is empty " & vbNewLine
      End If
    End If
  Next
 
  If xEptTxtName <> "" Then
    MsgBox "The following required fields are not complete;" & vbNewLine & vbNewLine & Chr(13) & xEptTxtName, vbInformation
    Exit Sub
  End If

  If Worksheets("Observations").Range("A2").value = "" Then
    Worksheets("Observations").Range("A2:G2").FormulaArray = "1"
  End If
    
  'PAGE 1
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
  sh.Cells(lr, "A").value = Me.lblGelCodeR.Caption
  sh.Cells(lr, "B").value = Me.lblProductNameR.Caption
  sh.Cells(lr, "C").value = Me.lblBatchNumberR.Caption
  sh.Cells(lr, "D").value = Me.lblBoxR.Caption
  sh.Cells(lr, "E").value = Me.txtStDate.value
  sh.Cells(lr, "F").value = Me.txtCorrectBy.value
  sh.Cells(lr, "G").value = Me.txtEndDate.value
  sh.Cells(lr, "H").value = Me.txtCheckDate.value
  sh.Cells(lr, "I").value = Me.cboDocument.value
  sh.Cells(lr, "J").value = Me.cboDocumentPart.value
  sh.Cells(lr, "K").value = Me.cboPart.value
  sh.Cells(lr, "L").value = sh.Cells(lr - 1, "L").value + 1
 
  'PAGE 2
  If Me.txtStDate2.Text <> "" Then
  lr = lr + 1
  sh.Cells(lr, "A").value = Me.lblGelCodeR.Caption
  sh.Cells(lr, "B").value = Me.lblProductNameR.Caption
  sh.Cells(lr, "C").value = Me.lblBatchNumberR.Caption
  sh.Cells(lr, "D").value = Me.lblBoxR.Caption
  sh.Cells(lr, "E").value = Me.txtStDate2.value
  sh.Cells(lr, "F").value = Me.txtCorrectBy2.value
  sh.Cells(lr, "G").value = Me.txtEndDate2.value
  sh.Cells(lr, "H").value = Me.txtCheckDate2.value
  sh.Cells(lr, "I").value = Me.cboDocument2.value
  sh.Cells(lr, "J").value = Me.cboDocumentPart2.value
  sh.Cells(lr, "K").value = Me.cboPart2.value
  End If

  Call MsgBox("A new observation has been added", vbInformation, "Add observation")
    
  SortIt
  Unload Me
  On Error GoTo 0
  Exit Sub
 
  cmdAdd_Click_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddObservation_Click of Form Productionreturns"
End Sub
 
Upvote 0
Bandito1 said:
Hey Dante,

1 day later as intended but i tried it out.

The latest changes don't resolve the problem.
When i only want to fill in page 1 it keeps saying that i need to fill page 2;
Click to expand...

What message appears to you?
This message appears:
"The following required fields are not complete;"

That is not my code.

Then delete these lines from the code.

VBA Code: 
  If xEptTxtName <> "" Then
    MsgBox "The following required fields are not complete;" & vbNewLine & vbNewLine & Chr(13) & xEptTxtName, vbInformation
    Exit Sub
  End If

And try again.

If you agree, finish testing my code and then I help you fix your code.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,789
Messages
6,121,590
Members
449,039
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top