Sharing workbooks limitations - Merged Cells

[L. Howard]

Merged cells, both in the vba coding and on the sheet for formatting seem to be stopping the sharing of a workbook.

When I read this Note: does it say that merged cells are NOT supported so you must be sure to add them BEFORE sharing and then they will work okay?

Or are merged cells just plain not allowed in a shared workbook?

I know very little about sharing workbooks and have never done so myself. I sent a workbook to a OP and that person wants to use it as a shared workbook and it is not working because of merged cells.

Thanks,
Howard

*************
Note: Not all features are fully supported in a shared workbook. If you want to include any of the following features, you should add them before you save the workbook as a shared workbook. You cannot make changes to thesefeatures after you share the workbook.

Features that are not supported in a shared workbook
IN A SHARED WORKBOOK, YOU CANNOT: HOWEVER, THIS FUNCTIONALITY IS AVAILABLE:
Create an Excel table
Insert or delete blocks of cells You can insert entire rows and columns
Delete worksheets
Merge cells or split merged cells
*************

 

[Claus Busch]

Hi Howard,

Am Mon, 10 Feb 2014 10:49:34 -0800 (PST) schrieb L. Howard:

Merged cells, both in the vba coding and on the sheet for formatting seem to be stopping the sharing of a workbook.

if merged cells don't work and centeracrossselection is not reliable
enough read the width and the height of the selection and place
textboxes or labels with the names and the color over the selection.


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Mon, 10 Feb 2014 10:49:34 -0800 (PST) schrieb L. Howard:






if merged cells don't work and centeracrossselection is not reliable

enough read the width and the height of the selection and place

textboxes or labels with the names and the color over the selection.





Regards

Claus B.

--

So the bottom line is no merged cells with a shared workbook, either before or after saving as a shared workbook.

Would you still be able to add comments to the selection or would the textbox/label prevent that?

I'll study up some on how to incorporate textbox/labels via code.

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 10 Feb 2014 16:56:40 -0800 (PST) schrieb L. Howard:

Would you still be able to add comments to the selection or would the textbox/label prevent that?

if you insert a textbox or a label you can't select the cell behind. So
it is impossible to add a comment. you can place the comment in a cell
in front or behind the selection.


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Mon, 10 Feb 2014 16:56:40 -0800 (PST) schrieb L. Howard:






if you insert a textbox or a label you can't select the cell behind. So

it is impossible to add a comment. you can place the comment in a cell

in front or behind the selection.





Regards

Claus B.

--

After playing around with text boxes, I was coming to that conclusion.

I found this code that does an excellent job of filling a selection neatly with a text box.

I have a couple of other codes that add a predetermined text to the text box (Which would be a name in this case) and color but I cannot incorporate those methods into this preferred code.

One code also names the text box and therefore gets away from the ever increasing TextBox10, 11, 12, 13 etc.

So neat as this little snippet is, I lack the ability to add the centered text "Charlie", a chosen background color, and name it TB_Charlie.

I am pretty sure I can get the delete text box code from the macro recorder by going into Design Mode, click on the text box and delete it while recording.

I'm not sure why this Dim statement was included in the code.

Dim oTB As Object

Works okay commented out...?

Thanks.
Howard


Sub TextBoxSelection()
Dim ws As Worksheet

Dim oTB As Object
Set ws = Worksheets("Sheet2")
If TypeName(Selection) = "Range" Then
With Selection
ActiveSheet.Shapes.AddTextBox _
msoTextOrientationHorizontal, .Left, _
.Top, .Width, .Height
End With
End If
End Sub

 

[Claus Busch]

Hi Howard,

Am Mon, 10 Feb 2014 23:52:36 -0800 (PST) schrieb L. Howard:

ActiveSheet.Shapes.AddTextBox _
msoTextOrientationHorizontal, .Left, _
.Top, .Width, .Height

I guess the OP would write:
set oTB= ActiveSheet.Shapes.AddTextBox _
( msoTextOrientationHorizontal, .Left, _
.Top, .Width, .Height)

that he could refer to oTB to set other properties.

