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!!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
smi018484 said:
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!!
Click to expand...

You are probably going to need real time help from a local source to straighten that mess out. It sounds as though there might be other files involved. You need to find somebody locally who works with VBA to take a look at the macros and see if there are links to other files. Your description of the barcoding and what might be recorded leads me to believe that it is not a simple macro that you are dealing with. If it were, you should be able to just save the file with the appropriate fileformat parameter and then update the macro to 2010 syntax, etc. But that ain't happening, so like I said, find a warm body locally that knows that stuff.

Or as an alternative, you can start from scratch and develop a new program to do the job.
 
Upvote 0
Thanks for the reply! Yeah I am looking for some now! Seeing what or if the district has anyone. Sad part is no one seems to know were the original one came from in the first place! Just figured I would give it a shot to people who might understand what is going on in the bigger picture!

Thanks again!
 
Upvote 0
it seems a bit fickle, but I can't see why it won't work under 2010, though it is very slow running through the posted code

Code: 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'B will update if A is edited
Dim MTFPer1S
MTFPer1S = TimeSerial(7, 10, 0)
Dim MTFPer1E
MTFPer1E = TimeSerial(8, 2, 0)
Dim MTFPer2S
MTFPer2S = TimeSerial(8, 8, 0)
Dim MTFPer2E
MTFPer2E = TimeSerial(9, 2, 0)
Dim MTFPer3S
MTFPer3S = TimeSerial(9, 8, 0)
Dim MTFPer3E
MTFPer3E = TimeSerial(10, 10, 0)
Dim MTFPer4S
MTFPer4S = TimeSerial(10, 16, 0)
Dim MTFPer4E
MTFPer4E = TimeSerial(11, 8, 0)
Dim MTFPer5S
MTFPer5S = TimeSerial(11, 14, 0)
Dim MTFPer5E
MTFPer5E = TimeSerial(12, 6, 0)
Dim MTFPer6S
MTFPer6S = TimeSerial(12, 12, 0)
Dim MTFPer6E
MTFPer6E = TimeSerial(13, 4, 0)
Dim MTFPer7S
MTFPer7S = TimeSerial(13, 10, 0)
Dim MTFPer7E
MTFPer7E = TimeSerial(14, 2, 0)
Dim MTFPer8S
MTFPer8S = TimeSerial(14, 8, 0)
Dim MTFPer8E
MTFPer8E = TimeSerial(15, 0, 0)
Dim MTFEarlyS
MTFEarlyS = TimeSerial(6, 0, 0)
Dim MTFEarlyE
MTFEarlyE = TimeSerial(7, 9, 59)
Dim MTFLateS
MTFLateS = TimeSerial(15, 0, 1)
Dim MTFLateE
MTFLateE = TimeSerial(17, 0, 0)

Dim WPer2S
WPer2S = TimeSerial(8, 0, 0)
Dim WPer2E
WPer2E = TimeSerial(9, 25, 0)
Dim WPer3S
WPer3S = TimeSerial(9, 31, 0)
Dim WPer3E
WPer3E = TimeSerial(10, 56, 0)
Dim WPer7S
WPer7S = TimeSerial(11, 2, 0)
Dim WPer7E
WPer7E = TimeSerial(12, 30, 0)
Dim WAccS
WAccS = TimeSerial(7, 10, 0)
Dim WAccE
WAccE = TimeSerial(7, 55, 0)
Dim WEarlyS
WEarlyS = TimeSerial(6, 0, 0)
Dim WEarlyE
WEarlyE = TimeSerial(7, 9, 59)
Dim WLateS
WLateS = TimeSerial(12, 30, 1)
Dim WLateE
WLateE = TimeSerial(17, 0, 0)


Dim TPer1S
TPer1S = TimeSerial(7, 10, 0)
Dim TPer1E
TPer1E = TimeSerial(8, 39, 0)
Dim TPer4S
TPer4S = TimeSerial(8, 45, 0)
Dim TPer4E
TPer4E = TimeSerial(10, 10, 0)
Dim TPer5S
TPer5S = TimeSerial(10, 16, 0)
Dim TPer5E
TPer5E = TimeSerial(11, 41, 0)
Dim TPer6S
TPer6S = TimeSerial(11, 47, 0)
Dim TPer6E
TPer6E = TimeSerial(13, 12, 0)
Dim TPer8S
TPer8S = TimeSerial(13, 18, 0)
Dim TPer8E
TPer8E = TimeSerial(15, 0, 0)
Dim TEarlyS
TEarlyS = TimeSerial(6, 0, 0)
Dim TEarlyE
TEarlyE = TimeSerial(7, 9, 59)
Dim TLateS
TLateS = TimeSerial(15, 0, 1)
Dim TLateE
TLateE = TimeSerial(17, 0, 0)

On Error GoTo enditall
today = Format(Now(), "Day")
If Target.Cells.Column = 1 Then
       N = Target.Row
       If Excel.Range("Sheet1!A" & N).Value <> "" Then
         Excel.Range("Sheet1!B" & N).Value = Format(Now(), "hh:mm AMPM")
       End If
       If Excel.Range("Sheet1!A" & N).Value <> "" Then
         Excel.Range("Sheet1!C" & N).Value = Format(Now(), "mm/dd/yyyy")
       End If
