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

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,415
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 1, 2016
Messages
72
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,434
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do you know the error number? Can you post the code for the button-click event?
 

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72

ADVERTISEMENT

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.

Userform.JPG


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.

Sheet Example.JPG


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,335
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 1, 2016
Messages
72

ADVERTISEMENT

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

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

Does anything look off there?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,335
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 1, 2016
Messages
72
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.

1606835735541.png
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,434
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Check your references. One (or more) of them is not backwards compatible.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

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