Multiple Userforms and Debug Error

[miami2k]

Dear All

I'm working on this project.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

For now, I'm focusing on the first page of the Userform Scadenziario, the one called TimeSheet. I have three issues:
1. When I open the project on a different PC it stops and gives me an error saying that the function Caricadati is not defined. I have to run it manually, close, save and then it works.
2. If I click on view records it opens the second userform "Edit Time Sheet" and if double-click any record it opens the third userform to edit the single record. Everything ok, so far. But if I close the third userform and I double-click again on any record it does not come up anymore.
3. If I debug and try to start the project again it gives me an error about a With function. I have to save the file, close and restart.

Thank you in advance.


miami2k

 

[rlv01]

I think you need to deal with those one by one, so I'll start with problem 1. #1 is because you have a host of compile errors. Most having to do with undefined variables. Here's one example:

There is no declaration (Dim statement) for variable "companies". You should fix these errors so that your code compiles successfully:

Then I think problem 1 will go away.

 

[rlv01]

For #2, the specific error is that on the 2nd pass you are trying to assign an illegal ListIndex value to the comboboxes. You don't see it because you turned off the runtime error message that would have alerted you to the problem with an On Error Resume Next statement in the listbox doubleclick code . On Error Resume Next should only be used under carefully understood circumstances. Here it is masking a bad search range and all the follow-on errors that is causing.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i
    'Sheets("Workings").Select

    'Loop through every item in the ListBox
    For i = 1 To ListBox1.ListCount - 1

        'Check if the item was selected.
        If ListBox1.Selected(i) Then

            ValCol0 = ListBox1.List(i, 0)
            ValCol1 = ListBox1.List(i, 1)
            ValCol2 = ListBox1.List(i, 2)
            ValCol3 = ListBox1.List(i, 3)
            ValCol4 = ListBox1.List(i, 4)
            ValCol5 = ListBox1.List(i, 5)
            ValCol6 = ListBox1.List(i, 6)
            ValCol7 = ListBox1.List(i, 7)
            ValCol8 = ListBox1.List(i, 8)
            ValCol9 = ListBox1.List(i, 9)

            On Error Resume Next  '<- this is masking some serious errors 

            Dim SearchRange As Range
            Dim FindRow As Range

            'Trova ID
            Sheets("Timesheet").Select
            Set SearchRange = Range("A1", Range("A65536").End(xlUp))
            Set FindRow = SearchRange.Find(ValCol0, LookIn:=xlValues, lookat:=xlWhole)
            FValCol0 = FindRow.Row

            'Trova Soc
            'Sheets("Workings").Select
            Set SearchRange = Sheets("Workings").Range("B2", Range("B65536").End(xlUp))
            Set FindRow = SearchRange.Find(ValCol3, LookIn:=xlValues, lookat:=xlWhole)
            FValCol3 = FindRow.Row
            FValCol3 = FValCol3 - 1

            'Trova Attiv
            'Sheets("Workings").Select
            Set SearchRange = Sheets("Workings").Range("H2", Range("H65536").End(xlUp))
            Set FindRow = SearchRange.Find(ValCol4, LookIn:=xlValues, lookat:=xlWhole)
            FValCol4 = FindRow.Row
            FValCol4 = FValCol4 - 1


            'Trova SubAct
            'Sheets("Workings").Select
            'Set SearchRange = Range("H2", Range("H65536").End(xlUp))
            'Set FindRow = SearchRange.Find(ValCol4, LookIn:=xlValues, lookat:=xlWhole)
            'FValCol4 = FindRow.Row
            'FValCol4 = FValCol4 - 1
            EditTSRecord.Show
            GoTo Fineciclo
        End If

    Next i

Fineciclo:

End Sub

Instead, you should add code to specifically deal with the chance that SearchRange is bad. For example:

            ValCol8 = ListBox1.List(i, 8)
            ValCol9 = ListBox1.List(i, 9)

            Dim SearchRange As Range
            Dim FindRow As Range

            'Trova ID
            Sheets("Timesheet").Select

            On Error Resume Next
            Set SearchRange = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
            On Error GoTo 0                           'restore default runtime error mechanism
            If Not SearchRange Is Nothing Then
                Set FindRow = SearchRange.Find(ValCol0, LookIn:=xlValues, lookat:=xlWhole)
                FValCol0 = FindRow.Row
                Set SearchRange = Nothing             'reset range for next test (i.e. Trova Soc)
            Else                                      'your code to handle the error condition
                MsgBox "Invalid Search Range", vbCritical, "Error"
                'Exit Sub
            End If

 

[miami2k]

Thank you for your help. Part of the problem is solved.
Unfortunately I still get this error when I launch the file on a different PC. Every time I copy the file from my laptop to the office I get this error:

What I do to solve this issue, is to run CaricaDati from the module, Save, Close and reopen and it works until I move the file to another PC.

Any suggestions to solve the problem? This is the most recent version of the file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

miami2k

 

[rlv01]

Well, my PC is different and I do not get that error, or any compile error when I open your file. I suspect that somehow you are opening an old version of the workbook on that other PC, not the new version you have fixed.

 

[Jaafar Tribak]

Did you try making the CaricaDati SUB in Modulo1 explicitly Public

Public Sub CaricaDati()

 

[miami2k]

Dear All,

I'm desperate! I'm stuck on this problem. Every time I switch PC (and therefore when I give the file to the users) I get the message that the Sub Caricadati is not defined but it is (image attached).
The sub is defined in module1. I've tried to define it as private, public, option explicit. I've tried to call it from the initialize worksheet or from initialize userform.
I've tried to run it from the immediate window and it returns the same error.
If I manually run it works, I change something, save and reopen and the file works, until I change PC again.
The file is here in the dropbox 200518.01 Deadlines.xlsm

I suppose that this is due to the version of Excel or some different settings. But I can't work it out.

Please help!

Thank you

 

[rlv01]

I do not see that behavior at all when I download your worksheet and run it. I still think you have users who have multiple versions on their PCs. If not then one thing you could try is to move all the Workbook_Open code to an auto_open sub that you create in module1.

 

[miami2k]

The file is the same. Dowloaded from the Dropbox. Tomorrow I will try to upload a video to show the behavior.

Thanx

 

[rlv01]

The file is the same. Dowloaded from the Dropbox. Tomorrow I will try to upload a video to show the behavior.

Thanx

You don't need to upload a video. I understand the behavior, I'm just reporting to you that I do not see it when I open your file. The downloaded version can compile and run the macros w/o issue.