End If

If Target.Cells.Column = 1 Then

If Format(Now(), "dddd") = "Monday" Then
        If Time > MTFPer8S Then
            If Time < MTFPer8E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 8"
            End If
        End If
        If Time > MTFPer7S Then
            If Time < MTFPer7E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 7"
            End If
        End If
        If Time > MTFPer6S Then
            If Time < MTFPer6E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 6"
            End If
        End If
        If Time > MTFPer5S Then
            If Time < MTFPer5E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 5"
            End If
        End If
        If Time > MTFPer4S Then
            If Time < MTFPer4E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 4"
            End If
        End If
        If Time > MTFPer3S Then
            If Time < MTFPer3E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 3"
            End If
        End If
        If Time > MTFPer2S Then
            If Time < MTFPer2E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 2"
            End If
        End If
        If Time > MTFPer1S Then
            If Time < MTFPer1E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 1"
            End If
        End If
        If Time > MTFEarlyS Then
            If Time < MTFEarlyE Then
              Excel.Range("Sheet1!G" & N).Value = "Before School"
            End If
        End If
        If Time > MTFLateS Then
            If Time < MTFLateE Then
              Excel.Range("Sheet1!G" & N).Value = "After School"
            End If
        End If
End If 'Monday Period

If Format(Now(), "dddd") = "Tuesday" Then
        If Time > MTFPer8S Then
            If Time < MTFPer8E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 8"
            End If
        End If
        If Time > MTFPer7S Then
            If Time < MTFPer7E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 7"
            End If
        End If
        If Time > MTFPer6S Then
            If Time < MTFPer6E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 6"
            End If
        End If
        If Time > MTFPer5S Then
            If Time < MTFPer5E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 5"
            End If
        End If
        If Time > MTFPer4S Then
            If Time < MTFPer4E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 4"
            End If
        End If
        If Time > MTFPer3S Then
            If Time < MTFPer3E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 3"
            End If
        End If
        If Time > MTFPer2S Then
            If Time < MTFPer2E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 2"
            End If
        End If
        If Time > MTFPer1S Then
            If Time < MTFPer1E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 1"
            End If
        End If
        If Time > MTFEarlyS Then
            If Time < MTFEarlyE Then
              Excel.Range("Sheet1!G" & N).Value = "Before School"
            End If
        End If
        If Time > MTFLateS Then
            If Time < MTFLateE Then
              Excel.Range("Sheet1!G" & N).Value = "After School"
            End If
        End If
End If 'Tuesday Period


If Format(Now(), "dddd") = "Wednesday" Then
        If Time > WPer7S Then
            If Time < WPer7E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 8"
            End If
        End If
        If Time > WPer3S Then
            If Time < WPer3E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 3"
            End If
        End If
        If Time > WPer2S Then
            If Time < WPer2E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 1"
            End If
        End If
        If Time > WAccS Then
            If Time < WAccE Then
              Excel.Range("Sheet1!G" & N).Value = "ACCESS Time"
            End If
        End If
        'commented out due to schedule change
        'If Time > WPer6S Then
         '   If Time < WPer6E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 6"
           ' End If
        'End If
        'If Time > WPer4S Then
         '   If Time < WPer4E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 4"
           ' End If
        'End If
        'If Time > WPer2S Then
         '   If Time < WPer2E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 2"
           ' End If
        'End If
        If Time > WEarlyS Then
            If Time < WEarlyE Then
              Excel.Range("Sheet1!G" & N).Value = "Before School"
            End If
        End If
        If Time > WLateS Then
            If Time < WLateE Then
              Excel.Range("Sheet1!G" & N).Value = "After School"
            End If
        End If
    End If 'Wednesday Period



If Format(Now(), "dddd") = "Thursday" Then
        If Time > TPer8S Then
            If Time < TPer8E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 8"
            End If
        End If
        If Time > TPer6S Then
            If Time < TPer6E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 6"
            End If
        End If
        If Time > TPer5S Then
            If Time < TPer5E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 5"
            End If
        End If
        If Time > TPer4S Then
            If Time < TPer4E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 4"
            End If
        End If
        If Time > TPer1S Then
            If Time < TPer1E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 1"
            End If
        End If
        'commented out due to change in schedule
        'If Time > TPer7S Then
         '   If Time < TPer7E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 7"
           ' End If
        'End If
        'If Time > TPer5S Then
         '   If Time < TPer5E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 5"
           ' End If
        'End If
        'If Time > TPer3S Then
         '   If Time < TPer3E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 3"
           ' End If
        'End If
        'If Time > TPer1S Then
         '   If Time < TPer1E Then
          '    Excel.Range("Sheet1!G" & N).Value = "Period 1"
           ' End If
        End If
        If Time > TEarlyS Then
            If Time < TEarlyE Then
              Excel.Range("Sheet1!G" & N).Value = "Before School"
            End If
        End If
        If Time > TLateS Then
            If Time < TLateE Then
              Excel.Range("Sheet1!G" & N).Value = "After School"
            End If
        End If
