Active Workbook Refreshall Security
Nov 4, 2008
I have a function that is called in Access vba code that refreshes all sheets in a given workbook. We use it all the time for workbooks containing any number of tabs to be refreshed. The code has a key line: ActiveWroksheet.Refreshall. I need to now start to secure the pivot so the user can not refresh it. Another post pointed me towards: .PivotCache.EnableRefresh = False.
This apparently can only be used for a particular sheet; ie it can not be applied to ALL sheets in the workbook. The trouble is the function is generically used to refresh any workbook, and the number of sheets may vary.
Any thoughts on how I can disable the ability to refresh a pivot for ALL sheets. I'd prefer not to turn off the pivot wizard since the user should be allowed to manipulate it...just not refresh it.
View 9 Replies
ADVERTISEMENT
May 30, 2009
my workbook is finally working the way it should but every time i open it i get a security warning window with the options disable macros, enable macros and more info. how do i get this to not show up every time it's opened. this workbook will be used by someone other than me and i don't want them to need to do anything but open it and use it.
View 8 Replies
View Related
Oct 5, 2013
I have a workbook which i really want to protect. I don't want any formulas to be seen and tampered with.
Simply hiding/locking cells and protecting the sheet is not secure enough. within minutes of googling crack password, i was able to find a macro that unlocks the sheet.
Is there something out there that can really secure your workbook. I found something called XLS Padlock [URL].... seems great.
View 2 Replies
View Related
Dec 15, 2008
I am relying on macros for a lot of functionality and security.
Is there a way to stop a spreadsheet from being opened/accessed if a user disables macros at the security prompt?
View 9 Replies
View Related
Feb 27, 2014
I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
View 6 Replies
View Related
Jun 30, 2014
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
View 13 Replies
View Related
Feb 6, 2013
I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
View 5 Replies
View Related
Jun 20, 2008
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
View 8 Replies
View Related
Mar 6, 2014
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
View 2 Replies
View Related
May 9, 2014
I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
View 3 Replies
View Related
Mar 6, 2014
I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.
View 1 Replies
View Related
Nov 20, 2006
I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.
View 9 Replies
View Related
Apr 22, 2009
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.
MS VB Script error:
Runtime error '9':
Subscript out of range
Any advise would be greatly appreciated.
Thank you
Code is below..
Sub saveall()
'
'
For Each ws In ActiveWorkbook.Worksheets
ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Next ws
End Sub
View 9 Replies
View Related
Nov 18, 2008
I have a sheet that will not let me use macros. I can record them, but when I go to use them it says that the Macro security level is too high. I change it to Medium or Low but it doesn't make a difference.
The same spreadsheet works with Macros on another computer, but not my main one.
View 5 Replies
View Related
Feb 15, 2007
I am working on an application where I have several workbooks open at the same time. I need to know which one is active. How do I do that?
View 3 Replies
View Related
Feb 21, 2008
I have an add-in (MyAddin.xla) that is called from my open_workbook event in my workbook (MyBook.xls):
Private Sub workbook_open()
AddIns("MyAddin").Installed = False
AddIns("MyAddin").Installed = True
ThisWorkbook.Activate
Run "MyAddIn.xla!WbookOpen"
End Sub
I put this in my WbookOpen procedure:
Sub WbookOpen(pWorkbook As Workbook)
MsgBox "Wbook Open Active workbook is " & ActiveWorkbook. Name
End Sub
The message displayed is:
Wbook Open Active workbook is MyAddin.xla
I thought this would be:
Wbook Open Active workbook is MyBook.xls.
View 9 Replies
View Related
Apr 21, 2009
I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.
View 5 Replies
View Related
Jan 20, 2010
I've manage to do this a while ago, unffortunaly my hard drive got damaged.
View 3 Replies
View Related
Apr 3, 2004
How do i calculate only the active workbook in VBA?
simple question, but i'm really slow today.
View 5 Replies
View Related
Oct 10, 2004
if it is possible to delete the current workbook that is open?
I'm thinking of using an add-in to delete the workbook i'm viewing
View 9 Replies
View Related
Feb 11, 2007
I have a number of files which I am opening, manipulating and copying the result to a master workbook
I am attempting to use code like this
Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow)
Is it posible to define the value of LastRow without making Master the active workbook?
View 3 Replies
View Related
Sep 13, 2007
Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?
View 3 Replies
View Related
Nov 3, 2008
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls
The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.
Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
View 5 Replies
View Related
May 17, 2005
How can I save only the active sheet of a workbook in VBA?
View 9 Replies
View Related
Jul 3, 2007
I have 16 proposals that are either hidden or visible in a proposal generator. I need to be able to copy only the visible worksheets over to a new workbook that uses a name from a cell inside the proposal generator. Then a save as prompt to force the user to save the file wherever they choose.
View 14 Replies
View Related
Nov 27, 2008
I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?
View 2 Replies
View Related
May 19, 2009
I have this script below that removes all code from the active workbook. The reason I am removing all the code is because I am running code from my personal library and the code changes the worksheet I am working with.So while stepping through I noticed it moves over to the sheet change sub routine from the active workbook that I am working on.
So I tried to remove all the code from the Active workbook but after I remove all the code it returns a expected end of sub error, where the old worksheet change sub was? but no code is in the entire workbook? So I tried to enter something in the blank sheet mod and it returns a (Cant Edit Module) error.
View 2 Replies
View Related
Dec 21, 2009
An Excel file is e-mailed to my company for all the credit card transactions in a given day. Because we manage many stores and each store is a different legal entity and a different fiscal year ending, I need my macro to sort the information and separate the transaction by store and by fiscal year end. I just hit my first block.
I need to open a workbook off the server to grab information for a vlookup formula. I can get the workbook open, but I can't get back to the original workbook or find a way to reactivate it. Because the macro is going to be saved in the "personal" workbook, I can't use the "thisworkbook.activate" code. Also the workbook name and tab name that is emailed to me will always be different so I can't use other solutions that I've seen posted.
View 2 Replies
View Related
Jan 3, 2010
I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well. If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break. I have tried several scripts that have the same effect. I want the script continues normally with execution.
View 3 Replies
View Related
Dec 25, 2013
With first code user selects a sheet from drop down and that sheet is activated. (This works fine)
Now hide all other sheets is what I'm trying to do with the second code. (This IS NOT working)
I assume I can either put the second code in the change event code or call it from the change event code once it works.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
[Code].....
View 2 Replies
View Related
