Coded and working in Excel 2016 but not working in 2010.

[daithiboy]

Hi All,

I did up a simple userform based rig to record attendances at various meetings in a hospital. I did it all on my work laptop which runs excel 2016. It was working beautifully so I sent it to my boss to get the final go ahead, and what happened when he clicked one of the command buttons - RUN-TIME ERROR! Brilliant. Fabulous. Went back to my laptop and it works perfectly. I can't fathom it.

I work offsite and have no way of testing my code to figure out what is wrong as it is working on my computer.

Would any of you fine ladies & gentlemen be willing to run it in a different version to 2016 and give me some guidelines? I don't think I can upload a workbook here though?

Any help you can offer is, as always, greatly appreciated.

Thanks,
Dave

 

[jasonb75]

Without knowing exactly what runtime error your code is encountering and which line is causing it there would be more guesswork than anything else.

Either use of a feature that was introduced after the 2010 edition, references to invalid file paths (to another file) or missing references in the vba library (vba editor menu Tools > References) would be my first guesses.

 

[daithiboy]

Without knowing exactly what runtime error your code is encountering and which line is causing it there would be more guesswork than anything else.

Either use of a feature that was introduced after the 2010 edition, references to invalid file paths (to another file) or missing references in the vba library (vba editor menu Tools > References) would be my first guesses.

Hi Jason,

Cheers for getting back so quick.

That's the thing, I can't check the error because it works on my laptop. As far as I'm aware, there are no external references or file paths. It's a pretty straightforward workbook.

I do remember that the runtime error that occurred was one that I had not seen before and was quite wordy.

Is there anyway of sharing the workbook to see if it would work on other versions??

 

[RoryA]

Do you know the error number? Can you post the code for the button-click event?

 

[daithiboy]

Do you know the error number? Can you post the code for the button-click event?

I don't know the error code but I can post the code.

A quick explanation - when I choose a row in the listbox; the checkboxes and textboxes are updated depending on the listbox row. I can then amend the details and click "update" to run the code below and make the changes.

The code updates this worksheet, in fact the listbox is populated using .list and a dynamic range of the sheet below. I use an advanced filter on another userform to only show the attendees at meetings I've selected - this works fine.

Private Sub Update_Click()
    
Dim i As Long
Dim Doctor As String: Doctor = Me.Consultant.Value
Dim Lrow As Long: Lrow = Lists.Range("C" & Rows.Count).End(xlUp).Row



    For i = 2 To Lrow

        If Doctor = Lists.Cells(i, 3) Then
        
            Lists.Cells(i, 3).Value = AddConsultant.Consultant.Value
            Lists.Cells(i, 4).Value = AddConsultant.Specialty.Value
            Lists.Cells(i, 5).Value = AddConsultant.Hospital.Value
            Lists.Cells(i, 6).Value = AddConsultant.MCN.Value
                    
            If AddConsultant.GYNAE.Value = True Then
                Lists.Cells(i, 7).Value = "Yes"
            Else
                Lists.Cells(i, 7).Value = "No"
            End If
        
            If AddConsultant.SARCOMA.Value = True Then
                Lists.Cells(i, 8).Value = "Yes"
            Else
                Lists.Cells(i, 8).Value = "No"
            End If
            
            If AddConsultant.MELANOMA.Value = True Then
                Lists.Cells(i, 9).Value = "Yes"
            Else
                Lists.Cells(i, 9).Value = "No"
            End If
            
            If AddConsultant.LYMPHOMA.Value = True Then
                Lists.Cells(i, 10).Value = "Yes"
            Else
                Lists.Cells(i, 10).Value = "No"
            End If
            
            If AddConsultant.MYELOMA.Value = True Then
                Lists.Cells(i, 11).Value = "Yes"
            Else
                Lists.Cells(i, 11).Value = "No"
            End If
            
            If AddConsultant.BREAST.Value = True Then
                Lists.Cells(i, 12).Value = "Yes"
            Else
                Lists.Cells(i, 12).Value = "No"
            End If
            
            If AddConsultant.THORACIC.Value = True Then
                Lists.Cells(i, 13).Value = "Yes"
            Else
                Lists.Cells(i, 13).Value = "No"
            End If
            
            If AddConsultant.NEURO.Value = True Then
                Lists.Cells(i, 14).Value = "Yes"
            Else
                Lists.Cells(i, 14).Value = "No"
            End If
            
            If AddConsultant.THYROID.Value = True Then
                Lists.Cells(i, 15).Value = "Yes"
            Else
                Lists.Cells(i, 15).Value = "No"
            End If
            
            If AddConsultant.HEADNECK.Value = True Then
                Lists.Cells(i, 16).Value = "Yes"
            Else
                Lists.Cells(i, 16).Value = "No"
            End If
            
            If AddConsultant.PROSTATE.Value = True Then
                Lists.Cells(i, 17).Value = "Yes"
            Else
                Lists.Cells(i, 17).Value = "No"
            End If
            
            If AddConsultant.COLORECTAL.Value = True Then
                Lists.Cells(i, 18).Value = "Yes"
            Else
                Lists.Cells(i, 18).Value = "No"
            End If
                        
            Sort_Table
    
[B]            For Each Xbox In AddConsultant.Controls
                        
                If TypeOf Xbox Is MSForms.CheckBox Then Xbox.Value = False
                        
            Next[/B]
            
            Consultant.Value = ""
            Specialty.Value = ""
            Hospital.Value = ""
            MCN.Value = ""
            
            ConsultantList.Clear
            ConsultantList.List = Range("allconsultants").Value
                                
            Exit Sub
        End If
    Next i
End Sub

I think I'm remembering an issue with the bold text above, which sets all checkboxes to false - but I'm not certain.

 

[Fluff]

What is the code for Sort_Table?
If it has something like SortFields.Add2 get rid of the 2 so that it's just SortFields.Add

 

[daithiboy]

What is the code for Sort_Table?
If it has something like SortFields.Add2 get rid of the 2 so that it's just SortFields.Add

Hi Fluff,

That is just a one liner to sort the range by specialty

Lists.Columns("C:R").Sort key1:=Range("D2"), order1:=xlAscending, Header:=xlYes

Does anything look off there?

 

[Fluff]

That should be fine.
I can't see anything obvious in your code, that would cause an error. So without knowing the error & which line is generating it, there's not much more I can do.

 

[daithiboy]

That should be fine.
I can't see anything obvious in your code, that would cause an error. So without knowing the error & which line is generating it, there's not much more I can do.

Turns out it was a compile error. I got someone to run it at work.

Bear in mind that this works perfectly on my laptop while accessing the same network drive file.

 

[RoryA]

Check your references. One (or more) of them is not backwards compatible.