Error Unprotecting Shared Book

Apr 13, 2007

I can get it working standalone, and when I protect it, but when I share the document it errors on me and I dont know why. I know there are limitations, but this macro should still run (the macro should unprotect everything, then protect and share after it has run).

Public Sub RQV()
Dim lngRow As Long
If MsgBox("Do you wish to move RQV data to yesterday? Note that all amendments will be reset also.", vbYesNoCancel) = vbYes Then
ActiveWorkbook.UnprotectSharing
worksheet1.Unprotect Password:="unsecureme"
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
. ScreenUpdating = False
End With...................................

View 7 Replies


ADVERTISEMENT

Copy Data From Book To Book Error

Jun 22, 2007

I have the below code, which now looks to see if a file is open or not, if it is, then copy and past 'Data' and if not open the book and copy 'Data'.

I think the code is sort of right, but im missing something, as i keep getting runtime error when i try and copy. Here is the
Sub PrintSaveKPIUpdate()

Dim sFilName As String
sFilName = "C: estCashSales_KPI.xls"
Set Main = ThisWorkbook

If IsOpen(sFilName) Then

' Book is Open.

Worksheets("Setup Data"). Range("Data").Activate

View 9 Replies View Related

Run Error 1004 When Sharing Book

Apr 26, 2007

However, since I have shared the document the macros do not work. I am a little confused at this as I thought there would only be a problem if I protected the document, so I took that off yet I get a run error 1004. I have played with the code to have exclusive access but that doesnt appear to make a difference either.

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim CurrentSheet As String
CurrentSheet = Application.ActiveSheet. Name
' turn off the screen updating
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' open the source workbook, read only
Set wb = Workbooks.Open("L:Clearance&AgencyCollateral Management ProductsCMSGRQV SPREADSHEETSRQV.MasterSheet.xls", True, True)...............

View 3 Replies View Related

Shared File Causing 1004 Error?

Feb 7, 2014

why my code would hit a run time error on a shared file? It works fine unshared. This is on a ActivateSheet Worksheet Event, Code is housed in a Module and called when activated.

[Code]......

View 2 Replies View Related

Runtime Error 1004 Command Is Not Available In Shared Workbook

Mar 15, 2013

i have this code for copying diffrent ranges of cells and saving it in different workbooks:

Sub NewCopy()
Dim strFileName As String
Dim range1 As Range
Dim rng1 As Range, rng2 As Range, myMultiRanges As Range

[Code].....

the problem is when is share the workbook, it generated the runtime error '1004' Command is not available in a shared workbook... make it work even in a shared workbook.

View 3 Replies View Related

Activating Open Book - Subscript Out Of Range Error?

May 20, 2012

The code opens the book but the activation line gets a subscript out of range error.

Code:
Workbooks.Open File
Workbooks(File).Worksheet("Disc_Nodes").Activate

View 1 Replies View Related

Move All Worksheets From Book B Behind All Sheets In Book A?

Jul 18, 2014

I made the following code to merge 2 workbooks together.

The code is to be executed when the user has Workbook A opened. (All sheets in workbook KPISWD are supposed to be moved after all worksheets in workbook KPICustomers).

I keep getting a debug error on the code that is supposed to do the actual move and loop until it is finished with all of the sheets in Workbook B.

Code:
Dim KPICustomers, KPISWD As String
KPICustomers = ActiveWorkbook.Name

Workbooks.Open Filename:= _
"W:FacturatieKPI per periode SWD.xls"

KPISWD = ActiveWorkbook.Name

[Code] ..........

View 3 Replies View Related

Call Macro In Book Two From Book One

Aug 29, 2007

i have done a search on this topic and found many similar answers to many similar questions. All specify using Application.Run "workbookname.xls!macroname".

In my code, the filename has an assigned value as the master code goes out to many secondary files - the user selects the particular one they want at the start of the macro. The variable assigned is called "UpdateKAMsFile".

