RunTime Error 438

[itsmarkdavies]

Hi , i have been given an Excel 2000 application to support and all our new office PC's are running Excel 2002 / 2003.

The application uses quite a lot of VBA behind the scenes, and when i try to load the app on the new PC's, i get "RunTime Error 438" (and also "RunTime Error 57121" after i click on "End" in reply to the message box).

Can anyone tell me what the cause may be please ? - my knowledge of Excel and VBA is very limited.

Thanks for your assistance.


mark.davies@npower.com

 

[SkipVought]

Hi,

Run the procedure again. When it errors, hit Debug and post the code up to and including that statement.

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[itsmarkdavies]

Thanks Skip, please find code below. The lines beginning with "XXXXX" appear to be the ones causing the problem :-


==============================
Code causing RunTime Error 438
==============================

Public Function SetOrgFilter()

Dim sSQL As String
Dim rsOrgIDFilter As Recordset
Dim i
Dim sNTLogon As String
Dim otxtFilterDisplay As TextBox

' Get default org for this user.
sNTLogon = MyToolBox.OSUserName()
sSQL = "SELECT orgShortName,uo_orgID,uoDefault FROM vwUserOrg WHERE uo_uNTLogon='" + sNTLogon + "' AND uoDefault = 'Y' ORDER BY OrgShortName"

Set rsOrgIDFilter = MyToolBox.DBQueryToRecordset(CONNECTSTRING, sSQL)

XXXXX If Worksheets("MyView").txtOrgFilter_ShortNames.Visible = False Then
Worksheets("MyView").txtOrgFilter_ShortNames.Visible = True
Worksheets("MyView").txtOrgFilter_ShortNames = rsOrgIDFilter.Fields(0)
Worksheets("MyView").txtOrgFilter_ShortNames.Visible = False
Else
Worksheets("MyView").txtOrgFilter_ShortNames = rsOrgIDFilter.Fields(0)
End If

Worksheets("MyView").txtOrgFilter_IDs = "'" + Trim(rsOrgIDFilter.Fields(1)) + "'"
Worksheets("MyView").txtOrgFilter_ShortNamesSQL = "'" + Trim(rsOrgIDFilter.Fields(0)) + "'"
SetOrgFilter = " WHERE org IN(" + Worksheets("MyView").txtOrgFilter_ShortNamesSQL + ")"

End Function







================================
Code causing RunTime Error 57121
================================

Private Sub cmdAQAppeals_Click()

'set filter for AQ Appeals view
strViewBy = "AQ Appeals : AQs by AQ Status, where sites live in Portfolio"

Worksheets("MyView").lblTitle.Caption = "AQ Appeals"


' Populate default view.
XXXXX Worksheets("MyView").LoadMyView


' Add blank entry to search combo.
Worksheets("MyView").cboSearchFor.AddItem ""

Worksheets("MyView").txtOrgFilter_ShortNames.Visible = True
Application.ScreenUpdating = True
Worksheets("MyView").MyViewProgress ""

End Sub




mark.davies@npower.com

 

[SkipVought]

Hi,

You appear to not be using worksheet objects properly.

what are...
[tt]
txtOrgFilter_ShortNames
LoadMyView
[tt]
???

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[itsmarkdavies]

Hi Skip, it looks like "txtOrgFilter_ShortNames" is just the name of a Text Box on the Worksheet, and "LoadMyView" is a Function behind the Worksheet.

NB :- All the code works perfectly if run in Excel 2000, it is only when i try to run it on Excel Version 2002 or 2003 that i have these problems. Thanks for your help.

Mark.



mark.davies@npower.com