End If 'Thursday Period


        
If Format(Now(), "dddd") = "Friday" Then
        If Time > MTFPer8S Then
            If Time < MTFPer8E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 8"
            End If
        End If
        If Time > MTFPer7S Then
            If Time < MTFPer7E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 7"
            End If
        End If
        If Time > MTFPer6S Then
            If Time < MTFPer6E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 6"
            End If
        End If
        If Time > MTFPer5S Then
            If Time < MTFPer5E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 5"
            End If
        End If
        If Time > MTFPer4S Then
            If Time < MTFPer4E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 4"
            End If
        End If
        If Time > MTFPer3S Then
            If Time < MTFPer3E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 3"
            End If
        End If
        If Time > MTFPer2S Then
            If Time < MTFPer2E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 2"
            End If
        End If
        If Time > MTFPer1S Then
            If Time < MTFPer1E Then
              Excel.Range("Sheet1!G" & N).Value = "Period 1"
            End If
        End If
        If Time > MTFEarlyS Then
            If Time < MTFEarlyE Then
              Excel.Range("Sheet1!G" & N).Value = "Before School"
            End If
        End If
        If Time > MTFLateS Then
            If Time < MTFLateE Then
              Excel.Range("Sheet1!G" & N).Value = "After School"
            End If
        End If
End If 'Friday Period

'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 Excel.Range("Sheet2!A" & Counter) = "EOF" Then
           Check = False    ' Set value of flag to False.
            Exit Do    ' Exit inner loop.
        End If
        If Excel.Range("Sheet1!A" & N).Value = Excel.Range("Sheet2!A" & Counter) Then
           Excel.Range("Sheet1!D" & N).Value = Excel.Range("Sheet2!B" & Counter)
        End If
        If Excel.Range("Sheet1!A" & N).Value = Excel.Range("Sheet2!A" & Counter) Then
           Excel.Range("Sheet1!E" & N).Value = Excel.Range("Sheet2!C" & Counter)
        End If
        If Excel.Range("Sheet1!A" & N).Value = Excel.Range("Sheet2!A" & Counter) Then
           Excel.Range("Sheet1!F" & N).Value = Excel.Range("Sheet2!D" & Counter)
        End If
        Loop While Check <> False
    Loop Until Check = False


enditall:

End Sub
 
Upvote 0
had a play

maybe quicker

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'B will update if A is edited
Dim today
With Application
.ScreenUpdating = False
End With

On Error GoTo enditall
today = Format(Now(), "Day")
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.
            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)
        End If
        If Sheets("sheet1").Range("A" & n).Value = Sheets("sheet2").Range("A" & Counter) Then
           Sheets("sheet1").Range("E" & n).Value = Sheets("sheet2").Range("C" & Counter)
        End If
        If Sheets("sheet1").Range("A" & n).Value = Sheets("sheet2").Range("A" & Counter) Then
           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
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


also save as xlsb, the compatibility is just the fact its xls, in a higher programme

if you use data tab and then open connections you should see the linked books, they maybe fine, or also need updating
 
Last edited:
Upvote 0
Welcome to the Board!

You can reach out the Mr. Excel Consulting Team: consult @ MrExcel.com

HTH,
 
Upvote 0
two more things

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

and


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

at the beginning and end of the macro (as changes are making the events re-trigger)

AND

Counter = Counter + 1
If Sheets("sheet2").Range("A" & Counter) = "EOF" Then

is counting to the end of a sheet which is over a MILLION rows, each time its run

EOF might be added by something else, but as it stands it doesn't exist, which sort of means the loops run a long time, even on eight processors
 
Upvote 0
Thanks Mole999! I will mess with it when I get a chance! Monday's are always crazy! Going to try your new code and see how it acts!

Just asking did you make the changes in your second post all ready or you saying I need to? The counter one makes since cause it can just spin and spin after a student gets scanned in.

Also, thanks Smitty for the info. We may be looking for help soon! Just depends on how bad the school wants to keep the system going. If you never worked for a public school, it is all about cost or at least at mine it is!
 
Last edited:
Upvote 0
Just quickly tried your new code and it defiantly is much faster and hasn't frozen yet! Just need some time to do more testing!

For the "if you use data tab and then open connections you should see the linked books, they maybe fine, or also need updating"

In 2010 when I click the Data tab and connections it has nothing listed in that box. So maybe there are no extra workbooks. Any ideal how to check that in 2003? Time to turn to my friend Google!

Thanks again for giving it a shot and once I can test it further I will let you know how it works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 
Upvote 0
Also, thanks Smitty for the info. We may be looking for help soon! Just depends on how bad the school wants to keep the system going. If you never worked for a public school, it is all about cost or at least at mine it is!
Click to expand...

Oh yeah, I've done a lot of work with schools, and my wife works for a university.
 
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