Share and unshare a workbook using code 1

[UnsolvedCoding]

I have a new coding issue that has never presented itself before.

A smaller location within the company has a few people working in it and they want to use Excel as a timesheet of sorts. In an unshared format all the changes and macro's work as expected and in a shared formate they work as expected right up to the point of merging cells.

When a new week starts Excel recreates the grids that hold the hours worked and merges some of the cells during the process.

Is it possible to share and unshare a workbook using code? For example, if someone is opening a new week and a fresh grid is created can VBA turn the share off, create the changes and then redisignate the workbook as shared?

So far I keep running across changes that happen when the workbook is saved by doing something like this -

newBook .SaveAs FileName:=newBook.FullName , AccessMode:=xlShared


However I haven't found anything to show how to unshare the workbook or if there is a way to merge cells in a shared workbook.

Does anyone know how to do these things or where to start looking?

 

[SkipVought]

hi,

Merge is a scourge!

I would advise strongly to avoid using merge, as this is only one of the many pitfalls associated therewith.

Try using Center Across Selection, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Thanks Skip, the technique works great.


Range("B15").Value = "Center with no merge"

Range("B15:E15").HorizontalAlignment = xlCenterAcrossSelection

 

[SkipVought]

I'd probably code it like this...

With Range("B15:E15")
   .Cells(1,1).value = "Center with no merge"
   .HorizontalAlignment = xlCenterAcrossSelection
End with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}