Sharing a Workbook that Contains Macros

[DarcyNoonan]

Hi,

I am using Excel 97.

I have a workbook that contains 4 small macros that act as advance filters to create lists based on certain criteria. I would like to share this workbook so that more than 1 person can work on the workbook at the same time.

I know that macros become disabled when you share workbooks but I was wondering if anyone has faced this issue and has overcome it in some way or another.

I have thought of a possible solution but I have no idea if it is even possible to accomplish. My solution is to place the macros and list worksheets in a seperate non-shared workbook. Users would then use the macros to filter out information contained in the shared workbook and display the list in the non-shared workbook.

Once again, I do not know if this is even possible. Can someone tell me if this is a possible solution and if so, how would I go about doing this? If it is not possible, can someone please tell me a way around the sharing/macro issue?

I know I can just assign a password and tell the users that they cannot be using the workbook at the same time but I would like to avoid this.

Thank You.

 

[SkipVought]

Hi,

You CAN have macros in shared workbooks.

HOWEVER, they cannot be viewed or edited wil in the shared mode.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DarcyNoonan]

SkipVought,

I shared the workbook and tried to execute the macro to run the Advance Filter so that I can create a list and I got the following error message:

"Run-time error '1004': AdvanceFilter method of Range class failed".

Any help on what this means exactly and how it can be solved would be greatly appreciated.

Thank You.

 

[SkipVought]

Let's see the code and show me where it failed.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DarcyNoonan]

Here are the 3 macros I am using. When the error message pops up, it won't let me debug (I'm guessing because the workbook is shared) but the macros work fine when the workbook is not being shared.

MACRO #1
Sub additiongenerator()
'
' additiongenerator Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range("additiondata&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A1:H2&quot, CopyToRange:=Range("A14:I1029&quot, Unique:=False
Range("A2&quot.Select
ActiveWindow.LargeScroll Down:=-1
Range("A2&quot.Select
End Sub

MACRO #2
Sub deletionlistgenerator()
'
' deletionlistgenerator Macro
'
' Keyboard Shortcut: Ctrl+l
'
Range("D18&quot.Select
Range("deletiondata&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A1:G2&quot, CopyToRange:=Range("A18:M1033&quot, Unique:=False
End Sub

MACRO #3
Sub summarylist()
'
' summarylist Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("additiondata&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A1:G2&quot, CopyToRange:=Range("A23:i1100&quot, Unique:=False
Range("deletiondata&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A1:G2&quot, CopyToRange:=Range("L23:T1100&quot, Unique:=False
End Sub

Thank You Once Again

 

[DarcyNoonan]

bump

 

[SkipVought]

I could not see anything off hand. :-(

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DarcyNoonan]

Skip,

Tell me if I'm on to something here...

In shared mode, I would like the user to input the criteria in the criteria range (specified in the macro code) and then run the macro. Could it be that you cannot enter new criteria in shared mode?

OR

Could excel have a problem running macros with advancefilter functions while in shared mode?

 

[SkipVought]

Here's what help states about shared workbook restrictions...

The following features can't be changed after a workbook is shared: merged cells, conditional formats, data validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros.

So Criteria Values ought to be able to be changed.

If you care to, I'll look at your workbook if you send it to me -- san any confidential info.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DarcyNoonan]

bump...anyone have anything to add to help me out? any help would be appreciated.

thx