Prev: wildcard in filenames in macro
Next: Create a new folderand name it the previous month
From: Derek Dowle on 16 Apr 2010 10:18
I am using an Excel 2003 VBA UserForm to enter data onto a worksheet.

On the UserForm I have a Navigation bar to navigate through the records held
on the worksheet to view them on the form; i.e. First, Next, Previous and
Last.

The navigation bar successfully locates each record and the code I use to
gather the data from the worksheet to display on the UserForm also works
successfully, see below 'Sub FillInData()'

The Navigation bar also has a button to enable me to save a new record onto
the Worksheet and then sort the records into the required sequence.

As soon as a new record has been saved and I navigate to another record an
error message appears:

Run-time error '380';
Could not set the property value. Invalid property value.

The line of code causing the problem is

frmBudgetInput.cboLevel4.Value = arrData(2)

The frustrating thing is that if I edit a record and press the save button
and then navigate away the problem does not occur.

Is there a way of determining what property value is Invalid, to give me a
clue how to rectify the problem?


Sub FillInData()
' Populate the forms with data from the WorkSheet
' Data from Budget Input Worksheet to frmBudgetInput

' Go to first record on sheet
Worksheets("Budget Input").Select
Cells(6, 1).Select
iCellValue = ActiveCell.Value
' Find the record
Do While iCellValue <> iCheckRef
ActiveCell.Offset(1, 0).Select
iCellValue = ActiveCell.Value
Loop
' Gather the Data
Dim i As Integer
For i = 1 To 6
arrData(i) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next
' Fill in the data
frmBudgetInput.cboLevel4.Value = arrData(2)
frmBudgetInput.txtCost.Text = arrData(3)
frmBudgetInput.txtDesc.Text = arrData(4)
frmBudgetInput.cboLevel1and2.Value = arrData(5)
frmBudgetInput.cboLevel3.Value = arrData(6)
' return to column A
ActiveCell.Offset(0, -6).Select
End Sub

Many thanks in anticipation

--
Derek Dowle
From: Gary Brown on 16 Apr 2010 13:56
You're not showing us enough to make a determination of your problem. I just
recreated what you've shown us and the code ran fine (had to declare the
iCheckRef only).
I suggest you repost with more info.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Derek Dowle" wrote:

> I am using an Excel 2003 VBA UserForm to enter data onto a worksheet.
>
> On the UserForm I have a Navigation bar to navigate through the records held
> on the worksheet to view them on the form; i.e. First, Next, Previous and
> Last.
>
> The navigation bar successfully locates each record and the code I use to
> gather the data from the worksheet to display on the UserForm also works
> successfully, see below 'Sub FillInData()'
>
> The Navigation bar also has a button to enable me to save a new record onto
> the Worksheet and then sort the records into the required sequence.
>
> As soon as a new record has been saved and I navigate to another record an
> error message appears:
>
> Run-time error '380';
> Could not set the property value. Invalid property value.
>
> The line of code causing the problem is
>
> frmBudgetInput.cboLevel4.Value = arrData(2)
>
> The frustrating thing is that if I edit a record and press the save button
> and then navigate away the problem does not occur.
>
> Is there a way of determining what property value is Invalid, to give me a
> clue how to rectify the problem?
>
>
> Sub FillInData()
> ' Populate the forms with data from the WorkSheet
> ' Data from Budget Input Worksheet to frmBudgetInput
>
> ' Go to first record on sheet
> Worksheets("Budget Input").Select
> Cells(6, 1).Select
> iCellValue = ActiveCell.Value
> ' Find the record
> Do While iCellValue <> iCheckRef
> ActiveCell.Offset(1, 0).Select
> iCellValue = ActiveCell.Value
> Loop
> ' Gather the Data
> Dim i As Integer
> For i = 1 To 6
> arrData(i) = ActiveCell.Value
> ActiveCell.Offset(0, 1).Select
> Next
> ' Fill in the data
> frmBudgetInput.cboLevel4.Value = arrData(2)
> frmBudgetInput.txtCost.Text = arrData(3)
> frmBudgetInput.txtDesc.Text = arrData(4)
> frmBudgetInput.cboLevel1and2.Value = arrData(5)
> frmBudgetInput.cboLevel3.Value = arrData(6)
> ' return to column A
> ActiveCell.Offset(0, -6).Select
> End Sub
>
> Many thanks in anticipation
>
> --
> Derek Dowle
 | 
Pages: 1
Prev: wildcard in filenames in macro
Next: Create a new folderand name it the previous month