Application or object error

[KenCriss]

I have the following code in Userform_initialize.

Set tbl = ActiveCell.CurrentRegion
     tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select

At work I have Excel 2002(I think) and at home I have Excel 2000. This code has no problem at work but at home I get "Application-defined or object-defined error"

It blows up on the tbl.resize line and not the "Set" line.

I have not a clue what would be causing this unless it's a difference in versions. Has anyone heard of this?

 

[Tazguy37]

At work I have Excel 2002(I think) and at home I have Excel 2000. This code has no problem at work but at home I get "Application-defined or object-defined error"

It blows up on the tbl.resize line and not the "Set" line.

I have not a clue what would be causing this unless it's a difference in versions. Has anyone heard of this?

I just ran it on my work machine. This one has XL2k, so it's not a version problem.

My sheet looks like this:

Book2
	A	B	C	D
1	1	2	5
2	6	3	4
Sheet1

Just a bunch of numbers I threw in. What's your data look like?

 

[KenCriss]

Here is the entire part of that code.

Private Sub UserForm_Initialize()
     Dim col_ctr As Long
     Dim tbl As Range
     TB_Filename.Value = "TextFile.csv"
     TB_Path.Value = ActiveWorkbook.path & "\"
     Set tbl = ActiveCell.CurrentRegion
     tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
     tbl.Columns.Count).Select

     Ref_Src.Value = Selection.Address(False, False)
     
End Sub

The data does not seem to be the problem, as it never even brings up the form. This error happens upon loading the form. If I comment out the line
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
then the form loads/shows like I want it to. By the way, I am trying to load it by using Userform1.Show, but that does not seem to be the problem.

 

[KenCriss]

I will also note that this code is being activated by the userform being shown.

Option Explicit

Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetDirectory(Optional msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, X As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(msg) Then
        bInfo.lpszTitle = "ABC, Inc. - Select a folder."
    Else
        bInfo.lpszTitle = msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    X = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal X, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function


Function CSV_Dir() As String
    Dim msg As String
    msg = "Aon Consulting, Inc. Please select a location for the PP+ CSV file."
    CSV_Dir = GetDirectory(msg)
    If Right(CSV_Dir, 1) <> "\" Then CSV_Dir = CSV_Dir & "\"
End Function

 

[KenCriss]

I don't get it. I took out lines I had commented in the Initialize, and now it runs! That was weird. Tazguy, thanks anyway!