Nbr:0 Error

[johndmingione]

Dear Reader,

I made a program to view Excel Worksheets within a VSFlexGrid and everything works except I'm getting small errors here and there and I'm not sure why. For example, the Title of this Thread is Nbr:0 Error because when a person Opens his Worksheet in the program it gives him that error message. There is a color coding button that colors a specific row in the VSFlexGrid as well as the Excel Spreadsheet. However, on one computer in the office (that has 6 duplicate computers like it) it has that error message previously listed.

In another case, the color coding button seems to be permanent on another machine in the office. What I mean is that hypothetically someone colors a row in the VSFlexGrid green. The corresponding row in Excel gets colored Green. Say you made a mistake, however, and wanted to re-color the row White, to it's original color. You would re-color it, save your work, and when you open it back up that Row is still Green. The only workaround to this we know of is opening up the Excel Worksheet and re-coloring the Green row back to White from there.

I'd like some insight on why this is happening. Why would it be only on one computer and not the other IDENTICAL 10 others we have (Dell Vostros x64)? Has anyone seen errors like this before? Let me know. I'd love to have a discussion.

[seenu_1st]

try that particular coloring code in a seperate project (just for checking purpose) in each machine, so u can find where the problem exactly, atleast u may knw whether this is the probelm or not.

[Bobbles]

Can you post your coloring code.
I have a suspicion as to what is happening

[johndmingione]

Pretty sure this is all of it regarding color:

Private Sub ApplyColor(PColor As Long)
Dim NewRange As Excel.Range, RepairOrderNumber As String, FromRow As Long, ToRow As Long, i As Long, bConBlizOpen As Boolean

    On Error GoTo ErroHandler
       
    CloseConnectionRO
    CloseConnectionBlitZTip bConBlizOpen
    
    RemoveReferenceToExcel
    ConnectToExcel
        
    Screen.MousePointer = vbHourglass
    
    With fgROList
        .FillStyle = flexFillRepeat
        .Col = .FixedCols
        .ColSel = .Cols - 1
        .CellBackColor = PColor
        .FillStyle = flexFillSingle
    End With
    
    RepairOrderNumber = fgROList.TextMatrix(fgROList.Row, 0)
    
    If IsNumeric(RepairOrderNumber) Then
        With objSH.UsedRange
            Set NewRange = objSH.Range("A" & .Row & ":" & "A" & .Rows.Count)
            
            FromRow = objExcel.WorksheetFunction.Match(CLng(RepairOrderNumber), NewRange, 0)
            
            ToRow = FromRow
            
            Do While NewRange.Cells(ToRow, 1) = RepairOrderNumber
                ToRow = ToRow + 1
                If ToRow > .Rows.Count Then Exit Do
            Loop
            ToRow = ToRow - 1
                        
            Set NewRange = Nothing
            Set NewRange = .Range("A" & FromRow & ":" & "P" & ToRow)
            NewRange.Interior.Color = fgROList.CellBackColor
        End With
    End If
    
    objWB.Save
    Set NewRange = Nothing
    RemoveReferenceToExcel
    
    OpenConnectionRO strROBlitZFilePath
    If bConBlizOpen Then OpenConnectionBlitZTip strROBlitZFilePath
    
    Screen.MousePointer = vbDefault

Exit Sub
ErroHandler:
    Screen.MousePointer = vbDefault
    If cnnRO.State <> 1 Then OpenConnectionRO strROBlitZFilePath
    If bConBlizOpen And cnnBlitZTip.State <> 1 Then OpenConnectionBlitZTip strROBlitZFilePath
    MsgBox err.Description, vbCritical, "Error Nbr:" & err.Number
End Sub
    
Private Sub cmdApplyColor_Click()
    ApplyColor cmldColor.Color
End Sub

Private Sub cmdSelectColor_Click()
    With cmldColor
        .Flags = cdlCCRGBInit Or cdlCCFullOpen
        .ShowColor
        lblColor.BackColor = .Color
    End With
End Sub

If it's not enough information let me know. Otherwise, I'm going to keep looking. I feel like I'm missing a piece that maybe I misplaced or put somewhere else.

[Bobbles]

OOPs Just looked a bit further down the code

[johndmingione]

I'm confused. What?

[Bobbles]

I'm confused. What?

I saw your name cmldColor and asked if it was a common dialog.
Then I read your last sub, and realized it was a common dialog, so I quickly removed my question, and replaced it with an OOPs

[johndmingione]

Ohh haha yes it is Common Dialog.

[Bobbles]

Do you have VB6 installed on your playing up machines.
If so, you could run your project on them instead of a compiled EXE.
Then you can place some appropriate Debug.Prints to see if you can work out what is happening.
If you cannot do that, it is possible to have a debug.print (cough) that writes to a log file

[Bobbles]

Here are some thoughts

Is the value returned from a common dialog a Long ?
(I have avoided using MS's dependencies)

Does your grid have cell ItemData property (presumably Longs) ?
And are they free (not being used) ?
What if you stored the long color value into there.
And then set the grid's backcolor.
And later, when you use your grid's cell backcolor, use the ItemData value instead.

PS IF IT LACKS A CELL ITEMDATA PROPERTY
For your experiments you could store it as a string in the cell text property, and convert it to long, and use that instead of the grids cell backcolor.
Not permanently, just as an experiment to see if that avoids your problem

[DataMiser]

Afraid I can't be of much help as I have never used this VSFlexGrid and I avoid coding for or in Excel. That said what version of excel are you using and is it the same version on all the PCs?

The other question would be are you actually coding in VB6 or are you coding in VBA?

This info may help someone else help solve your problem.

[johndmingione]

The Excel I use is the same as everyone else, and I am coding in VB6 not VBA. In regards to Bobbles, I'll review everything you said and start experimenting.

[jcis]

What Workbook are you using? it's one of those you sent to me the other time? what's the workbook name?

If you don't start debugging your code, you won't have a clue not even in which line the error is being fired, the problem with that is if you don't have at least that info nobody here will understand how to help you. Change to "break on all errors" so execution flow wont go into error handlers, then you can see the error in the exact line it's being fired.

[DataMiser]

The Excel I use is the same as everyone else, and I am coding in VB6 not VBA.

Ok, you still have not told us what version of Excel it is though, It may not matter but there are differences from one version to the next.

[johndmingione]

2010. 14.0 in VB Terms.

[johndmingione]

I figured since everyone uses the same Computer, same program, and same everything that it had to be a problem with the Worksheet. So I sent the worksheet to my computer and bam, it messed up. Debugged it. And it gave me this error message

Run-time error '-2147021892 (80070bbc)

Office has detected a problem with this file. To help protect your computer this file cannot be opened.

Any ideas?

[johndmingione]

Upon clicking Debug it sends me to this section

Private Sub ConnectToExcel()
    On Error GoTo ErrHandler
    
    If objExcel Is Nothing Then
        Set objExcel = New Excel.Application
        Set objWB = objExcel.Workbooks.Open(strROBlitZFilePath)
        Set objSH = objWB.Sheets("ROS")
    End If
    
    objExcel.DisplayAlerts = False

Exit Sub
ErrHandler:
    err.Raise err.Number, err.Source, err.Description & vbNewLine & "In Sub: ConnectToExcel()", err.HelpFile, err.HelpContext
End Sub

and points to this line

Set objWB = objExcel.Workbooks.Open(strROBlitZFilePath)