Using multipage to add data to worksheet

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
80
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"?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,711
Office Version
2007
Platform
Windows
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
 

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
80
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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,711
Office Version
2007
Platform
Windows
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?
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.
 

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
80
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,711
Office Version
2007
Platform
Windows
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.
 

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
80
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,711
Office Version
2007
Platform
Windows
Of course.
I gladly support you with any questions.
 

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
80
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,711
Office Version
2007
Platform
Windows
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;
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.
 

Forum statistics

Threads
1,077,779
Messages
5,336,254
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top