Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting cells in Shared Workbook...
Hi, I've currently got shared workbook that pulls in data (onto the data
sheet) which I then sort two columns a time. The data sheet contains staff in different departments along with a shift identifier. When the workbook isn't shared this code: With dataSheet.Range("A:B") .Sort Key1:=Cells(2, 1), Order1:=xlAscending, Key2:=Cells(2, 2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, DataOption1:=xlSortNormal End With works fine. When the workbook is shared Excel doesn't seem to want to play ball with regard to restricting the sort to the columns I've specified - it sorts the whole sheet. By the time it's gone through the four different departments (A:B, C:D, E:F, G:H) the whole things in a damn mess quite frankly and this ruins the named ranges that I've also setup. Does anyone have any idea how I can enforce this restriction of sorting on columns when the workbook is shared? Thanks George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting cells in Shared Workbook...
Hi George,
Microsoft says in their help files that you cannot create a shared excel workbook if it has code in. That is, the code wont work! There are certain elements (Excel facilities) that are "disabled" when a workbook becomes shared. Your code calls some of this unsharable functionality. Hope this helps. Aran Black "George" wrote: Hi, I've currently got shared workbook that pulls in data (onto the data sheet) which I then sort two columns a time. The data sheet contains staff in different departments along with a shift identifier. When the workbook isn't shared this code: With dataSheet.Range("A:B") .Sort Key1:=Cells(2, 1), Order1:=xlAscending, Key2:=Cells(2, 2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, DataOption1:=xlSortNormal End With works fine. When the workbook is shared Excel doesn't seem to want to play ball with regard to restricting the sort to the columns I've specified - it sorts the whole sheet. By the time it's gone through the four different departments (A:B, C:D, E:F, G:H) the whole things in a damn mess quite frankly and this ruins the named ranges that I've also setup. Does anyone have any idea how I can enforce this restriction of sorting on columns when the workbook is shared? Thanks George |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting cells in Shared Workbook...
Hmmm - this doesn't sound right to me. I've written quite a lot of code in
this workbook and the only warning when I share is that you cannot edit or view the code in a shared workbook - not that certain options won't work. I can also correctly sort the data sheet (as in each two column section) if I choose to sort using the Excel menu so it seems to me that the functionality is there. I'm going to check if the specific "sort" functionality is broken in code when you share (although it does still work in some fashion) although I suspect that I've either written something wrongly in my code rather than the "sort" piece of code just not working. Surely MS can't expect the "sort" functionality to break anything in a shared document? Thanks for the input - I'm going to head back to the help files to see what can be done...I can't believe for a second that I can't achieve what I'm trying to do - even if it is a shared doc :-( "Aran Black" wrote: Hi George, Microsoft says in their help files that you cannot create a shared excel workbook if it has code in. That is, the code wont work! There are certain elements (Excel facilities) that are "disabled" when a workbook becomes shared. Your code calls some of this unsharable functionality. Hope this helps. Aran Black "George" wrote: Hi, I've currently got shared workbook that pulls in data (onto the data sheet) which I then sort two columns a time. The data sheet contains staff in different departments along with a shift identifier. When the workbook isn't shared this code: With dataSheet.Range("A:B") .Sort Key1:=Cells(2, 1), Order1:=xlAscending, Key2:=Cells(2, 2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, DataOption1:=xlSortNormal End With works fine. When the workbook is shared Excel doesn't seem to want to play ball with regard to restricting the sort to the columns I've specified - it sorts the whole sheet. By the time it's gone through the four different departments (A:B, C:D, E:F, G:H) the whole things in a damn mess quite frankly and this ruins the named ranges that I've also setup. Does anyone have any idea how I can enforce this restriction of sorting on columns when the workbook is shared? Thanks George |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting cells in Shared Workbook...
Rats - It's all pretty much been blown out of the water now by Excel not
adding Menu Controls to the menu bar when the workbook is shared. Damnit - I wish I'd checked that earlier on...without the menu controls the shared workbook is pretty useless :-( "Aran Black" wrote: Hi George, Microsoft says in their help files that you cannot create a shared excel workbook if it has code in. That is, the code wont work! There are certain elements (Excel facilities) that are "disabled" when a workbook becomes shared. Your code calls some of this unsharable functionality. Hope this helps. Aran Black "George" wrote: Hi, I've currently got shared workbook that pulls in data (onto the data sheet) which I then sort two columns a time. The data sheet contains staff in different departments along with a shift identifier. When the workbook isn't shared this code: With dataSheet.Range("A:B") .Sort Key1:=Cells(2, 1), Order1:=xlAscending, Key2:=Cells(2, 2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, DataOption1:=xlSortNormal End With works fine. When the workbook is shared Excel doesn't seem to want to play ball with regard to restricting the sort to the columns I've specified - it sorts the whole sheet. By the time it's gone through the four different departments (A:B, C:D, E:F, G:H) the whole things in a damn mess quite frankly and this ruins the named ranges that I've also setup. Does anyone have any idea how I can enforce this restriction of sorting on columns when the workbook is shared? Thanks George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting cells in Shared Workbook...
Hi George,
Yes I agree. Shared Excel workbooks arent the most impressive feature of Excel I have seen. My rule when developing for users is never put VBA code in a workbook that is going to be shared. MS Access and other technologies (including Excel Services - see below) is better for this. Excel Services I think the Excel development Team have created a new sharing workbook facility called "Excel Services" based on web based server technology! And VBA code is re-written in VBScript and Javascript!! Aran Black "George" wrote: Rats - It's all pretty much been blown out of the water now by Excel not adding Menu Controls to the menu bar when the workbook is shared. Damnit - I wish I'd checked that earlier on...without the menu controls the shared workbook is pretty useless :-( "Aran Black" wrote: Hi George, Microsoft says in their help files that you cannot create a shared excel workbook if it has code in. That is, the code wont work! There are certain elements (Excel facilities) that are "disabled" when a workbook becomes shared. Your code calls some of this unsharable functionality. Hope this helps. Aran Black "George" wrote: Hi, I've currently got shared workbook that pulls in data (onto the data sheet) which I then sort two columns a time. The data sheet contains staff in different departments along with a shift identifier. When the workbook isn't shared this code: With dataSheet.Range("A:B") .Sort Key1:=Cells(2, 1), Order1:=xlAscending, Key2:=Cells(2, 2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, DataOption1:=xlSortNormal End With works fine. When the workbook is shared Excel doesn't seem to want to play ball with regard to restricting the sort to the columns I've specified - it sorts the whole sheet. By the time it's gone through the four different departments (A:B, C:D, E:F, G:H) the whole things in a damn mess quite frankly and this ruins the named ranges that I've also setup. Does anyone have any idea how I can enforce this restriction of sorting on columns when the workbook is shared? Thanks George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting in a shared workbook | Excel Discussion (Misc queries) | |||
Sorting Shared Workbook | Excel Discussion (Misc queries) | |||
Sorting in Shared Workbook | Excel Worksheet Functions | |||
Sorting in a shared workbook | Excel Discussion (Misc queries) | |||
Sorting data in a shared workbook | Excel Programming |