Excel Sharing Workbook 1

[nelson97]

I've created an Excel spreadsheet with a ton of check boxes and have also made this sheet shared. I notice that in a shared mode it will update numbers when entered in manually in the cells. Meaning that other users will see the numbers change. If I put a check in the check boxes and save, it doesn't get updated with other user's opened Excel Sheet. I'm not trying to do any calculation on the check box, just simply display a check mark in there. And yes, I do make sure that other users hit Save to get their sheet refreshed. Is this one of those "can't be done" in shared mode? I want other users to be able to see the checked boxes when a user checks them. This is the main reason I want to share it.
I'm using Excel 2002.
Please advise. Thank you!!

 

[sheri922]

I've found in this case they will have to close the workbook and re-open it to view the changes. When they open it their view is static. When you make a change while that view is open, programatic items such as check boxes won't refresh the same way your static data will, say with a link.
It's unfortunate the Microsoft hasn't added the Refresh command under the View Menu in Excel the way it has in Access (especially since more users are learning to program with excel).

 

[Tianjin]

Either you use a form checkbox or a ActiveX Checkbox, the only way you can update status of a checkbox is to create a linkcell for that checkbox. As long as you change the status of a checkbox, the value in correspond linkcell is changed. thus, while a user hit save button in this shared workbook, first, the linkcell got updated with new content and checkbox will reflect that change.
you can find these by right clicking the checkbox and select properties. by the way, you must do all of above in exclusive access status(not shared). my another suggestion is you should keep a list for all checkboxes in a separate sheet and hide it.

Good luck

Tianjin

 

[nelson97]

Hi sheri922, thanks for the insight. I tried creating a macro that paints 3 cells red when pressed. I notice that while it works on the excel sheet that's running the macro, the other sheet doesn't get updated with the colored cells that the macro ran on the other sheet. Is it accurate to say that macros will work, but while in shared mode, will not propagate to other shared excel files while it's open?
I don't want to close and reopen because it will be a spreadsheet on display on a dedicated monitor at all times. It does have an auto refresh at a min interval of 5 minutes. But I've chosen to update manually on Save.

Thanks

 

[nelson97]

HI Tianjin, basically what I've done is color coded my spreadsheet. It's an inventory display sheet basically with predetermined amount of cells with specific colors. For example I have 3 cells: A1, B1, C1. Cell A1 is colored green, B1 yellow and C1 Red. Well in each box I've added a form checkbox in the middle. So that I have the ability to put a check in either boxes. Just simply for display purposes and not generate an action of some kind from the checkbox click event. And I want other users to also see that the yellow cell has a check in that box after refreshing their shared excel file, for example. But you're saying that this can't be done and I have to link the check box to a cell in order for updating to occur with other sheeet? This is all of course while keeping the workbook open and not have to close and reopen it. Also just to see if macro generated events gets updated on other users's files, I created a simple macro that when clicked it colored 2 cells red. But if I check and refresh the other user's sheets, it doesn't see what the macro has done. I would have to of course close the sheet altogether and reopen it in order to see that change, which is not what I'm trying to achieve.

Thanks!

 

[Tianjin]

Sorry for not explaining clearly, in fact, linkcell is one property of the Form Checkbox. The status of a checkbox(check or uncheck) is automatically link with the value of a cell that checkbox is link to. This link is handled by excel application itself if you setup a linkcell to the checkbox. To setup a link to a checkbox, right click on one checkbox, from dropdown menu, select format control, and then select control tab. In here, you can set up a link to that checkbox. In this way, the change either from checkbox or from the linkcell will reflect one to another. In other words, if you check a checkbox, in the linkcell, the value will change to “true”; if you changed linkcell to “false”, the checkbox will be unchecked. The updating in a share workbook is only update the value change in the cells instead of status of controls within the sheet. By using a bridge of a linkcell, you can indirectly update the status of a checkbox.

Good luck

tianjin

 

[nelson97]

Awesome Tianjin! That worked, I'm so psyched! Is there a way to hide what gets reflected in the cell? When I click on the check box "True" or "False" gets displayed in the cell as you mentioned above.

Thank you!!

 

[Tianjin]

Two options you can select to hide contents in the linkcell.
first, if you don't fill anything in the cells under each checkbox, use that cell as linkcell and change the font color of linkcell same as the background color, so you can visually hide the linkcells. This approach may need someting vb code to dynamically match the font color with backgound color.
second, Manage all linkcells in a separate sheet by employ two columns, one for attribute(name, function or location etc.)of each checkbox. and another one for holding the value of "true" or "false". in this way, you can keep your main worksheet tidy and clean.

good luck

tianjin