Solved: Cant get the value of a control in a multipage

[gk039]

Hi all,

I am having a problem with a part of a code in a form that has a multipage in it.
By pressing a button i want to trap the values of the controls and create a report sheet with some of the data entries the user has entered.

The code below:

Private Sub cmdReportSheet_Click()
Dim mpage As Page
Dim ccontrol As Control
Dim Emp() As String                 'Employees array
Dim i As Integer                    'Last row with Emp Name data
Dim j As Integer, k As Integer
Dim ColArr As Integer               'Index of Second dimension in array
Worksheets("Data").Activate
ColArr = 1
k = 3                               'First row with data in Data sheet
i = Range("c2").End(xlDown).Row     'Set value to variable
ReDim Emp(1 To i - 2, 1 To 2)       'Set array
'Fill Emp array with Employees names
For j = 1 To i - 2
        Emp(j, ColArr) = Worksheets("Data").Range("b" & k)
        Emp(j, ColArr + 1) = Worksheets("Data").Range("c" & k) & " " & Worksheets("Data").Range("d" & k)
        k = k + 1
Next j
Worksheets("Report").Activate
ColArr = 1
For j = 1 To UBound(Emp())
        Range("a" & j + 1) = Emp(j, ColArr)
        Range("a" & j + 1).Offset(0, 1) = Emp(j, ColArr + 1)
Next j
'Loop through pages and controls to find controls with no value
For Each mpage In MultiPage1.Pages
    For Each ccontrol In mpage.Controls
    'MsgBox TypeName(ccontrol)
        If TypeName(ccontrol) = "TextBox" Or TypeName(ccontrol) = "ComboBox" Then
            If IsEmpty(ccontrol) Then
                MsgBox ccontrol.Name & " is empty"
            End If
        End If
    Next ccontrol
Next mpage
End Sub

Within the loop I cannot track the info in the controls. If the control is a text box or a combo box and the user has not entered something (ie: age, telephone number, street etc) i want to track the label of the control and copy it to another worksheet.
The msgobx function in the code is just for testing. Ignore it

I hope it is all clear the way i put it

Any help will be much appreciated

Thank you in advance

George

[Kenneth Hobs]

Welcome to the forum!

If you don't use IsEmpty?

[VBA]If ccontrol.Value="" Then[/VBA]

[snb]

or

[vba]Private Sub cmdReportSheet_Click()
with sheets("Data").cells(3,2).resize(sheets("Data").cells(rows.count,3).end(xlu p).row-2,2)
sheets("Report").cells(1).resize(.rows.count,2)=.value
end with
For Each pg In MultiPage1.Pages
For Each ct In pg.Controls
If instr("TextBoxComboBox",typename(ct))>0 Then
If ct.value="" then Then MsgBox ct.Name & " is empty"
End If
Next
Next
End Sub[/vba]

[gk039]

Thanx a lot both of you.
I'll try both and come back for the feedback

Thank you very much!!
George