Multiple Userforms and Debug Error

miami2k

Board Regular
Joined
Nov 1, 2017
Messages
55
Dear All

I'm working on this project.

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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773
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:

1589731701278.png


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

1589731793318.png


Then I think problem 1 will go away.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773
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.

VBA Code:
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:

VBA Code:
            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

Board Regular
Joined
Nov 1, 2017
Messages
55
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:

1590083053633.png


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:


miami2k
 

Attachments

  • 1590082720548.png
    1590082720548.png
    113.9 KB · Views: 1

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
Did you try making the CaricaDati SUB in Modulo1 explicitly Public

VBA Code:
Public Sub CaricaDati()
 

miami2k

Board Regular
Joined
Nov 1, 2017
Messages
55

ADVERTISEMENT

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
 

Attachments

  • 1590083053633.png
    1590083053633.png
    65.8 KB · Views: 3

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773
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

Board Regular
Joined
Nov 1, 2017
Messages
55
The file is the same. Dowloaded from the Dropbox. Tomorrow I will try to upload a video to show the behavior.

Thanx
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,244
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top