Anyone? Making an 2003 Excel Spreadsheet work in 2010 with Macros.

S

smi018484

New Member
Joined
Apr 18, 2014
Messages
4
Hello all and thank you for letting me join the forum!

I am a new computer tech at a high school and I inherited an excel spreadsheet that is used for a student library check in system.

What it does is when students come into the library they scan their ID badge and then it searches spreadsheet 2 which has all the students listed and then puts the info needed on sheet 1. They use it to keep up with what students check in the library, but it also tells them if they have that hour off.

My problem is it was running on an old XP machine and Office 2003. So nobody ever touched it cause the lady wouldn't let them. Well, now she is gone and I must move it to a Windows 7 machine running Office 2010.

I got it all moved to the new machine and got it working only in Compatibility mode, but now when I try to save it at the end of the day it gives me an error about MS Excel - Compatibility Checker Some formulas in this workbook are linked to other workbooks that are closed. Location Defined names.

I can click continue and it acts like it saves but when I open the file back up nothing new is there. It tells me to click cancel and save the file in one of the new formats. I have tried saving in the new formats, but when using the file with students it will just freeze and crash!

So, long story short, I have an Excel file made in 97 or 2003 and I must make it work with 2010. I am great with hardware and software installs but don't know much about Excel and workbooks and macros and my head hurts!

Excel Error: https://dl.dropboxusercontent.com/u/25115647/Excel Error.PNG

Macro in NotePad https://dl.dropboxusercontent.com/u/25115647/MACRO.txt

I can try and post the excel file if needed, but I will have to delete the kids data first.

If no one helps I understand, but this is really making me feel dumb! Apparently there is a lot about Excel that I have no clue about!

Well, I am calling it an end to a long week and hope someone can shed any light on my problem!

If you need anything that might help, please let me know!!
 
Sort by date Sort by votes
i just demonstrated these bits as i found them so, nothing was cumulative

combined here though
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'B will update if A is edited
    Dim today
    With Application
        .ScreenUpdating = False
      .EnableEvents = False
    End With

    On Error GoTo enditall
    ' today = Format(Now(), "Day") 'took this out as I can't see the reason for it
    If Target.Cells.Column = 1 Then
        n = Target.Row
        If Sheets("sheet1").Range("A" & n).Value <> "" Then
            Sheets("sheet1").Range("B" & n).Value = Format(Now(), "hh:mm AMPM")
        End If
        If Sheets("sheet1").Range("A" & n).Value <> "" Then
            Sheets("sheet1").Range("C" & n).Value = Format(Now(), "mm/dd/yyyy")
        End If
    End If

    If Target.Cells.Column = 1 Then

        If Format(Now(), "dddd") = "Monday" Then Monday

        If Format(Now(), "dddd") = "Tuesday" Then Tuesday

        If Format(Now(), "dddd") = "Wednesday" Then Wednesday

        If Format(Now(), "dddd") = "Thursday" Then Thursday

        If Format(Now(), "dddd") = "Friday" Then Friday

    End If


    'Compare A1-Sheet1 to A1 Sheet2 if match then put name and grade
    Check = True: Counter = 0
    Do        'Outer loop.
        Do        'Inner Loop.

            Counter = Counter + 1
            If Sheets("sheet2").Range("A" & Counter) = "EOF" Then
                Check = False        ' Set value of flag to False. ' v long count in 2007 onwards 1,000,000 plus rows
                Exit Do        ' Exit inner loop.
            End If
            If Sheets("sheet1").Range("A" & n).Value = Sheets("sheet2").Range("A" & Counter) Then
                Sheets("sheet1").Range("D" & n).Value = Sheets("sheet2").Range("B" & Counter)
                Sheets("sheet1").Range("E" & n).Value = Sheets("sheet2").Range("C" & Counter)
                Sheets("sheet1").Range("F" & n).Value = Sheets("sheet2").Range("D" & Counter)
            End If

        Loop While Check <> False
    Loop Until Check = False

    With Application
        .ScreenUpdating = True
       .EnableEvents = True
    End With
enditall:

