I'm guessing but if you have 'track changes' on, that would presumably slow
a macro down. Perhaps you should unshare the workbook before running a
macro if possible.

--
Jim
"puba" wrote in message
ups.com...
| Hi,
| I have a very simple two sheet Excel file with two macros to act as a
| basic helpdesk database. They work fine and fast - but when I share the
| workbook, the macros become terribly slow. I don't know why sharing the
| workbook slow down so much the macros.
|
| As I don't know VBA, most of the code is "recorded".
| I've heard that the "select" command is slowing down execution of the
| code.
|
| Perhaps, someone could simply tell me by what to replace the recorded
| select code by a smarter alternative.
|
| The NEWLINE macro copies the last existing row, then paste below the
| formatting and the validation (drop down lists). Each line in numbered,
| so row 30 will be referenced as "30" in column A. Then, the current
| time and date is inserted, as well as the "no" marker in the "closed"
| column to indicate the call is open.
|
| The SHOWOPEN macro filters on calls that are marked as "not closed",
| using autofilter.
|
| The second sheet is a list of names or items for the drop down lists.
|
| Here are the headers for the 'database' sheet.
|
| Ref Date Time Name Incident type Ware Details Description Helpdesk
| 1 Escalation Helpdesk 2 Solution Closed Date closed Duration Same day
|
|
| Thanks.
| Jim
|
| Here are the macros.
|
| --------------------------------
|
|
| Sub newline()
| '
| ' newline Macro
| ' New incident line
| '
| ' Keyboard Shortcut: Ctrl+Shift+N
|
| 'Select database sheet
| Sheets("database").Select
|
| 'Remove auto-filter, if any. (the SHOWOPEN macro will use autofilter to
| display open calls)
| Range("A1:P1").Select
| ActiveSheet.AutoFilterMode = False
|
| ' Sort by incident number
|
| Columns("A:P").Select
| Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
| Key2:=Range("B2") _
| , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
| Header:= _
| xlGuess, OrderCustom:=1, MatchCase:=False,
| Orientation:=xlTopToBottom, _
| DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
| DataOption3:= _
| xlSortNormal
|
| ' Select column A
| Range("A1").Select
|
| ' count number of lines
| n = Selection.CurrentRegion.Rows.Count
| Rows(n).Copy
|
| ' Go to last line and copy it
| Rows(n).Select
| Selection.Copy
|
| ' Go to new line below and paste the validation (time, names...)
| Rows(n + 1).Select
|
| Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
| _
| SkipBlanks:=False, Transpose:=False
|
| 'and paste formats
| Selection.PasteSpecial Paste:=xlPasteFormats,
| Operation:=xlNone, _
| SkipBlanks:=False, Transpose:=False
|
| ' Send ESC key to deselect row
| SendKeys ("{ESCAPE}")
|
|
| 'Increment incident number and type it as the reference number
| ActiveCell.Value = n + 1
|
|
| 'Move to the right to Date column
| ActiveCell.Offset(0, 1).Select
|
| 'insert today's date
| ActiveCell.Value = Date
|
| 'move to the time cell
| ActiveCell.Offset(0, 1).Select
|
| 'insert time
| ActiveCell.Value = Now
|
|
| 'move to the "closed" column and enter "No" by default (call is
| therefore 'open' by default)
| ActiveCell.Offset(0, 10).Select
| ActiveCell.FormulaR1C1 = "No"
|
| 'then return to Name cell
| ActiveCell.Offset(0, -9).Select
|
|
|
| '
| End Sub
|
| Sub SHOWOPEN()
| '
| ' SHOWOPEN Macro
| ' Macro recorded 24/08/2006 by PE
| 'Ctrl Shift I
|
| '
| 'force auto-filter
| Range("A1:P1").Select
| Selection.AutoFilter
|
| ' filter for NO in closed column
|
| Worksheets("database").Range("A1").AutoFilter _
| field:=13, _
| Criteria1:="no"
|
| 'Sort by date (descending) and time, then by status (closed/open)
|
| Columns("A:P").Select
| Range("P1").Activate
| Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
| Key2:=Range("C2") _
| , Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlAscending,
| Header _
| :=xlGuess, OrderCustom:=1, MatchCase:=False,
| Orientation:=xlTopToBottom _
| , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
| DataOption3:= _
| xlSortNormal
|
| ' scroll to top of the screen
|
| Range("A1").Select
| End Sub
|