Control Array not showing all the information it should show.

[glossy001]

Help Please
My form only shows information in the first few text boxes and the first text box (TxtBxPrimaryNo) shows the dated in stead of the information (just numbers) that it should show.
many thanks in advance.


Sub FormData(ByVal Form As Object, ByVal sh As Object, ByVal RecordRow As Long, ByVal Action As XLActionType)

Dim i As Integer
Dim CalDate As Variant
Dim msg As String
Dim ctrl As msforms.Control

On Error GoTo ExitSub
With sh
'add password if required
.Unprotect Password:=""
Select Case Action
Case xlNew

EnableButtons Form:=Form, NewButton:=False, CancelButton:=True, DeleteButton:=False, AddUpdateButton:="Add"
'
'Form.Calendar.Value = Date
'THERE IS NO CALANDER ON THIS FORM

'clear form
For Each ctrl In Form.Controls
If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl

Case xlAdd, xlUpdate
'validate required entry

For Each ctrl In Form.Controls
If Not IsComplete(ctrl) Then GoTo ExitSub
Next ctrl

For i = 0 To 50
If i = 0 Then
.Cells(RecordRow, 1).Offset(0, i).Value = Form.Controls(ControlArray(i)).Value
Else
.Cells(RecordRow, 1).Offset(0, i).Value = Form.Controls(ControlArray(i)).Value
End If
Next

EnableButtons Form

msg = IIf(Action = xlAdd, "New Record Added To Database", "Record Updated")

MsgBox msg, 48, Left(msg, 16)

Case xlScrollRow
For i = 0 To 50
If i = 0 Then
CalDate = .Cells(RecordRow, 1).Offset(0, i).Value
CalDate = IIf(IsDate(CalDate), CDate(CalDate), Date)
Form.Controls(ControlArray(i)).Value = CalDate
Else
Form.Controls(ControlArray(i)).Value = .Cells(RecordRow, 1).Offset(0, i).Value
End If
Next i

EnableButtons Form

Case xlDelete
Application.EnableEvents = False
.Cells(RecordRow, 1).EntireRow.Delete xlShiftUp
MsgBox "Record Deleted", 48, "Record Deleted"

End Select
'add password if required
'.Protect Password:=""
End With
ExitSub:
Application.EnableEvents = True
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Sub EnableButtons(ByVal Form As Object, Optional ByVal NewButton As Boolean = True, Optional ByVal CancelButton As Boolean, _
Optional ByVal DeleteButton As Boolean = True, Optional ByVal AddUpdateButton As String = "Update")
With Form
.ButtonCancel.Enabled = CancelButton
.ButtonNew.Enabled = NewButton
.ButtonDelete.Enabled = DeleteButton
.ButtonAddUpdate.Caption = AddUpdateButton
End With
End Sub
Function ControlArray() As Variant


ControlArray = Array("TxtBxPrimaryNo", "TxtBxModule", "TxtBxParentFler", _
"TxtbxFler", "TxtBxFlerDescription", "TxtBxAssetclass", "TxtBxCommdate", "TxtBxAssetRepVal", _
"TxtBxReliability", "TxtBxPerformance", "TxtBxExternalCondition", "TxtBxObsolescence", _
"TxtBxOverallCondition", "CmboBxFailuremode", "TxtBxFailureEffects", "TxtBxMTBF", "TxtBxAvgMainCost", _
"TxtBxProductionLossHrs", "TxtBxSafManHrs", "TxtBxRiskYear", "TxtBxUnplannedActual", _
"TxtBxPlannedActual", "TxtBxWk1", "TxtBxWeek2", "TxtBx1Mnth", "TxtBx2Mnth", "TxtBx3Mnth", "TxtBx6Mnth", _
"TxtBx1Y", "TxtBx2Yr", "TxtBx3Yr", "TxtBx4Yr", "TxtBox5Yr", "TxtBx7Yr", "TxtBx10Yr", "TxtBx12Yr", _
"TxtBx25Yr", "TxtBxParentWk1", "TxtBxParentWk2", "TxtBxParent1Mnth", "TxtBxParent2Mnth", "TxtParent3Mnth", _
"TxtBxParent6Mnth", "TxtBxParent1Yr", "TxtBxParent2Yr", "TxtBxParent3Yr", "TxtBxParent4Yr", "TxtBxParent5Yr", _
"TxtBxParent7Yr", "TxtBxParent10Yr", "TxtBxParent12y", "TxtBxParent25Yr", "CmboBxTaskStatus", _
"TxtBxMaintenanceComments", "TxtBxGeneralComments")

End Function

 

[StephenCrump]

TxtBxPrimaryNo will show a date because that's what your code specifies for i = 0:

For i = 0 To 50
    If i = 0 Then
        CalDate = .Cells(RecordRow, 1).Offset(0, i).Value
        CalDate = IIf(IsDate(CalDate), CDate(CalDate), Date)
        Form.Controls(ControlArray(i)).Value = CalDate
    Else
        Form.Controls(ControlArray(i)).Value = .Cells(RecordRow, 1).Offset(0, i).Value
    End If
Next i

The ith control will be assigned the value of sh.Cells(RecordRow, i + 1) so you might check that these values are correct.

I also note that ControlArray contains 55 elements, so your loop from 0 to 50 won't set every value.

 

[glossy001]

Hi Stephen
Thank you for answering my question but that didn't fix the problem how do I get the code to show the data in the cell not the date, changed the control array to 55 (as per your advice ) and rechecked my spelling but the form still won't show all the data in the cells.
stumped
GG

 

[StephenCrump]

I assume we're talking about the piece of code that distils down to:

With sh
    Select Case Action
    Case xlScrollRow
    For i = 0 To 54
        Form.Controls(ControlArray(i)).Value = .Cells(RecordRow, i + 1).Value
    Next i
End With

So if sh is Sheet1, and RecordRow is 2, say, then

TxtBxPrimaryNo will be assigned the value of Sheet1!A2
and similarly
TxtBxModule = Sheet1!B2
TxtBxParentFler = Sheet1!C2
...
TxtBxGeneralComments = Sheet1!BC2

Is this not working?

At present, you 're calling the ControlArray function each time you iterate through the loop. It would be more efficient to set the values of ControlArray once upfront, inside Sub FormData.

Alternatively, perhaps you could have both the textbox names and the values to be assigned inside the one range in Excel, perhaps something like this:

Dim vTextBoxes As Variant
Dim i As Long

vTextBoxes = Range("MyTextBoxRange").Value

For i = 1 To UBound(vTextBoxes)
    Form.Controls(vTextBoxes(i, 1)).Value = vTextBoxes(i, 2)
Next i

where:

Excel 2010

	A	B
TxtBxPrimaryNo	Value 1
TxtBxModule	Value 2
TxtBxParentFler	Value 3
etc
MyTextBoxRange:	A1:B55

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

</tbody>

1
​

 

[glossy001]

Hi Stephen
Thanks for taking the time to answer but I didn't understand any of that I'm just a newbie at VB, I have had to forget the form idea and go back to manually scrolling across the sheet time consuming I know but never mind I will keep studying and try to figure out VB.
thanks for your valuable time
GG