Pleas look again here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "DeskBooking"

I copied a row of the table to BE10:BX10 with columnwidth.
Now you can select a range into the table and run "Test_CB". The same
range will be selected in BE10:BX10, will be colored and get the name
and then this range will be copied as picture back into the table.


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Mon, 10 Feb 2014 23:52:36 -0800 (PST) schrieb L. Howard:










I guess the OP would write:

set oTB= ActiveSheet.Shapes.AddTextBox _

( msoTextOrientationHorizontal, .Left, _

.Top, .Width, .Height)



that he could refer to oTB to set other properties.



Pleas look again here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "DeskBooking"



I copied a row of the table to BE10:BX10 with columnwidth.

Now you can select a range into the table and run "Test_CB". The same

range will be selected in BE10:BX10, will be colored and get the name

and then this range will be copied as picture back into the table.





Regards

Claus B.

--

Now that's impressive.

I have some work to do to get all the names assigned and colors too then the deletes.

This should keep me busy for awhile and off the Forum. (May have a question or two but will give it my best shot.)

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 11 Feb 2014 01:31:10 -0800 (PST) schrieb L. Howard:

This should keep me busy for awhile and off the Forum. (May have a question or two but will give it my best shot.)

please look first in SkyDrive for "DeskBookings_2.0"
because I now know why centeracrossselection doesn't work reliable.
We forgot with "Reform" to reset the horizontal alignment to xlGeneral.
So the cells with centeracrossselection not always are the same as
selected ones.
Have a try for the workbook. Then comments will also be possible.


Regards
Claus B.

 

[L. Howard]

please look first in SkyDrive for "DeskBookings_2.0"

because I now know why centeracrossselection doesn't work reliable.

We forgot with "Reform" to reset the horizontal alignment to xlGeneral.

So the cells with centeracrossselection not always are the same as

selected ones.

Have a try for the workbook. Then comments will also be possible.

Regards

Claus B.

--

I got 2.0 up and going with the first name Charlie. Really seems to work well as I cannot duplicate the overrun selection errors. I just need to clean up all the other names and should be good to go.

Do have a question about the merged cells Y1, Z1 AA1 to make the wide drop downs readable. Will those merged cells stop the workbook from being shared?

If they do, then I guess I can shorten the long entries to some degree and live with that.

The comments and the sharing seem to be the larger issue.

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 11 Feb 2014 05:55:07 -0800 (PST) schrieb L. Howard:

Do have a question about the merged cells Y1, Z1 AA1 to make the wide drop downs readable. Will those merged cells stop the workbook from being shared?

if merged cells exist workbook sharing should be possible.
But you can't merge or unmerge in a shared workbook.


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Tue, 11 Feb 2014 05:55:07 -0800 (PST) schrieb L. Howard:






if merged cells exist workbook sharing should be possible.

But you can't merge or unmerge in a shared workbook.





Regards

Claus B.

--

Okay, got it.

Thanks.

Howard

 

[L. Howard]

Hi Claus,

From the end user of the Bookings workbook after receiving version 2.0, and my reply to her:

----------
Howard - this is brilliant!

thank you so much!

My colleagues have had a look too and they're all very excited to start using this in place of our current crappy system that times out on us all the time!

Thank you thank you thank you thank you!

Samantha

---------

Thank you for the kind words while at the same time I found a small glitch in the coding in 2.0.

You will notice if Charlie adds comments he will get a different set of user form displays than the rest of the bookers.

This version 2.1 has that corrected and should be the one to use.

https://www.dropbox.com/s/jf02mhdeu7...rop Box.xlsm

Also, please know that the majority of the code is from Claus, a very dominant contributor in the MS Public Excel Groups.
He often gives me gems and I "do the assembly" to the best of my know how.

This seems to have worked out nicely for your bookings project.

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Wed, 12 Feb 2014 08:01:11 -0800 (PST) schrieb L. Howard:

My colleagues have had a look too and they're all very excited to start using this in place of our current crappy system that times out on us all the time!

fine that it is working now. Glad to help. Thank you for the feedback.


Regards
Claus B.