Application Error when running Macro on Workbook open

  • Thread starter Thread starter TM
  • Start date Start date
T

TM

I posted this a couple of weeks ago but got no response.
So in a spirit of perseverance (optimism even ;-)) here we
go again...

I have a multipage form that I'd like to open when a
workbook is opened. It reads values from various defined
Names of the workbook to populate the form fields. The
user can then change the values and on pressing 'OK'
button the values should be validated and if appropriate
re-copied back into the cells referenced by the Names.
This works fine when I display the form from the already
opened workbook, but the process fails with Error 1004
("Application-defined or object-defined error") when the
form is called from the Workbook_Open() procedure. The
procedure crashes on the following line:

ActiveWorkbook.Names(arrControlNames(intCurrentControl,
2)).RefersToRange.Value = _
Controls(arrControlNames(intCurrentControl, 1)).Value

Where arrControlNames is an array to store details of text
controls and associated name such that
arrControlNames(x,1) is the ControlName
and arrControlNames(x,2) is the associated RangeName


Full code extract is below.

Can anyone shed any light on this?

Thanks in advance

TM

==================
Private Sub Workbook_Open()
Load frmProjectProperties
frmProjectProperties.Show
End Sub

Private Sub cmdOK_Click()
'First, validate all values - if invalid then exit sub
'... {Validation Code}
'...


'If we reach this point then all values have tested
valid
'Update values to worksheet and close form
For intCurrentControl = LBound(arrControlNames, 1) To
UBound(arrControlNames, 1)
If arrControlNames(intCurrentControl, 1) <> "" Then
ActiveWorkbook.Names(arrControlNames
(intCurrentControl, 2)).RefersToRange.Value = _
Controls(arrControlNames
(intCurrentControl, 1)).Value
End If
Next


Unload frmProjectProperties

End Sub
 
Hi,

I would suggest to move your code that shows the form out
of the Thisworkbook module, like this:


Private Sub Workbook_Open()
Application.OnTime Now,"ShowYourForm"
End Sub

And in a normal module:

Sub ShowYourForm()
Load frmProjectProperties
frmProjectProperties.Show
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Back
Top