End Sub
Sub Monday()
    If Time > TimeSerial(14, 8, 0) Then
        If Time < TimeSerial(15, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 8"
        End If
    End If
    If Time > TimeSerial(13, 10, 0) Then
        If Time < TimeSerial(14, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 7"
        End If
    End If
    If Time > TimeSerial(12, 12, 0) Then
        If Time < TimeSerial(13, 4, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 6"
        End If
    End If
    If Time > TimeSerial(11, 14, 0) Then
        If Time < TimeSerial(12, 6, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 5"
        End If
    End If
    If Time > TimeSerial(10, 16, 0) Then
        If Time < TimeSerial(11, 8, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 4"
        End If
    End If
    If Time > TimeSerial(9, 8, 0) Then
        If Time < TimeSerial(10, 10, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 3"
        End If
    End If
    If Time > TimeSerial(8, 8, 0) Then
        If Time < TimeSerial(9, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 2"
        End If
    End If
    If Time > TimeSerial(7, 10, 0) Then
        If Time < TimeSerial(8, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 1"
        End If
    End If
    If Time > TimeSerial(6, 0, 0) Then
        If Time < TimeSerial(7, 9, 59) Then
            Sheets("sheet1").Range("G" & n).Value = "Before School"
        End If
    End If
    If Time > TimeSerial(15, 0, 1) Then
        If Time < TimeSerial(17, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "After School"
        End If
    End If        'Monday Period

End Sub
Sub Tuesday()
    If Time > TimeSerial(14, 8, 0) Then
        If Time < TimeSerial(15, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 8"
        End If
    End If
    If Time > TimeSerial(13, 10, 0) Then
        If Time < TimeSerial(14, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 7"
        End If
    End If
    If Time > TimeSerial(12, 12, 0) Then
        If Time < TimeSerial(13, 4, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 6"
        End If
    End If
    If Time > TimeSerial(11, 14, 0) Then
        If Time < TimeSerial(12, 6, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 5"
        End If
    End If
    If Time > TimeSerial(10, 16, 0) Then
        If Time < TimeSerial(11, 8, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 4"
        End If
    End If
    If Time > TimeSerial(9, 8, 0) Then
        If Time < TimeSerial(10, 10, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 3"
        End If
    End If
    If Time > TimeSerial(8, 8, 0) Then
        If Time < TimeSerial(9, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 2"
        End If
    End If
    If Time > TimeSerial(7, 10, 0) Then
        If Time < TimeSerial(8, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 1"
        End If
    End If
    If Time > TimeSerial(6, 0, 0) Then
        If Time < TimeSerial(7, 9, 59) Then
            Sheets("sheet1").Range("G" & n).Value = "Before School"
        End If
    End If
    If Time > TimeSerial(15, 0, 1) Then
        If Time < TimeSerial(17, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "After School"
        End If
    End If
    'Tuesday Period

End Sub
Sub Wednesday()
    If Time > TimeSerial(11, 2, 0) Then
        If Time < TimeSerial(12, 30, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 8"
        End If
    End If
    If Time > TimeSerial(9, 31, 0) Then
        If Time < TimeSerial(10, 56, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 3"
        End If
    End If
    If Time > TimeSerial(8, 0, 0) Then
        If Time < TimeSerial(9, 25, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 1"
        End If
    End If
    If Time > TimeSerial(7, 10, 0) Then
        If Time < TimeSerial(7, 55, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "ACCESS Time"
        End If
    End If
    'commented out due to schedule change
    'If Time > WPer6S Then
    '   If Time < WPer6E Then
    '    sheets("sheet1").range("G" & N).Value = "Period 6"
    ' End If
    'End If
    'If Time > WPer4S Then
    '   If Time < WPer4E Then
    '    sheets("sheet1").range("G" & N).Value = "Period 4"
    ' End If
    'End If
    'If Time > TimeSerial(8, 0, 0) Then
    '   If Time < TimeSerial(9, 25, 0) Then
    '    sheets("sheet1").range("G" & N).Value = "Period 2"
    ' End If
    'End If
    If Time > TimeSerial(6, 0, 0) Then
        If Time < TimeSerial(7, 9, 59) Then
            Sheets("sheet1").Range("G" & n).Value = "Before School"
        End If
    End If
    If Time > TimeSerial(12, 30, 1) Then
        If Time < TimeSerial(17, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "After School"
        End If
    End If
    'Wednesday Period

End Sub

Sub Thursday()
    If Time > TimeSerial(13, 18, 0) Then
        If Time < TimeSerial(15, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 8"
        End If
    End If
    If Time > TimeSerial(11, 47, 0) Then
        If Time < TimeSerial(13, 12, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 6"
        End If
    End If
    If Time > TimeSerial(10, 16, 0) Then
        If Time < TimeSerial(11, 41, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 5"
        End If
    End If
    If Time > TimeSerial(8, 45, 0) Then
        If Time < TimeSerial(10, 10, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 4"
        End If
    End If
    If Time > TimeSerial(7, 10, 0) Then
        If Time < TimeSerial(8, 39, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 1"
        End If
    End If
    'commented out due to change in schedule
    'If Time > TPer7S Then
    '   If Time < TPer7E Then
    '    sheets("sheet1").range("G" & N).Value = "Period 7"
    ' End If
    'End If
    'If Time > TimeSerial(10, 16, 0) Then
    '   If Time < TimeSerial(11, 41, 0) Then
    '    sheets("sheet1").range("G" & N).Value = "Period 5"
    ' End If
    'End If
    'If Time > TPer3S Then
    '   If Time < TPer3E Then
    '    sheets("sheet1").range("G" & N).Value = "Period 3"
    ' End If
    'End If
    'If Time > TimeSerial(7, 10, 0) Then
    '   If Time < TimeSerial(8, 39, 0) Then
    '    sheets("sheet1").range("G" & N).Value = "Period 1"
    ' End If
    'End If
    If Time > TimeSerial(6, 0, 0) Then
        If Time < TimeSerial(7, 9, 59) Then
            Sheets("sheet1").Range("G" & n).Value = "Before School"
        End If
    End If
    If Time > TimeSerial(15, 0, 1) Then
        If Time < TimeSerial(17, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "After School"
        End If
    End If
    'Thursday Period

End Sub
Sub Friday()
    If Time > TimeSerial(14, 8, 0) Then
        If Time < TimeSerial(15, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 8"
        End If
    End If
    If Time > TimeSerial(13, 10, 0) Then
        If Time < TimeSerial(14, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 7"
        End If
    End If
    If Time > TimeSerial(12, 12, 0) Then
        If Time < TimeSerial(13, 4, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 6"
        End If
    End If
    If Time > TimeSerial(11, 14, 0) Then
        If Time < TimeSerial(12, 6, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 5"
        End If
    End If
    If Time > TimeSerial(10, 16, 0) Then
        If Time < TimeSerial(11, 8, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 4"
        End If
    End If
    If Time > TimeSerial(9, 8, 0) Then
        If Time < TimeSerial(10, 10, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 3"
        End If
    End If
    If Time > TimeSerial(8, 8, 0) Then
        If Time < TimeSerial(9, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 2"
        End If
    End If
    If Time > TimeSerial(7, 10, 0) Then
        If Time < TimeSerial(8, 2, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "Period 1"
        End If
    End If
    If Time > TimeSerial(6, 0, 0) Then
        If Time < TimeSerial(7, 9, 59) Then
            Sheets("sheet1").Range("G" & n).Value = "Before School"
        End If
    End If
    If Time > TimeSerial(15, 0, 1) Then
        If Time < TimeSerial(17, 0, 0) Then
            Sheets("sheet1").Range("G" & n).Value = "After School"
        End If
    End If
    'Friday Period

End Sub
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
ASAP Utilities and Navigator Utilities might reveal linked books (not something I use regularly so not sure what you might find)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Memory resource issue when opening and closing excel files
Replies
0
Views
275
Mike UK
M
A
  • Question Question
Purchase Order macros
Replies
5
Views
223
AndyB86
A
N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
279
nevyoung
N
D
  • Solved
repopulating data in a spreadsheet, without having to fix formulas
Replies
3
Views
74
Alex Blakenburg
A
amadams
  • Question Question
Macro to AutoSave Excel Spreadsheets Using Different File Names
Replies
1
Views
434
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top