how do i get the macro called KAMsRandomColour to run in the workbook designated by UpdateKAMsFile?

View 9 Replies View Related

Macro To Highlight Data Inside Other Data Or Move From Book To Book

Jul 16, 2014

so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.

2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.

View 6 Replies View Related

Protecting And Unprotecting With VBA

Feb 6, 2010

I have created a form that allows users to input data to an excel sheet, and also will enable users to view the data once they are done.

Is there a way to unprotect the sheet using VBA so that the data can be written, and then protecting once the data has been input?

View 2 Replies View Related

Unprotecting And Protecting Sheets

Nov 18, 2008

I currently have set up two macros that will unprotect and protect all sheets. When I run the macro I noticed that it will not unprotect the sheets that have been set as a new sheet for the chart (the chart is not a object in a sheet it is a sheet all by itself) Can anyone tell me where I have gone wrong?

View 14 Replies View Related

Unprotecting A Worksheet By Username

Dec 4, 2009

I would like to unlock the work sheet based on the username. However this code does not seems to be working.

The object is that only these users have access to the locked cells on a sheet and those users not on the list would only have access to the unlocked cells.

View 3 Replies View Related

Unprotecting Sheets Before Spellcheck

Jun 11, 2003

I cannot get my spellcheck to check any textbox. Is there a way to do this? Unprotecting sheets before spellcheck will work, but nothing about texboxes.

View 7 Replies View Related

Protecting And Unprotecting A Sheet

Oct 2, 2008

I want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.

View 9 Replies View Related

Using VBA: Protecting And Unprotecting A Workbook

Feb 8, 2010

i would like the user to be able to unprotect the workbook via clicking a button. this button would first ask the user for a password before unprotecting the workbook.

how can i code this through VBA?

i already built the following VBA ...

View 9 Replies View Related

Worksheet Keep Unprotecting After Protected With Password?

Nov 1, 2013

I have the following VBA in my spreadsheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))

[Code] ......

As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.

View 2 Replies View Related

Protecting / Unprotecting Multiple Worksheets

Jul 21, 2014

I have a workbook which contains 52 worksheets, all of which need protecting. Is there a way of protecting / unprotecting all these worksheets without having to enter my password on each separate sheet?

View 3 Replies View Related

Insert A Comment Without Unprotecting Sheet

Sep 28, 2007

scenario: sheet & work book are protected to users; however need them to sometimes insert a comment to this sheet.

all columns/rows are locked except columns K, R, Y and every 7th column thru CJ (rows are from 4 - 100).

how do I enable a user to insert a comment without unprotecting sheet / WB? note: there's no pattern, it could be any cell within aforementioned columns.

View 9 Replies View Related

Unprotecting Workbooks Multiple Times

Mar 6, 2008

I've got code that loops through approximately 700 excel files and unprotects the workbook. I have to run this code for five consecutive days, and the issue I'm having is for the files that are unprotected on Day 1, the code still runs for those files the next 4 days.

There may be only 10-15 new files submitted on day 5, but the code still runs through all 700.

View 9 Replies View Related

Copy From One Book To Another Tab On Another Book

Aug 20, 2009

I would like to know if there is a way to copy a spreadsheet and paste it into a new tab on another spreadsheet. We currently download 2 lots of spreadsheets from SAP and would like to create another spreadsheet to act as a 'central' preadsheet. What I would like to happen is that the data from the 2 downloaded spreadsheets will be automatically pasted into 2 new tabs on the central spreadsheet, then run some formatting macros (which I can do fine).

View 3 Replies View Related

Shared File

Jan 7, 2007

I am designing a workbook that techs in my lab can use from any of our network computers to submit requests for making chemical solutions they need to run their tests. Currently they have to fill out a paper form to make the request, so this should make things a little more efficient and less paper intensive.

