userform error 424

[dwagner3210]

I am pretty much a newb re macros. I have a client who has asked me to troubleshoot a macro that was working for a long time. the PC crashed and now the macro does not. I helped them resolve some reference issues when they reloaded the OS. However, they now have a runtime error 424 object required that keeps coming up. It highlights the subroutine bolded at end of this code. I have tried renaming the userform but to no avail. It is running on winxp sp3. Here is code from the module. not even sure if that is what someone needs to troubleshoot. Any suggestions?

Private Sub DoEvent()
    UserForm1.DoEvent ' This is called when timer event fires
End Sub
   
Sub LoadData()
'
' ups Macro
' Macro recorded 11/4/2003 by L Flowers
' Keyboard Shortcut: Ctrl+r
'
' Modified by JLW: Added the following functionality:
'   Barcode and Scale Reading capability, and modularized it for more than one PC.
    Dim CSVFile As String
   
    Columns("A:A").EntireColumn.AutoFit
    Range("A250").Select
    ActiveWindow.ScrollRow = 1
    
    'Prior to loading data, sort the scanned information
    Columns("A:G").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    ' Now looks for txt.csv in the same folder as the spreadsheet
    CSVFile = VBAProject.ThisWorkbook.Path & "\txt.csv"
    
    On Error GoTo NoCSVFile 'If we can't load the UPS csv data file, let user know why!
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & CSVFile, Destination _
        :=Range("E1"))
        .Name = "txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
        .Refresh BackgroundQuery:=False
    End With
    Range("E1").Select
    Selection.ClearContents
    CurrentRow = 1
    Columns("E:E").Select
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns("F:F").Select
    Selection.NumberFormat = "General"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]=RC[-1]"
    Range("F1").Select
    Selection.Copy
    Range("F2:F1000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Put focus back on originating cell
    Range("A1").Select
    Exit Sub
    
NoCSVFile:
    MsgBox "The UPS Data file was not found in the same folder as this spreadsheet. Please ensure that it does.", , "UPS CSV Data File Missing"
    Exit Sub
    
End Sub
Sub ScaleReader()
    UserForm1.Show
    
End Sub

 

[RoryA]

You need to press f8 when the error occurs and step into the code in the userform itself to find out where the true error is.

 

[dwagner3210]

You need to press f8 when the error occurs and step into the code in the userform itself to find out where the true error is.

Ok when I press F8 it stops on this line of the userform intialize bolded below. After some research it appears it may be related to an MSCOMM object not being created. But I am not quite sure how to verify it setup..is it the regsvr32 mscomm32.ocx command, is it the references being correct or some property of the form? or am I off base.

Private Sub UserForm_Initialize()
MSComm1.CommPort = 1 ' Set the port number
ScaleOutColumn = "B" ' Change this to the desired output column
BarCodeColumn = "A" ' Output column of Bar Code
NoteColumn = "G"
HighResMode = False

 

[RoryA]

That error suggests you do not actually have an MSCOMM object on the form at all (or its name is different).

 

[dwagner3210]

That error suggests you do not actually have an MSCOMM object on the form at all (or its name is different).

how do i determine if the MSCOMM object is on the form?

 

[RoryA]

If you look at the form in design view can you see it?

 

[dwagner3210]

If you look at the form in design view can you see it?

not sure if this is what you mean but i can see the form but see nothing that would tell me MSCOMM is on object.

https://www.box.com/s/4vnlk93uvoqpermxarp4

 

[RoryA]

Nor can I (though a picture is not terribly helpful). In the userform code module, if you type Me. the intellisense will list MSCOMM1 as an available item if the control is present

 

[dwagner3210]

Nor can I (though a picture is not terribly helpful). In the userform code module, if you type Me. the intellisense will list MSCOMM1 as an available item if the control is present

Rory not sure if this is where you meant or what you meant. But i pulled up the code for the userform initialization. PS This in design mode inside of excel. And typed Me. as instructed (noted in bold below) and nothing happened. Is this what you meant?

Are there other ways to confirm MSCOMM is available to the form


Private Sub UserForm_Initialize()
Me.
MSComm1.CommPort = 1 ' Set the port number
ScaleOutColumn = "B" ' Change this to the desired output column
BarCodeColumn = "A" ' Output column of Bar Code
NoteColumn = "G"
HighResMode = False

' Shut off bold if it's on
Columns("A:Z").Select
Selection.Font.Bold = False

' To keep barcode readings legitimate, format all cells as text
Columns("A:K").Select
Selection.NumberFormat = "@"

 

[RoryA]

In the VB Editor, under Tools-Options, on the Editor tab, do you have the 'Auto List Members' option checked? If so, you should get a list of available properties of the form as soon as you typed the period after Me

At any rate it would appear unlikely that the control is there. If you select Tools-References in the VB Editor, can you see a reference to the COMMS library? If so, does it have 'MISSING:' in front of it?