I have a worksheet that is shared. It has hidden rows and columns. Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared. How do I keep the rows and columns hidden?
View 3 RepliesThe other day I inserted a row in a shared workbook and then saved it. On my screen, the inserted row was visible and was the correct row height of 12.75. My goal was to insert the row so another user sitting next to me could enter information in the new row.
After I saved the workbook, and after the user sitting next to me saved her workbook, the row I inserted should have appeared exactly the same on her screen, but it did not. Instead, the inserted row showed up as a hidden row on her screen. It was there, but the row height was collapsed to the point of invisibility.
I repeated my attempt to insert a row which the other user could edit several times, but each time I got the same result. I could not make the row show up on her screen with a row height of 12.75.
Shared workbook in Microsoft Excel 2003?
I have a spreadsheet with various hidden rows and columns that I do not wish the user to have access too as it contains too much detail for their purposes.
Copy/paste and copy/paste special also copies across the hidden columns and rows so what I need to do is to copy it to a new workbook but then remove the hidden rows and columns and just delete the existing formulas in the cells which total the data and insert a new autosum.
I have mapped data from Sheet1 to Sheet2 and created graphs in Sheet2 using the data. but when I hide the rows in Sheet2 the Graph is being cleared off. Need a solution so as to display the Graph in Sheet2 while hiding the rows in the same sheet.
View 2 Replies View RelatedOne of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected!
WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
example attached
I need to find, then replace a particular value ("/0"), however, some cells may be hidden. And I just discovered that I can not replace the value of a hidden cell! Am I missing something, or do I have to unhide the row/column that the cell is in and then replace it? ...this does not replace the "/0" with "0" if the row the cell is in is hidden.
Sub test()
Dim rMatch
Set rMatch = ActiveSheet.Cells.Find(What:="/0", LookIn:=xlValues, LookAt:=xlWhole)
rMatch.Value = "0"
End Sub
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
View 5 Replies View Relatedim looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.
The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I have an Excel workbook which is networked throughout our business. Its been in daily use for years with no problems. Every month I add a new sheet and to do that I untick the "allow changes by more than one user...etc". However this month the tick is greyed out and wont let me clear it. The pc's run windows XP professional with Excel 2003.
View 2 Replies View RelatedI have a workbook that is no longer being shared. I tried clicking on Tools > Shared Workbook. BUT i keep getting a message saying "The file C:CCTPAccountsSummary1.XLS cannot be found". Is there any way to kill this Shared workbook so I am free to modify everything?
View 2 Replies View RelatedPlease follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.
View 14 Replies View Relatedhow to speed up a shared excel worksheet?
View 4 Replies View RelatedHow do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below
HTML Sub Time()
Call Save
Application.OnTime Now + TimeValue("00:00:45"), "Time"
End Sub
HTML Sub Save()
ActiveWorkbook.Save
End Sub
I have a workbook that i am trying to make a shared workbook.
Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.
Once a workbook is shared it's protection cannot be changed.
I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).
Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.
Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.
We have a worksheet, which contains charts and data which requires updating by different department.
However, when I have this work- book as a Shared workbook (Tools - share workbook), The charting and macro has all been deactivated.
Would anyone have any idea on how do I share a workbook - while being able to also update the Excel Charts within it ?
(Different user will be updating different charts, and there will be no conflict in between them)
HTML Code:
Private Sub SaveAsAndShare()
ActiveWorkbook.SaveAs Filename:= _
"S:LdnPath1Path2Path32012Folder1Folder 2Aug12ReportName " & Format(Date, "MM-DD") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub
HTML Code:
ActiveWorkbook.SaveAs FileName, , , , , , xlShared
My code is the first bit, the 2nd code I found from another post, I want to add xlshared to my code above so it is shared when saved down.
Which version of excel allows you to do a Track Changes without switching to a shared workbook? I am awared the Excel 2002 version allows Track Changes on the conditioned that the workbook is switch to shared mode.
View 3 Replies View Related1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
View 2 Replies View RelatedOur main spreadsheet in the office is a shared filed which is accessed by 3 pc's.
We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
i have a workbook with picture and that workbook in share mode.
i have to move picture in share mode.
I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
it is making a well laid out and good sheet look like something a child has done
I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
My macros don't work when the workbook is shared. They are not stored in my personal workbook and so are available to all users. What's going on?
View 3 Replies View RelatedUsing Excel 2000, I have a shared workbook which is effectively a single sheet, flat database. Rows contain dates, and when certain dates occur, I want to send an email alert to specific users. I have searched and found lots of guidance on that aspect, but am wondering how best to trigger the email alert. At least one, and up to 10 users access the shared workbook at different times of day, and I only want to review the data and send the email alerts to the relevant users once per day.
I thought about a timed event, but cannot be sure the shared workbook will be open. I wondered whether an event in Personal.xls might do it, if I ensure I open Excel every day, but it's not ideal. Finally, I wondered if I could somehow trigger the alerts when the first user opens the workbook - but can't find how to capture that.
Can you use or apply advanced filters in a shared workbook?
I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).
SETUP: I have a shared workbook that is accessed by many people. It is designed to control the flow of work during the monthly close process. These processes are controlled by macros. There are 40 sheets in this workbook.
PROBLEM: On of my location's workbooks frequently loses half of it's sheets in the workbook. Counted in the neighborhood of 14 to 19 sheets go missing.
EFFORTS: I have attempted to Delete, Cut, Move, etc... these sheets with no success.
Only when I put the file in an exclusive state can I manipulate the sheets.