I have the workbook designed and working, but now need to implement it as a shared file and this is where the problems are occurring. I assumed that shared files could allow access to the same file AT THE SAME TIME with no issues, but that is not the case in Excel97 on our network. Using two networked computers side by side I have tried updating the shared file at the same time and got a myriad of popups. The include:

1) Do you want to save the changes you made to "..."? (I did not include the actual filename here.)

2) A file named "......" already exists in this location. Do you want to replace it?

3) "......" is now available for editing. Choose Read-Write to open it for editing.

4) This file is locked. Try the command later.

5) This file has been locked by for saving. Try again later.

Question:

Are these error messages normal for shared files? Do some reflect issues more with the network than with Excel? Is there any way to get VBA to simulate clicking a Yes, No, or Cancel, or OK whenever a one of these specific messages comes up?

View 9 Replies View Related

Changes To Shared Workbook

May 6, 2006

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 Related

Shared Spreadsheets

May 20, 2006

I have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.

View 4 Replies View Related

Vba On Shared Spreadsheet

May 31, 2007

I have a 4 MB Excel file containing some very basic VBA that is shared on a server (it is a large file because it contains LOTS of "VLOOKUP" functions). But the VBA only works a part of the time. I assume the reason it might not work is because the server at that time was under high traffic and thus the VBA failures can be traced to poor server performance at that time.

View 4 Replies View Related

Book Using Too Much Memory?

Dec 23, 2013

I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.

Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups.

I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.

View 3 Replies View Related

VBA Book For Beginners

Sep 8, 2009

I am looking some suggestions for a good book i can buy online to learn about writing VBA for excel. I find this forum very useful for help on solutions to write VBA code but i would like to learn the basics so i can understand what it is i am adding into my excel files. Moderators... If this breaks the forum rules (i've read them and can't see anything).

View 3 Replies View Related

Copy From One Book To Another

Sep 30, 2009

I Have been working on a spreadsheet for attendance recording. I will be keeping a new one for each month. I need to be able to take accumulated data from one book and put it in the new one. I have the Following

View 2 Replies View Related

Vlookup From 1 Book To Another

Sep 11, 2009

What do I need to do to get my vlookup formulas to look into another workbook(or excel file) without having to open it up.

Here is a sample of formula that I have:

[=IF(C13="","",VLOOKUP(C13,Data,21,))]

View 14 Replies View Related

Txt Address Book

Aug 16, 2008

I have data like this:

Sammi Alvarez-Vegan

1201 E. Ponce De Leon Blvd.

Coral Gables, FL 33134

Angie Penns

6870 SW 44th Street

Building G28- #110

Miami, FL 33155

Some names and addresses are 3 lines and some are 4 lines. All have a blank line between. There is a double blank between different name blocks.

I tried to Concatenate so all parts would be in B1 cell. =A1&","&A2&","&A3&","&A4&","&A5&","&A6&","&A7

That did what I wanted for B1, and when I copied it down, B2:B8 was part of an address.

It was not until B9 did the actual next name come into play.

I know there must be a better way

I am using WindowsXP and Excel2007

View 9 Replies View Related

Set Variable As Book Name

Aug 14, 2006

I am generating Excel reports (with different names) using VBA. The problem I'm having is that for a certain macro, it needs to create a new book, and then activate the original window. But because all the new reports have new names (but using same macros), the name written in code doesn't equal the book's new name. How can I set a variable to be the book name? I do know that the name of the original book will be equal to two letters plus the value in A7. The problem is this: I have written a macro that shows the following

Worksheets("Sheet2").Select
Workbooks.Add
Windows("FT Bi Test.xls").Activate

I need something more like this (though this exact code doesn't work):

Worksheets("Sheet2").Select
Workbooks.Add
Windows("FT " & Range("A7").Value ".xls").Activate

Or something like this:

Worksheets("Sheet2").Select
Workbooks.Add
Windows("FT " & Variable ".xls").Activate

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved