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?

If ccontrol.Value="" Then

[snb]

or

Private Sub cmdReportSheet_Click()
with sheets("Data").cells(3,2).resize(sheets("Data").cells(rows.count,3).end(xlup).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

[gk039]

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

Thank you very much!!
George