Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Time to save a workbook
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Time to save a workbook

Hi,

I have a shared workbook with several sheets that I use to scan in
badges from exhibitors (3 operators). After scanning i'm checking if
the number isn't already used.
If the check up is done, i save the workbook because then I can see the
changes made in the other sheets by other operators.
I never have had problems with saving the workbook as I went quickly.
But now I have to wait more then 6 seconds to scan in another badge.
I'm using at this moment Excel 2003 SP2 while previous I used Excel
2000.
Please, can somebody help me??
I have to use this program on saterday and I didn't found any answer
yet.

Thanks!
Serge


Code of 1 worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim scannummer As Variant
Dim teller As Variant
Dim timestamp As Date
Dim myrange As Range
Application.MoveAfterReturn = False

Set myrange = Intersect(Target, Range("E:E"))
If Not myrange Is Nothing Then
Sheets("scannummers").Visible = True
Sheets("details").Visible = True
Sheets("vooraan scan in").Select
scannummer = Range("E6")
If scannummer = "" Then
End If
If scannummer < "" Then
Sheets("scannummers").Select
Sheets("Scannummers").Range("a:a").Cells.Find(what :=scannummer, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate
teller = ActiveCell.Offset(0, 1).Formula
teller = teller + 1
ActiveCell.Offset(0, 1).Formula = teller
If teller = 2 Then
teller = 1
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan scan in").Activate
Cells.Select
With Selection.Interior
..ColorIndex = 3
..Pattern = xlSolid
End With
Beep
Application.Wait Now + TimeValue("00:00:01")
Beep

MsgBox "Tweede maal binnengekomen", vbOKOnly
Cells.Select
With Selection.Interior
..ColorIndex = 8
..Pattern = xlSolid
End With
Range("D4:F5").Select
With Selection.Interior
..ColorIndex = 2
..Pattern = xlSolid
End With
Range("d7:f9").Select
Selection.Interior.ColorIndex = 2
Range("D5:D6").Select
Selection.Interior.ColorIndex = 2
Range("F5:F6").Select
Selection.Interior.ColorIndex = 2
ElseIf teller = 3 Then
Sheets("vooraan scan in").Activate
MsgBox "Doorverwijzen, 3e keer !", vbOKOnly
ElseIf teller = 4 Then
Sheets("vooraan scan in").Activate
MsgBox teller & "e keer, ONTOELAATBAAR !!!", vbCritical, vbOKOnly
ElseIf teller = -1 Then
teller = 0
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan scan in").Activate
MsgBox "Meer buitengegaan dan binnengekomen", vbOKOnly

End If
Sheets("details").Select
Sheets("details").Range("a65536").End(xlUp).Offset (1, 0).Activate
ActiveCell = scannummer
timestamp = Format(Now(), "d/mm/yyyy hh:mm:ss")
ActiveCell.Offset(0, 1).Formula = timestamp
ActiveCell.Offset(0, 2).Value = "IN"
Sheets("vooraan scan in").Select
Sheets("vooraan scan in").Range("E6").Select
ActiveWorkbook.Save
Sheets("scannummers").Visible = False
Sheets("details").Visible = False


End If

ElseIf Range("G6") < "" Then

Set myrange = Intersect(Target, Range("G:G"))
If Not myrange Is Nothing Then
scannummer = Range("G6")
Sheets("scannummers").Visible = True
Sheets("details").Visible = True
If scannummer = "" Then
End If
If scannummer < "" Then
Sheets("scannummers").Select
Sheets("scannummers").Range("a:a").Cells.Find(what :=scannummer, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate
teller = ActiveCell.Offset(0, 1).Formula
teller = teller - 1
ActiveCell.Offset(0, 1).Formula = teller
If teller = -1 Then
teller = 0
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan").Activate
MsgBox "Meer buitengegaan dan binnengekomen", vbOKOnly

Else
End If
Sheets("details").Select
Sheets("details").Range("a65536").End(xlUp).Offset (1, 0).Activate
ActiveCell = scannummer
timestamp = Format(Now(), "d/mm/yyyy hh:mm:ss")
ActiveCell.Offset(0, 1).Formula = timestamp
ActiveCell.Offset(0, 2).Value = "OUT"
Sheets("vooraan").Select

ActiveWorkbook.Save
Sheets("vooraan").Range("E6").Select
Selection.Interior.ColorIndex = xlNone
Sheets("vooraan").Range("g6").Select
Selection.Interior.ColorIndex = 8
Sheets("scannummers").Visible = False
Sheets("details").Visible = False
End If



End If
End If
End Sub


--
serge
------------------------------------------------------------------------
serge's Profile: http://www.excelforum.com/member.php...fo&userid=4164
View this thread: http://www.excelforum.com/showthread...hreadid=510948

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Time to save a workbook
serge,
Didn't examine your code too closely and can't say about any difference
between XL2000 and XL2003, but a few thing spring to mind to speed thing up.

You can get rid of all the .Selects and .Activates., until you actually want
to bring a cell to the attention of the user.
Also, a minor point, but don't use variants unless you require that ability.

Whilst I assume this works, what is the value of "scannummer" ?
Dim scannummer As Variant 'Dim scannummer As String
scannummer = Range("E6") 'scannummer = Range("E6").Value
because later you test scannummer < ""

Also with:
Dim teller As Variant Dim teller As Long
teller = teller + 1

And, do you need to work with every cell on the WS ?
e.g. Cells.Select


NickHK

"serge" wrote in
message ...

Hi,

I have a shared workbook with several sheets that I use to scan in
badges from exhibitors (3 operators). After scanning i'm checking if
the number isn't already used.
If the check up is done, i save the workbook because then I can see the
changes made in the other sheets by other operators.
I never have had problems with saving the workbook as I went quickly.
But now I have to wait more then 6 seconds to scan in another badge.
I'm using at this moment Excel 2003 SP2 while previous I used Excel
2000.
Please, can somebody help me??
I have to use this program on saterday and I didn't found any answer
yet.

Thanks!
Serge


Code of 1 worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim scannummer As Variant
Dim teller As Variant
Dim timestamp As Date
Dim myrange As Range
Application.MoveAfterReturn = False

Set myrange = Intersect(Target, Range("E:E"))
If Not myrange Is Nothing Then
Sheets("scannummers").Visible = True
Sheets("details").Visible = True
Sheets("vooraan scan in").Select
scannummer = Range("E6")
If scannummer = "" Then
End If
If scannummer < "" Then
Sheets("scannummers").Select
Sheets("Scannummers").Range("a:a").Cells.Find(what :=scannummer, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate
teller = ActiveCell.Offset(0, 1).Formula
teller = teller + 1
ActiveCell.Offset(0, 1).Formula = teller
If teller = 2 Then
teller = 1
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan scan in").Activate
Cells.Select
With Selection.Interior
ColorIndex = 3
Pattern = xlSolid
End With
Beep
Application.Wait Now + TimeValue("00:00:01")
Beep

MsgBox "Tweede maal binnengekomen", vbOKOnly
Cells.Select
With Selection.Interior
ColorIndex = 8
Pattern = xlSolid
End With
Range("D4:F5").Select
With Selection.Interior
ColorIndex = 2
Pattern = xlSolid
End With
Range("d7:f9").Select
Selection.Interior.ColorIndex = 2
Range("D5:D6").Select
Selection.Interior.ColorIndex = 2
Range("F5:F6").Select
Selection.Interior.ColorIndex = 2
ElseIf teller = 3 Then
Sheets("vooraan scan in").Activate
MsgBox "Doorverwijzen, 3e keer !", vbOKOnly
ElseIf teller = 4 Then
Sheets("vooraan scan in").Activate
MsgBox teller & "e keer, ONTOELAATBAAR !!!", vbCritical, vbOKOnly
ElseIf teller = -1 Then
teller = 0
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan scan in").Activate
MsgBox "Meer buitengegaan dan binnengekomen", vbOKOnly

End If
Sheets("details").Select
Sheets("details").Range("a65536").End(xlUp).Offset (1, 0).Activate
ActiveCell = scannummer
timestamp = Format(Now(), "d/mm/yyyy hh:mm:ss")
ActiveCell.Offset(0, 1).Formula = timestamp
ActiveCell.Offset(0, 2).Value = "IN"
Sheets("vooraan scan in").Select
Sheets("vooraan scan in").Range("E6").Select
ActiveWorkbook.Save
Sheets("scannummers").Visible = False
Sheets("details").Visible = False


End If

ElseIf Range("G6") < "" Then

Set myrange = Intersect(Target, Range("G:G"))
If Not myrange Is Nothing Then
scannummer = Range("G6")
Sheets("scannummers").Visible = True
Sheets("details").Visible = True
If scannummer = "" Then
End If
If scannummer < "" Then
Sheets("scannummers").Select
Sheets("scannummers").Range("a:a").Cells.Find(what :=scannummer, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate
teller = ActiveCell.Offset(0, 1).Formula
teller = teller - 1
ActiveCell.Offset(0, 1).Formula = teller
If teller = -1 Then
teller = 0
ActiveCell.Offset(0, 1).Formula = teller
Sheets("vooraan").Activate
MsgBox "Meer buitengegaan dan binnengekomen", vbOKOnly

Else
End If
Sheets("details").Select
Sheets("details").Range("a65536").End(xlUp).Offset (1, 0).Activate
ActiveCell = scannummer
timestamp = Format(Now(), "d/mm/yyyy hh:mm:ss")
ActiveCell.Offset(0, 1).Formula = timestamp
ActiveCell.Offset(0, 2).Value = "OUT"
Sheets("vooraan").Select

ActiveWorkbook.Save
Sheets("vooraan").Range("E6").Select
Selection.Interior.ColorIndex = xlNone
Sheets("vooraan").Range("g6").Select
Selection.Interior.ColorIndex = 8
Sheets("scannummers").Visible = False
Sheets("details").Visible = False
End If



End If
End If
End Sub


--
serge
------------------------------------------------------------------------
serge's Profile:
http://www.excelforum.com/member.php...fo&userid=4164
View this thread: http://www.excelforum.com/showthread...hreadid=510948



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Time to save a workbook

Hi Nick,

I' ve changed a few things but the problem stays the same. The progra
works perfect if I drop the line activeworkbook.save
I never have had the problem before and i didn't change anything on th
program before.

Serg

--
serg
-----------------------------------------------------------------------
serge's Profile: http://www.excelforum.com/member.php...nfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=51094

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Save Workbook as Current Date/Time Jacob_F_Roecker Excel Discussion (Misc queries) 1 October 25th 05 09:37 AM
adding before save so it works every time excel opens and with any workbook Job[_2_] Excel Programming 3 January 12th 05 10:13 PM
auto save on exit while at the same time rename workbook... Brian Excel Programming 2 October 5th 04 01:33 AM
filling a combobox (without being prompted to save the workbook EVERY time its opened neowok[_34_] Excel Programming 2 March 1st 04 03:39 PM
What commands do you use to name a workbook, save a workbook,open a workbook Steven R. Berke Excel Programming 1 July 24th 03 11:37 PM


All times are GMT +1. The time now is 04:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement