Sorting cells in Shared Workbook...

[George]

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

 

[Aran Black]

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]

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 :-(

 

[George]

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]

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